How to split a CSV or Excel file
Sometimes its necessary to split large import files, and we have a tool to help you do just that!
Written by Jay Godfrey
Updated over a week ago
Files imported to a nation need to be smaller than 1000 MB, and your computer may have trouble opening a CSV file that is larger than 50 MB, in this case, you may need some additional help with file splitting. So, 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 Splitter. 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 WorksheetSet 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
Excel for Microsoft 365 for Mac Excel 2021 for Mac Excel 2019 for Mac Excel 2016 for Mac More...Less
In Excel for Mac, you can split a sheet into panes or use windows to view multiple sheets or multiple workbooks.
Split a sheet into panes
You can view two areas of a sheet by splitting it into pane. When you split a sheet into separate panes, you can scroll in both panes independently.
Select below the row where you want the split, or the column to the right of where you want the split.
On the View tab, in the Window group, click Split.
To remove the split panes, click Split again.
View multiple sheets in one workbook
Open the workbook that you want to view.
On the Window menu, click New Window.
Switch to the new window, and then click the tab for the sheet that you want to view.
Repeat steps 2 and 3 for each sheet that you want to view.
On the Window menu, click Arrange.
Do one of the following:
To arrange windows
So that they appear like this
Click
As equally sized, tiled squares
Tiled
Horizontally from top to bottom
Horizontal
Vertically from right to left
Vertical
In an overlapping cascade from top to bottom
Cascade
Select the Windows of active workbook check box.
View multiple workbooks
Open all the workbooks that you want to view.
On the Window menu, click Arrange.
Do one of the following:
To arrange windows
So that they appear like this
Click
As equally sized, tiled squares
Tiled
Horizontally from top to bottom
Horizontal
Vertically from right to left
Vertical
In an overlapping cascade from top to bottom
Cascade
Tip: To restore a workbook window to full size, select the workbook, and then on the Window menu, click Zoom.
See also
Freeze panes to lock the first row or column