Split large csv file and keep header in each part

Question

How to split a large csv file (~100GB) and preserve the header in each part ?

For instance

h1 h2
a  aa
b  bb

into

h1 h2
a  aa

and

h1 h2
b  bb

how can we know what is "each part" with such a tiny example? — fedorqui, May 23 '16 at 08:37
This is too broad. Provide a [mcve] together with what you tried. — fedorqui, May 23 '16 at 08:39
Looks like a potential duplicate and there's a better answer (using `awk`) here: https://stackoverflow.com/questions/51420966/split-csv-files-into-smaller-files-but-keeping-the-headers — Neil C. Obremski, Nov 23 '21 at 00:38

score 6 · Accepted Answer · answered May 23 '16 at 08:41

6

First you need to separate the header and the content :

header=$(head -1 $file)
data=$(tail -n +2 $file)

Then you want to split the data

echo $data | split [options...] -

In the options you have to specify the size of the chunks and the pattern for the name of the resulting files. The trailing - must not be removed as it specifies split to read data from stdin.

Then you can insert the header at the top of each file

sed -i "1i$header" $splitOutputFile

You should obviously do that last part in a for loop, but its exact code will depend on the prefix chosen for the split operation.

answered May 23 '16 at 08:41

Aaron

24,009
2
33
57

Wouldn't `data=$(tail -n +2 $file)` try to load the entire file into memory? – Neil C. Obremski Nov 23 '21 at 00:31
@NeilC.Obremski I can't say for sure honestly. Maybe I'll find the time to test it, if you do first please give me your feedback ! – Aaron Dec 04 '21 at 07:59

score 1 · Answer 2 · answered Jul 29 '17 at 03:34

I found any previous solutions to this to not work properly on the mac systems that my script was targeting (why Apple? why?) I eventually ended up with a printf option that worked out pretty good as a proof of concept. I'm going to enhance this by putting the temporary files into a ramdisk and the like to improve performance since it is putting a bunch on disk as is and will probably be slow.

#!/bin/sh

# Pass a file in as the first argument on the command line (note, not secure)
file=$1

# Get the header file out
header=$(head -1 $file)

# Separate the data from the header
tail -n +2 $file > output.data

# Split the data into 1000 lines per file (change as you wish)
split -l 1000 output.data output

# Append the header back into each file from split 
for part in `ls -1 output*`
do
  printf "%s\n%s" "$header" "`cat $part`" > $part
done

score 0 · Answer 3 · answered Sep 21 '20 at 11:07

you may download a freeware CsvSplitter from here. It is a zip from the website that contains a simple portable .exe file and a .txt file, necessary to work along with the executable, just extract the content in some directory and you're ready to work:

and it can split the file as can be seen in this picture

Everything is self-explanatory but more details can be found here

Split large csv file and keep header in each part

3 Answers3