Split large CSV file with header into multiple CSV files for every nth row with a header

Question

I have a large CSV file that I would like to split into multiple CSV files. I've tried numerous VBS scripts, but I cannot seem to get this.

This script does some of what I want but does not save them as CSV files:

Sub Split()
Dim rLastCell As Range
Dim rCells As Range
Dim strName As String
Dim lLoop As Long, lCopy As Long
Dim wbNew As Workbook

With ThisWorkbook.Sheets(1)
    Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)

    For lLoop = 1 To rLastCell.Row Step 35
        lCopy = lCopy + 1
        Set wbNew = Workbooks.Add
        .Range(.Cells(lLoop, 1), .Cells(lLoop + 35, .Columns.Count)).EntireRow.Copy _
        Destination:=wbNew.Sheets(1).Range("A1")
        wbNew.Close SaveChanges:=True, Filename:="Inventory_" & lLoop + 34
    Next lLoop
End With

End Sub

Before Closing the workbook, save it as CSV: `wbNew.SaveAs FileName:="C:\Temp\MyCSV.csv", FileFormat:=xlCSV`. After that, you can close the workbook with `SaveChanges:=False`. — AutomatedChaos, Sep 28 '12 at 14:58
You can get more control with a recordset: http://stackoverflow.com/questions/427488/want-vba-in-excel-to-read-very-large-csv-and-create-output-file-of-a-small-subse/427511#427511 — Fionnuala, Sep 28 '12 at 22:08

score 3 · Answer 1 · answered Sep 28 '12 at 15:02

Off the top of my head:

Const ForReading = 1
Const ForWriting = 2

Set fso = CreateObject("Scripting.FileSystemObject")

maxRows = 35
i = 0
n = 0

Set out = Nothing
Set csv = fso.OpenTextFile("C:\PATH\TO\your.csv", ForReading)
header = csv.ReadLine

Do Until csv.AtEndOfStream
  If i = 0 Then
    If Not out Is Nothing Then out.Close
    Set out = fso.OpenTextFile("out_" & Right("00" & n, 2) & ".csv", ForWriting)
    out.WriteLine(header)
    n = n + 1
  End If
  out.WriteLine(csv.ReadLine)
  i = (i + 1) Mod maxRows
Loop

csv.Close    
If Not out Is Nothing Then out.Close

I agree. No need to involve Excel. – Robert Co Sep 28 '12 at 20:04 — Robert Co, Sep 28 '12 at 20:04

score 3 · Accepted Answer · edited Oct 08 '12 at 17:08

3

Added a saveas line to your code to specify the file format, you should be all set

Sub Split()
Dim rLastCell As range
Dim rCells As range
Dim strName As String
Dim lLoop As Long, lCopy As Long
Dim wbNew As Workbook

With ThisWorkbook.Sheets(1)
    Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)

    For lLoop = 2 To rLastCell.Row Step 35
        lCopy = lCopy + 1
        Set wbNew = Workbooks.Add
        .Cells(1, 1).EntireRow.Copy _
        Destination:=wbNew.Sheets(1).range("A1")
        .range(.Cells(lLoop, 1), .Cells(lLoop + 35, .Columns.Count)).EntireRow.Copy _
        Destination:=wbNew.Sheets(1).range("A2")
        wbNew.SaveAs FileName:="Inventory_" & format(lLoop + 34,"0000") & ".csv", FileFormat:=xlCSV, Local:=True
        wbNew.Close SaveChanges:=False
    Next lLoop
End With

End Sub

edited Oct 08 '12 at 17:08

Travis Smith

538
2
9
21

answered Sep 28 '12 at 15:58

nutsch

5,922
2
20
35

This is close. Is there a way to keep the first line on every subsequent CSV file? Also, is there a way to make the file name have 4 numbers every time? Currently, it creates Inventory_35.csv, Inventory 135.csv, Inventory 1235.csv. Can we change it so that it always has 4 numbers? So, Inventory_0035.csv, Inventory 0135.csv, Inventory 1235.csv, et. al. – Travis Smith Oct 08 '12 at 16:18
Also, I would like to be able to make it semi-colon seperated instead of comma separated. So is there a way to set the sperator? – Travis Smith Oct 08 '12 at 16:20
Change the regional settings on your computer before saving (then change them back). Region and Language \ Additional Settings \ List separator. – nutsch Oct 08 '12 at 16:29
Yes, I've done that (and haven't changed it back), but the VBA is saving them as comma separated instead of semi-colon separated?? – Travis Smith Oct 08 '12 at 16:30
I changed my code for your first question. For the comma vs semi-column, are the commas in your excel sheet to start with? – nutsch Oct 08 '12 at 16:34
No, I am dealing with a large CSV file that I save as a *.xlsm file to run the Macro. – Travis Smith Oct 08 '12 at 16:40
and your current list separator is the semi-column, as in the French regional settings – nutsch Oct 08 '12 at 16:42
Yes, it is. My list separator is set to semicolon. – Travis Smith Oct 08 '12 at 16:44
Even if I change my settings to French, the Macro still makes it comma-separated instead of semi-colon separated. – Travis Smith Oct 08 '12 at 16:47
I think that this will help: [Semi-Colon Separated](http://logbuffer.wordpress.com/2010/10/19/vba-save-excel-table-as-csv-semicolon-delimited/) – Travis Smith Oct 08 '12 at 16:52
That didn't work. To test, I checked Region and Language \ Additional Settings \ List separator. Then open a SEMI-COLON separated file in Excel, separates nicely. Then open a COMMA separated file in Excel, stays in Column A. – Travis Smith Oct 08 '12 at 16:56
To further test, I create a file and save as CSV then open in Notepad to see it semicolon separated. – Travis Smith Oct 08 '12 at 16:57
Excel will only split your csv properly if they're delimited by the list separator in your regional settings. To force the split otherwise, you'll need to use text to columns. – nutsch Oct 08 '12 at 16:58
No, the culprit is wbNew.Close SaveChanges:=True. It is removing the semicolons and changing them to commas. Changing that line to wbNew.Close SaveChanges:=False solved the problem. – Travis Smith Oct 08 '12 at 17:05

score 0 · Answer 3 · answered Mar 22 '23 at 15:57

'In O365 Excel the OpenTextFile statement should be CreateTextFile in order to
'create a new file.

Const ForReading = 1
Const ForWriting = 2

Set fso = CreateObject("Scripting.FileSystemObject")

maxRows = 35
i = 0
n = 0

Set out = Nothing
Set csv = fso.OpenTextFile("C:\PATH\TO\your.csv", ForReading)
header = csv.ReadLine

Do Until csv.AtEndOfStream
  If i = 0 Then
    If Not out Is Nothing Then out.Close
    Set out = fso.CreateTextFile("out_" & Right("00" & n, 2) & ".csv", ForWriting)
    out.WriteLine(header)
    n = n + 1
  End If
  out.WriteLine(csv.ReadLine)
  i = (i + 1) Mod maxRows
Loop

csv.Close    
If Not out Is Nothing Then out.Close

Split large CSV file with header into multiple CSV files for every nth row with a header

3 Answers3