Split CSV files into smaller files but keeping the headers?

by

The answer to this question is yes, this is possible with AWK.

The idea is to keep the header in mind and print all the rest in filenames of the form filename.00001.csv:

awk -v l=11000 '(NR==1){header=$0;next}
                (NR%l==2) {
                   close(file); 
                   file=sprintf("%s.%0.5d.csv",FILENAME,++c)
                   sub(/csv[.]/,"",file)
                   print header > file
                }
                {print > file}' file.csv

This works in the following way:

  • (NR==1){header=$0;next}: If the record/line is the first line, save that line as the header.
  • (NR%l==2){...}: Every time we wrote l=11000 records/lines, we need to start writing to a new file. This happens every time the modulo of the record/line number hits 2. This is on the lines 2, 2+l, 2+2l, 2+3l,…. When such a line is found we do:
    • close(file): close the file you just wrote too.
    • file=sprintf("%s.%0.5d.csv",FILENAME,++c); sub(/csv[.]/,"",file): define the new filename as FILENAME.00XXX.csv
    • print header > file: open the file and write the header to that file.
  • {print > file}: write the entries to the file.

note: If you don’t care about the filename, you can use the following shorter version:

awk -v m=100 '
    (NR==1){h=$0;next}
    (NR%m==2) { close(f); f=sprintf("%s.%0.5d",FILENAME,++c); print h > f }
    {print > f}' file.csv

More Related Contents:

  1. Parsing a CSV file using gawk
  2. Bash: Parse CSV with quotes, commas and newlines
  3. using bash (sed/awk) to extract rows AND columns in CSV files?
  4. How to delete rows from a csv file based on a list values from another file?
  5. Some troubles with using sed and awk [closed]
  6. How do I use shell variables in an awk script?
  7. How can I do a recursive find/replace of a string with awk or sed?
  8. Bash tool to get nth line from a file
  9. awk in bash with ls and variable
  10. Remove first N lines of a file in place in unix command line
  11. How to extract one column of a csv file
  12. How to add to the end of lines containing a pattern with sed or awk?
  13. Sort a text file by line length including spaces
  14. Parsing variables from config file in Bash
  15. How to make awk ignore the field delimiter inside double quotes? [duplicate]
  16. Specify other flags in awk script header
  17. Use awk to find average of a column [duplicate]
  18. Check if all of multiple strings or regexes exist in a file
  19. How to remove double-quotes in jq output for parsing json files in bash?
  20. How to find/replace and increment a matched number with sed/awk?
  21. find difference between two text files with one item per line [duplicate]
  22. Grep output with multiple Colors?
  23. Using grep to search for hex strings in a file
  24. How to append a string at end of a specific line in a file in bash [duplicate]
  25. How to strip out all of the links of an HTML file in Bash or grep or batch and store them in a text file
  26. How to get the PID of a process in a pipeline
  27. Sum duplicate row values with awk
  28. What is the shortest way to get n-th column of an output?
  29. How to grep for case insensitive string in a file?
  30. How can I format the output of a bash command in neat columns

Leave a Comment