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

Issue

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

Solution

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

Answered By - RavinderSingh13

Answer Checked By - Candace Johnson (WPSolving Volunteer)