Print the first N lines of a CSV where quoted fields can contain newlines

user_13000875 0
Viewed 107

CSV file can have data with new line . it can be with any column. Also some line can have data without any new line so it should work in all case

Sample input

   ID,username,mobile,city,Message,Address,city
'11111111',TestUSer,1234567890,test,"Hi how are you? Well: we will connnect

Thanks for your time!
With Joy.
Test",Address test,City test
11111116,TestUser,1234567891,test,hello msg,Address test1,City test1
'111111167',TestUSer,1234567890,test,"Hi how are you one? Well: we will connnect

Thanks for your time!
With Joy.
Test",Address test,City test
11111112,TestUser,1234567891,test1,hello msg1,Address test2,City test2
11111113,TestUser,1234567891,test1,hello msg1,Address test2,City test2
11111114,TestUser,1234567891,test1,hello msg1,Address test2,City test2

I am using below command to read top 5 record of csv

awk -v RS='("[^"]*")?\r?\n' 'NF{ORS = gensub(/\r?\n(.)/, "\\\\n\\1", "g", RT);  ++n; print} n==5{exit}' file.csv

Actual output:

ID,username,mobile,city,Message,Address,city
'11111111',TestUSer,1234567890,test,"Hi how are you? Well: we will connnect\nThanks for your time!\nWith Joy.\Test",Address test,City test
11111116,TestUser,1234567891,test,hello msg,Address test1,City test1
'111111167',TestUSer,1234567890,test,"Hi how are you one? Well: we will connnect\nThanks for your time!\nWith Joy.\nTest",Address test,City test
11111112,TestUser,1234567891,test1,hello msg1,Address test2,City test2
11111113,TestUser,1234567891,test1,hello msg1,Address test2,City test2
11111114,TestUser,1234567891,test1,hello msg1,Address test2,City test2

Wanted output:

ID,username,mobile,city,Message,Address,city
'11111111',TestUSer,1234567890,test,"Hi how are you? Well: we will connnect\nThanks for your time!\nWith Joy.\Test",Address test,City test
11111116,TestUser,1234567891,test,hello msg,Address test1,City test1
'111111167',TestUSer,1234567890,test,"Hi how are you one? Well: we will connnect\nThanks for your time!\nWith Joy.\nTest",Address test,City test
11111112,TestUser,1234567891,test1,hello msg1,Address test2,City test2
sed csv awk shell
2 Answers

With your shown samples only, could you please try following awk code. Written and tested with GNU awk. Make use of RS record separator and then substitute globally to nullify new lines in RT, then print the lines accordingly.

awk -v RS='"[^"]*"' '{gsub(/\n/,"\\n",RT);ORS=RT} 1' Input_file

To get first 10 records, try following:

awk -v RS='"[^"]*"' '{gsub(/\n/,"\\n",RT);ORS=RT} 1' Input_file | head -10
user_58665800

Warning: Self promotion ahead!

I wrote an awk-like utility called tawk that uses tcl as its scripting language, and has a mode that reads CSV data without having to hack around with regular expressions to handle records with embedded newlines and quotes (This feature was actually my major inspiration for it).

Using it:

$ tawk -csv 'line {$NR <= 5} { puts [regsub -all {\n+} $F(0) "\\n"]; if {$NR == 5} exit }' input.csv
ID,username,mobile,city,Message,Address,city
'11111111',TestUSer,1234567890,test,"Hi how are you? Well: we will connnect\nThanks for your time!\nWith Joy.\nTest",Address test,City test
11111116,TestUser,1234567891,test,hello msg,Address test1,City test1
'111111167',TestUSer,1234567890,test,"Hi how are you one? Well: we will connnect\nThanks for your time!\nWith Joy.\nTest",Address test,City test
11111112,TestUser,1234567891,test1,hello msg1,Address test2,City test2
user_99521960
Related