How To Split A CSV Or Excel File | NationBuilder HOWTOs

Home » How To Split Sheets In Excel Into Separate Files Kutools » How To Split A CSV Or Excel File | NationBuilder HOWTOs

Maybe your like

Skip to main contentAll CollectionsDataHow to split a CSV or Excel fileHow 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!

Updated over a year 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 WorkbookDim WBO As WorkbookDim WBN As WorkbookDim WST As WorksheetDim WSO As WorksheetDim WSN As WorksheetSet WBT = ThisWorkbookSet WST = WBT.Worksheets(1)Set WBO = ActiveWorkbookSet WSO = ActiveSheetTopCount = WST.Range("C6").ValueDivCount = WST.Range("C9").ValueIf WBT.Name = WBO.Name ThenMsgBox "Please switch to the large workbook before Ctrl+Shift+S"Exit SubEnd IfMyName = WBO.NameMyPath = WBO.Path & Application.PathSeparatorWST.Range("E17").Value = MyPathMyRows = WSO.UsedRange.Rows.CountWST.Range("E18").Value = MyRowsRowsPerFile = Int(MyRows / DivCount) + 1WST.Range("E19").Value = RowsPerFileStartRow = TopCount + 1Ctr = 0For i = StartRow To MyRows Step RowsPerFileCtr = Ctr + 1NewFN = MyPath & Format(Ctr, "000") & MyNameApplication.StatusBar = NewFN & " rows " & i & " to " & (i + RowsPerFile - 1)WSO.CopySet WBN = ActiveWorkbookSet WSN = ActiveSheetKeepRow1 = iKeepRowN = i + RowsPerFile - 1DelRow1 = KeepRowN + 1' Delete everything from DelRow1 on downDelSize1 = 1048576 - DelRow1Cells(DelRow1, 1).Resize(DelSize1, 1).EntireRow.DeleteIf i > StartRow Then' Also delete rows at the topDelRow1 = StartRowRange(Cells(StartRow, 1), Cells(i - 1, 1)).EntireRow.DeleteEnd IfWBN.SaveAs NewFN, FileFormat:=WBO.FileFormatWBN.Close FalseWST.Cells(20 + Ctr, 2).Value = NewFNNext iWBT.ActivateApplication.StatusBar = FalseMsgBox Ctr & " files created"End SubDid this answer your question?😞😐😃

Tag » How To Split Sheets In Excel Into Separate Files Kutools