Linux – How to split CSV files as per number of rows specified

awkcsvlinuxsedunix

I've CSV file (around 10,000 rows ; each row having 300 columns) stored on LINUX server. I want to break this CSV file into 500 CSV files of 20 records each. (Each having same CSV header as present in original CSV)

Is there any linux command to help this conversion?

Best Answer

For the sake of completeness, here are some minor improvements:

You could save the header once and reuse many times
You could insert the header in the split files using sed without temporary files

Like this:

header=$(head -n 1 file.csv)
tail -n +2 file.csv | split -l 20
for file in x??; do
    sed -i -e 1i$'\\\n'"$header" "$file"
done

The $'\\\n' there is a NEWLINE character escaped with a backslash. The sed expression means: insert $header before the 1st line.

Related Solutions

Remove Line Breaks Following Carriage Return in Bash

If these ^M-s are indeed line break characters, not literal caret & letter M strings, then they are what we denote \r, CR or 0x0d (compare this answer of mine, the beginning of it).

Your command

sed -e "s/^M//"

doesn't remove \r; it doesn't even remove literal ^M. The command means "take a line, search for a letter M that is at the very beginning of the line (^, see this), replace it with nothing.

Note sed understands \r. Still sed -e 's/\r//' is not exactly what you need. It removes \r but you need to remove the following \n as well. You may want to try sed -e 's/\r\n//', this will also fail. The problem is sed is a text tool and it treats \n as a separator. Excerpt from info sed (emphasis mine):

sed operates by performing the following cycle on each lines of input: first, sed reads one line from the input stream, removes any trailing newline, and places it in the pattern space. Then commands are executed; […].

This means normally \n doesn't belong to any string processed with s/… (or another sed command). For this reason concatenating few lines is not easy. Still it can be done. This is the command you need:

sed -e ': start; /\r$/{ s/\r$//; N; s/\n// }; /\r$/b start'

Explanation:

: start is a label.
If the line contains \r (i.e. ^M, 0x0d character) at the very end ($), execute the {} block which is:
- replace \r at the very end with nothing,
- append an additional line from the input (N),
- replace \n that separates the additional line from the previous data.
If the outcome contains \r at the very end (meaning the additional line brought it, so we need to add yet another line), jump to start.

Best Answer

Related Solutions

Remove Line Breaks Following Carriage Return in Bash

Related Question