Can be done.
Going to assume your data doesn't have an EQUALS sign anywhere in it because the code you are using delimits the file by an = sign. This would make all the data after an equals sign disappear in your output. The delimiters should be set to nothing.

So basically what I would do is hold the very first line in your big file which is your header record into a separate variable. Then use an IF statement to see if your LineNum variable is equal to 1. When it is equal to 1 echo that variable to the output file. Of course you will have to a flag so that it doesn't output the header twice the very first time.

Technically your code does not output 5000 "records" to the first file because it is counting the header record as a line. Currently the code will have 4999 records with data and 1 header record in the first split file and the spilt files after that will all have 5000 records.

So you will need to ask yourself. Do you want 5000 data records in each file plus the header record to make each split file 5001 lines or do you want 4999 data records plus the header record to make each split file 5000 lines? That may require some additional coding as well.

 

I had a great routine for this, I'll see if I can find it.

 

Here it is: http://www.mydigitallife.info/download-free-csv-splitter-to-split-csv-files-easily/

Worked like a champ for me a while back.

 

Here it is: http://www.mydigitallife.info/download-free-csv-splitter-to-split-csv-files-easily/

Worked like a champ for me a while back.

Awesome. Works like a charm and lot faster than a batch file.

 

Would be nice if it had an option to not replicate the header record. I work with a lot of csv files that do not have the header record but I usually use an old unix port of SPLIT to split them into smaller chunks.

 

Would be nice if it had an option to not replicate the header record. I work with a lot of csv files that do not have the header record but I usually use an old unix port of SPLIT to split them into smaller chunks.

I remember thinking the same thing...

 

I'm sure the first file will have the header, all you need is a bacth to take all the other files (the names will be related as far as I can make out of the tool) and insert the header into each file?

 

I'm sure the first file will have the header, all you need is a bacth to take all the other files (the names will be related as far as I can make out of the tool) and insert the header into each file?

The utility ckphilli posted works exactly how the OP asked. Doesn't need a batch file for this.

 

Thank you all for your response, however, in as much as I would like to use ckphilli's suggestion, I can not do so because of some licensing issues even if it is a freeware that is why I was hoping to do it using a batch file.

 

Its not only freeware it is open source. There is no licensing issue! And because this is a compiled executable you will find that it runs 10 times faster than a batch file. Batch was never meant to process large text files.

Trust me on this. I do data processing for the largest printing company in the world. This is a no brainer! There should be no issue using it.

 

I'll see if my company will allow it. Many thanks to all, will keep this open for now and mark it as solved if the company will allow using it. Thanks again

 

I'll see if my company will allow it. Many thanks to all, will keep this open for now and mark it as solved if the company will allow using it. Thanks again

It doesn't even require an install. It is a self contained executable.

Trust me when I say splitting large text files with BATCH is extremely slow. I tried writing several batch files in the past to do certain things and it would take hours to process a million or so records. I just ran that utility and it took probably less than 30 seconds to process 266,000 lines.

PowerShell can do it faster but I haven't gotten around to learning everything about it to do what I need it to do. There is no substitute for a compiled executable.

 

It doesn't even require an install. It is a self contained executable.

Trust me when I say splitting large text files with BATCH is extremely slow. I tried writing several batch files in the past to do certain things and it would take hours to process a million or so records. I just ran that utility and it took probably less than 30 seconds to process 266,000 lines.

PowerShell can do it faster but I haven't gotten around to learning everything about it to do what I need it to do. There is no substitute for a compiled executable.

Additionally, writing a macro to process this much data will SMOKE your machine. When I was dealing with this kind of thing I tried the macro route before I found this on a stout machine (fast processor, lots of memory) and it was horrible.

 

Additionally, writing a macro to process this much data will SMOKE your machine. When I was dealing with this kind of thing I tried the macro route before I found this on a stout machine (fast processor, lots of memory) and it was horrible.

Exactly. I found that out the hard way as well. When you use a file in a FOR Loop in a batch file, the whole file gets loaded into memory. I only got 1GB of memory on my system. So anytime I encountered a file larger than around 600MB, it would give me a memory error and not process the file.

 

Could you post let's say a 100 row sample of one of these csv files?
I want to try something out before explaining more, that is if you don't ind.