Create multiple CSV files from one Excel file

K B
K B used Ask the Experts™
on
I have an Excel File with 1000 Rows.

I would like to create a CSV from each "X" rows of data.
Each CSV would have the duplicate headers as the source Excel file.
For example, "X" would equal 50 rows of data.
It would be also great to name the 20 CSV files accordingly (the incremental number of total rows in Excel):
For example:
50.csv
100.csv
150.csv

Sometimes there will be only one column - sometimes there will be more.

Thank you so much for your time in advance!
K.B.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Open in new window

Fernando SotoRetired
Distinguished Expert 2017

Commented:
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));
            }
        }
    }

}

Open in new window

K B

Author

Commented:
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.

2015-04-16-0126.png
2015-04-16-0128.png
Thank you!

K.B.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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".
Fernando SotoRetired
Distinguished Expert 2017

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

Author

Commented:
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.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
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;  

Open in new window

K B

Author

Commented:
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!
Fernando SotoRetired
Distinguished Expert 2017

Commented:
What version of Visual Studio are you using?
K B

Author

Commented:
I don't have visual studio.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
How and with what development envierment are you developing the software with?
K B

Author

Commented:
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.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
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.
K B

Author

Commented:
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.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
@ 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

Open in new window

Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi K B;

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

Good Luck.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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

Open in new window

K B

Author

Commented:
I am looking forward to giving this a try soon!
Thank you!!
K B

Author

Commented:
This is the error I got:
2015-05-14-1709.png2015-05-14-1709-001.png
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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
K B

Author

Commented:
2013
Microsoft Excel Expert
Top Expert 2014
Commented:
i suspect, it has to do with your file  testEE.xlsx  

i run it in Microsoft Excel 2013  and it worked like charm.  

to fix the problem, please download the two attached files.

replace the attachment testEE.xlsx  with your file in your directory C:\scripts\EE\

and then save  the second attachment  EE.xlsm  somewhere open EE.xlsm and then click on the button and then see what happens.

plz let me know how it goes.
EE.xlsm
testEE.xlsx
K B

Author

Commented:
Thank you!  Works perfect!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial