Create multiple CSV files from one Excel file

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.
LVL 8
K BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
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

0
Fernando SotoRetiredCommented:
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

0
K BAuthor 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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Martin LissOlder than dirtCommented:
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".
0
Fernando SotoRetiredCommented:
The code I posted was tested and working, but @K B never even commented on the solution.
0
K BAuthor 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.
0
Fernando SotoRetiredCommented:
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

0
K BAuthor 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!
0
Fernando SotoRetiredCommented:
What version of Visual Studio are you using?
0
K BAuthor Commented:
I don't have visual studio.
0
Fernando SotoRetiredCommented:
How and with what development envierment are you developing the software with?
0
K BAuthor 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.
0
Fernando SotoRetiredCommented:
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.
0
K BAuthor 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.
0
ProfessorJimJamCommented:
@ 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

0
Fernando SotoRetiredCommented:
Hi K B;

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

Good Luck.
0
ProfessorJimJamCommented:
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

0
K BAuthor Commented:
I am looking forward to giving this a try soon!
Thank you!!
0
K BAuthor Commented:
This is the error I got:
2015-05-14-1709.png2015-05-14-1709-001.png
0
ProfessorJimJamCommented:
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
0
K BAuthor Commented:
2013
0
ProfessorJimJamCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K BAuthor Commented:
Thank you!  Works perfect!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.