1

I have a 700 MB folder containing 24k text files with varying sizes. I want to create new folders each having ~50 MB size so that I can process them in excel using vba as it has row limit of 10 lakh lines.

Any help will be appreciated.

9
  • 1
    Showing us what you've tried will be appreciated – Dave Nov 28 '17 at 14:14
  • 1
    Post your code and point out where you're stuck and we'll be happy to help. – root Nov 28 '17 at 14:18
  • 1
    I could split files according to certain files in each folder (that doesn't serve my purpose) but I could not get any related code when searched then I posted it here. – Samadhan Gaikwad Nov 28 '17 at 14:19
  • 2
    You can use dir /b to get a list of all the files, which you can then step through, moving files and counting as you go, and after every 50 moves increment the target directory. That should give you the guidelines for a solution, but don't expect someone to write it for you: we don't do that on this site. – AFH Nov 28 '17 at 14:26
  • 1
    How are you correlating 50 Megabytes equals the number of rows you need? The row limit for modern versions of excel is 1,048,576. – Squashman Nov 28 '17 at 17:26
1

I was interrested into doing this as well, so here is the PowerShell code I use. This will get all files of your main folder, and move them into 10MB subfolders. Feel free to adapt values and names to your need.

# Replace with your base folder's path
$baseFolder = "C:\Users\you\myHugeFolder"

# Replace with the desired value for the subfolders to create
$maxSubFolderSize = 10MB

# Get all files contained in your base folder (could use the -recurse switch if needed)
$allFiles = Get-ChildItem $baseFolder

# Setting the subfolders naming convention : a name and a suffix
$baseSubFolder = "SubFolder-"
[int]$index = 0

# Creating the first subfolder
$subFolder = "SubFolder-" + "$index"
New-Item -Path $subFolder -Type Directory -Force

# Now processing the files
foreach ($file in $allFiles)
{
    # Evaluating the size of the current subfolder
    $subFolderSize = ((Get-ChildItem $subFolder -Recurse | Measure-Object -Property Length -Sum -ErrorAction Stop).Sum / 1MB)

    # If the current subfolder size is greater than the limit, create a new subfolder and begin to copy files in it
    if([int]$subFolderSize -gt [int]$maxSubFolderSize/1MB)
    {
        $index++
        $subFolder = $baseSubFolder + $index
        New-Item -Path $subFolder -Type Directory -Force
        Write-Verbose -Message "Created folder $subFolder"
        Move-Item $file.FullName -Destination $subFolder
    }
    # If the current subfolder is not yet greater that the limit, continue copying files in it
    else {
        Move-Item $file.FullName -Destination $subFolder
    }
}

Hope this helps !

3
  • 1
    Ran script for 1MB, but all folder size are having size >1.5 MB. Script has created 23 folders. I think that will be fine. Just one question, is it possible to create folders each having files such that count of lines =10 lakh. – Samadhan Gaikwad Nov 29 '17 at 13:03
  • 1
    Then you can adapt the size in the variable to match your need. There might be differences between size on disk and actual size. – Ob1lan Nov 29 '17 at 13:07
  • 1
    OK. Thanks, anyway this will be helpful for me to go ahead! – Samadhan Gaikwad Nov 29 '17 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.