Create multiple CSV files from one Excel file

Try this macro. Modify the FolderPath to suit.

Sub SaveCSVfiles()

    Const NumRows As Integer = 50
    Const HeaderRow As Integer = 1
    Const FolderPath As String = "P:\" 'change to suit
    
    Dim iRow As Integer, startSheet As Worksheet, tmpSheet As Worksheet
    
    Application.ScreenUpdating = False

    iRow = HeaderRow + 1
    Set startSheet = ActiveSheet
    Do Until iRow > 1000 + HeaderRow
        Set tmpSheet = Worksheets.Add
        'copy the header
        startSheet.Range(HeaderRow & ":" & HeaderRow).EntireRow.Copy tmpSheet.Range("A1")
        'copy the data
        startSheet.Range(iRow & ":" & (iRow + NumRows - HeaderRow)).EntireRow.Copy tmpSheet.Range("A2")
        'save tmpSheet to CSV file
        tmpSheet.Move
        With ActiveWorkbook
            .SaveAs Filename:=FolderPath & (iRow + NumRows - HeaderRow - 1) & ".csv", FileFormat:=xlCSVMSDOS
            .Close True
        End With
        iRow = iRow + NumRows
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

Select allOpen in new window

Hi K B;

The below C# code will do what you need. To use this code you will need to add a dll to LinqToExcel and can be gotten by using NuGet and searching for LinqToExcel.

using System.Linq;
using LinqToExcel;
using System.IO;  


private void ProcessExcelToFiles( )
{
    // Open and query the Excel document. This string, "C:\Working Directory\testExcel.xlsx", in the next line is the path and filename for the Excel file.
    var excel = new ExcelQueryFactory(@"C:\Working Directory\testExcel.xlsx");
    // In the next line change "Sheet1" in the next line to the name of the sheet name where the data is in.
    var lines = (from c in excel.Worksheet("Sheet1")
                 select c).ToList();

    // The number of files that have been created through each loop.
    int fileIndex = 1;
    // Number of rows in each file except maybe the last
    int linesPerFile = 50;
    // Total lines to process after each loop
    int totalLinesRemaining = lines.Count();
    // Used for paggination, point in the list the last line that was processed
    int skip = 0;
    // When done processing line make this false
    bool notDone = true;
    // Number of columns in the excel file.
    int numberOfColumns = lines[0].ColumnNames.Count();


    while( notDone )
    {
        // Create the file name, true part full lines per file, false part number of line remaining
        int intFilename = (totalLinesRemaining / linesPerFile > 0 
            ? fileIndex * linesPerFile 
            : ((lines.Count() / linesPerFile) * linesPerFile) + (totalLinesRemaining % linesPerFile));

        // Keep track of the number of line processed
        totalLinesRemaining -= linesPerFile;
        fileIndex += 1;

        // Are we done
        if (totalLinesRemaining <= 0) notDone = false;
        // Create filename as a string from integer.
        string filename = intFilename + ".csv";

        // Write data to the files.
        using( StreamWriter sw = new StreamWriter(filename) )
        {
            // Get the lines to write from the list, skipping over the lines already processed 
            var linesToWrite = (from l in lines.Skip(skip).Take(linesPerFile)
                                select l).ToList();
            // Get ready for the next lines
            skip += linesPerFile;

            // Write the headers to the file.
            string headers = lines[0].ColumnNames.Aggregate((output, name) => output + ", " + name);
            sw.WriteLine(headers);
            // Now write the data for each row to the file.
            foreach (Row row in linesToWrite)
            {
                List<string> rowValues = new List<string>();
                row.ForEach(cell => rowValues.Add(cell.ToString()));
                sw.WriteLine(rowValues.Aggregate((output, value) => output + ", " + value));
            }
        }
    }

}

Select allOpen in new window

Wayne,

First of all congrats!
Thank you for the code.  I am running into this error when executing it.  It does crank out several CSVs. 10 in this example.



Thank you!

K.B.

I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".

The code I posted was tested and working, but @K B never even commented on the solution.

Apologies for not posting this. Perhaps you can help me with this.  I could not get the  Linq2Excel working at all. I spent many hours trying and googling. I should have posted here sooner.

Did you include the using statements at the top of the code file, as shown in the original post and reposted here? And you also used the NuGet package manager and loaded LinqToExcel into your project. Also what version of Visual Studio are you using? And if you done all that what is the exception you are getting and inner exception.

using System.Linq;
using LinqToExcel;
using System.IO;  

Select allOpen in new window

Fernando I am grateful for your follow up replies as I really need this solution.
My company got me a new laptop as my old one died  - Though I remember i tried loading so many things to no avail.  
Could you tell me specifically what to download (link) and install to use this code?
I am not a developer so I don't have visual studio but will install what you tell me.

Thank you again Fernando!

What version of Visual Studio are you using?

I don't have visual studio.

How and with what development envierment are you developing the software with?

I am not a developer.
I am not developing any software.
I am a systems engineer.  I heavily use PowerShell (and Excel/CSVs in conjuntion with PowerShell).
I use the VBScripts provided here directly in Excel (Alt + F11) and that is about it.

OK, I am sorry the topic area this question was placed in showed C# and .Net as well as Microsoft Excel SpreadSheet. Seeming I do not know Excel programing I will not be able to help with this.

I have visual studio. I would be willing to load it if you think it wouldn't be too difficult to show me how to use the linqtoExcel.

@ K B

can you try my simple VBA Solution?


first create a folder somewhere for example in C:\Users then put your Excel file in there. i mean the one that you want to split it into CSVs . or if your file have a different name and different path, the replace the bold underlined text with your file path and file name, in the below code.

then

open just a simple excel file, please the below code in it and then run the macro SplitXLForEach50Row and it will split your excel file and will save into new csvs for each 50 of rows.

let me know how it goes

Public Sub SplitXLForEach50Row()

' Solution for question ID 28656054  by ProfessorJimJam
    Dim inputFile As String, inputWB As Workbook
    Dim lastRow As Long, row As Long, n As Long
    Dim newCSV As Workbook
    
    inputFile = [u][b]"C:\Users\Split.xlsx"[/b][/u]
    
    Set inputWB = Workbooks.Open(inputFile)
    
    With inputWB.Worksheets(1)
        lastRow = .Cells(Rows.Count, "A").End(xlUp).row
        
        Set newCSV = Workbooks.Add
        
        nNumber = 0
        For row = 2 To lastRow Step 50
            nNumber = nNumber + 50
            .Rows(1).EntireRow.Copy newCSV.Worksheets(1).Range("A1")
            .Rows(row & ":" & row + 50 - 1).EntireRow.Copy newCSV.Worksheets(1).Range("A2")
                        
            'Saves in same folder as input workbook with .xlsx
            newCSV.SaveAs Filename:=Replace(inputWB.FullName, ".xlsx", "(" & nNumber & ").csv"), FileFormat:=xlCSV, CreateBackup:=False
        Next
    End With
    
    newCSV.Close saveChanges:=False
    inputWB.Close saveChanges:=False
    
End Sub

Select allOpen in new window

Hi K B;

I will leave you in the capable hands of @ProfessorJimJam who seems to have a simpler solution for you.

Good Luck.

correction to the above code, by removing the bold and underline from the snapped code

try this one

Public Sub SplitXLForEach50Row()

' Solution for question ID 28656054  by ProfessorJimJam
    Dim inputFile As String, inputWB As Workbook
    Dim lastRow As Long, row As Long, n As Long
    Dim newCSV As Workbook
    
    inputFile = "C:\Users\Split.xlsx"  '  modify to your desired path location and file name
    
    Set inputWB = Workbooks.Open(inputFile)
    
    With inputWB.Worksheets(1)
        lastRow = .Cells(Rows.Count, "A").End(xlUp).row
        
        Set newCSV = Workbooks.Add
        
        nNumber = 0
        For row = 2 To lastRow Step 50
            nNumber = nNumber + 50
            .Rows(1).EntireRow.Copy newCSV.Worksheets(1).Range("A1")
            .Rows(row & ":" & row + 50 - 1).EntireRow.Copy newCSV.Worksheets(1).Range("A2")
                        
            'Saves in same folder as input workbook with .xlsx
            newCSV.SaveAs Filename:=Replace(inputWB.FullName, ".xlsx", "(" & nNumber & ").csv"), FileFormat:=xlCSV, CreateBackup:=False
        Next
    End With
    
    newCSV.Close saveChanges:=False
    inputWB.Close saveChanges:=False
    
End Sub

Select allOpen in new window

I am looking forward to giving this a try soon!
Thank you!!

This is the error I got:

What is the version of excel you have in your machine? I guess it is 2003. Right? If it is then I am going to modify the code for 2003 version. Please confirm

2013

Thank you!  Works perfect!