How to split a CSV or Excel file

Files imported to a nation need to be smaller than 1000 MB. On the other hand, your computer may have trouble opening a CSV file that is larger than 50 MB. 

Microsoft MVP in Excel Bill Jelen created a solution just for NationBuilder customers: The File Splitter!

Bill graciously provided permission for NationBuilder to share both the Excel file and the macro code. This is shared as a use at your own risk, unsupported tool. 

This macro works on an open Excel file. The interface for the file is:

To run the macro, fill in the following information:

  • The number of heading rows (Cell C6)
  • The number of files to be created (Cell C9)

Make sure macros are enabled and click Control-Shift-S. Once the file split is finished, the macro will return the location of the files, the number of rows in the file and the total number of files created.

Click here to download a compressed version of the Excel file. After unzipping the file, you'll be able to open it in Excel. You'll see a warning message that the workbook contains macros and that macros can contain viruses. In order to use the macro, click the "Enable Macros" button. 

If you would prefer to build the macro yourself, use this process to add the code to a file:

  • Open a new file in Excel
  • Enable macros
  • Open the macro editor
  • Copy the text below starting at "Sub" and ending with "End Sub"
  • Paste it into the macro editor
  • Return to Excel from the macro editor
  • Save the file as a file of type xlsm

Here is the code for the macro:

Sub SplitTheWorkbook()
Dim WBT As Workbook
Dim WBO As Workbook
Dim WBN As Workbook
Dim WST As Worksheet
Dim WSO As Worksheet
Dim WSN As Worksheet

Set WBT = ThisWorkbook
Set WST = WBT.Worksheets(1)
Set WBO = ActiveWorkbook
Set WSO = ActiveSheet


TopCount = WST.Range("C6").Value
DivCount = WST.Range("C9").Value

If WBT.Name = WBO.Name Then
MsgBox "Please switch to the large workbook before Ctrl+Shift+S"
Exit Sub
End If

MyName = WBO.Name
MyPath = WBO.Path & Application.PathSeparator
WST.Range("E17").Value = MyPath

MyRows = WSO.UsedRange.Rows.Count
WST.Range("E18").Value = MyRows

RowsPerFile = Int(MyRows / DivCount) + 1
WST.Range("E19").Value = RowsPerFile

StartRow = TopCount + 1
Ctr = 0

For i = StartRow To MyRows Step RowsPerFile
Ctr = Ctr + 1
NewFN = MyPath & Format(Ctr, "000") & MyName
Application.StatusBar = NewFN & " rows " & i & " to " & (i + RowsPerFile - 1)
WSO.Copy
Set WBN = ActiveWorkbook
Set WSN = ActiveSheet
KeepRow1 = i
KeepRowN = i + RowsPerFile - 1
DelRow1 = KeepRowN + 1
' Delete everything from DelRow1 on down
DelSize1 = 1048576 - DelRow1
Cells(DelRow1, 1).Resize(DelSize1, 1).EntireRow.Delete
If i > StartRow Then
' Also delete rows at the top
DelRow1 = StartRow
Range(Cells(StartRow, 1), Cells(i - 1, 1)).EntireRow.Delete
End If
WBN.SaveAs NewFN, FileFormat:=WBO.FileFormat
WBN.Close False
WST.Cells(20 + Ctr, 2).Value = NewFN
Next i

WBT.Activate
Application.StatusBar = False
MsgBox Ctr & " files created"

 

End Sub

If you’re unclear on how this feature works, please ask a question. If you would like to see changes to this feature, please submit a suggestion.