1

I have a comma-delimited CSV file but for some reason our system inserts a new line character at a random location in the file which causes the entire file to break. I can get the number of columns in the file.

How do I solve it with sed and/or perl in a one liner command? I know it's solvable with awk but this is for learning purposes. If using perl, I don't want to use the built-in CSV functions. Is it solvable?? I'm on this problem for several days i can't seem to find a solution :(

Sample malformed input (lots of randomly inserted \n)

policyID,statecode,county,Point longitude,Some Thing Here,point_granularity
119736,FL,CLAY COUNTY,-81.711777,“Residential Lot”,1
448094,FL,CLAY COUNTY,-81.707664,“Residen
tial Lot”,3
206893,FL,CLAY COUNTY,-81.7
00455,“Residen
tial Lot”,1
333743,FL,CLAY COUNTY,-81.707703,“Residential Lot”,
3
172534,FL,CLAY COUNTY,-81.702675,“Residential Lot”,1
785275,FL,CLAY COUNTY,-81.707703,“Residential Lot”,3
995932,FL,CLAY COUNTY,-81.713882,
“Residential Lot”,1
223488,FL,CLAY COUNTY,-81.707146,“Residential Lot”,1
4335
12,FL,CLAY COUNTY,-81.704613,
“Residential Lot”,1

Required output

policyID,statecode,county,Point longitude,Some Thing Here,point_granularity
119736,FL,CLAY COUNTY,-81.711777,“Residential Lot”,1
448094,FL,CLAY COUNTY,-81.707664,“Residential Lot”,3
206893,FL,CLAY COUNTY,-81.700455,“Residential Lot”,1
333743,FL,CLAY COUNTY,-81.707703,“Residential Lot”,3
172534,FL,CLAY COUNTY,-81.702675,“Residential Lot”,1
785275,FL,CLAY COUNTY,-81.707703,“Residential Lot”,3
995932,FL,CLAY COUNTY,-81.713882,“Residential Lot”,1
223488,FL,CLAY COUNTY,-81.707146,“Residential Lot”,1
433512,FL,CLAY COUNTY,-81.704613,“Residential Lot”,1
Improve this question
5
  • Find why the newlines are inserted and fix that. That would be the best resolution to the issue.
    – Kusalananda ♦
    Apr 2, 2018 at 6:33
  • Guess: Newlines are inserted when some internal buffer is full, and written out.
    – dirkt
    Apr 2, 2018 at 6:36
  • For the actual question: Your lines start and end with digits, so removing all newlines not between digit will be a start, but this won't fix the file completely. To detect the other newlines, I guess one would need to add knowledge about how the fields look like.
    – dirkt
    Apr 2, 2018 at 6:38
  • How would you fix it in awk and why can't you do something similar in Perl or sed?
    – muru
    Apr 2, 2018 at 6:56
  • on this problem for several days please add at least one of those attempts to question..
    – Sundeep
    Apr 2, 2018 at 7:50

2 Answers 2

Reset to default
3 
$ awk -F, '{ while (NF < 6 || $NF == "") { brokenline=$0; getline; $0 = brokenline $0}; print }' file.csv
policyID,statecode,county,Point longitude,Some Thing Here,point_granularity
119736,FL,CLAY COUNTY,-81.711777,“Residential Lot”,1
448094,FL,CLAY COUNTY,-81.707664,“Residential Lot”,3
206893,FL,CLAY COUNTY,-81.700455,“Residential Lot”,1
333743,FL,CLAY COUNTY,-81.707703,“Residential Lot”,3
172534,FL,CLAY COUNTY,-81.702675,“Residential Lot”,1
785275,FL,CLAY COUNTY,-81.707703,“Residential Lot”,3
995932,FL,CLAY COUNTY,-81.713882,“Residential Lot”,1
223488,FL,CLAY COUNTY,-81.707146,“Residential Lot”,1
433512,FL,CLAY COUNTY,-81.704613,“Residential Lot”,1

The awk code will append the next line of input to the current line for as long as there is less than six fields in the current line, or the last field is empty (there is one line that is broken just after the last field separator).

A Perl workalike:

perl -ne 'chomp;while (tr/,/,/ < 5 || /,$/) { $_ .= readline; chomp } print "$_\n"' file.csv
Improve this answer
6
  • thank you for your response! i was trying to make it work with any type of delimiter like so: perl -ne 'chomp;while (tr/quotemeta('"$d2"')/quotemeta('"$d2"')/ < '"$columns"'-1 || /quotemeta('"$d2"')$/) { $_ .=readline; chomp } print "$_\n"' where $d2 for example is a bash variable with value || but it doesnt work :(
    – Harry McKenzie
    Apr 2, 2018 at 12:44
  • @HarryMcKenzie I would use the awk solution for that as it's easier to just change the option-argument for the -F flag.
    – Kusalananda ♦
    Apr 2, 2018 at 12:46
  • yeah but i need to use perl :(
    – Harry McKenzie
    Apr 2, 2018 at 12:47
  • @HarryMcKenzie I don't quite see why.
    – Kusalananda ♦
    Apr 2, 2018 at 12:47
  • 1
    what if the CSV values contain commas?
    – Bernie
    Aug 28, 2018 at 23:58
1

Like say by Kusalananda, there is 6 fields on each line, so you can try this gnu sed.

sed -E ':A;h;s/^/,/;s/((,[^,]+){6})(.*)/\3/;/./{g;N;s/\n//;bA};g' infile
Improve this answer
3
  • but unfortunately doesn't work. you didnt test it :p
    – Harry McKenzie
    Apr 2, 2018 at 13:10
  • Is your sed gnu sed? On Openbsd, I must change the code like that : sed -Ee ':A;h;s/^/,/;s/((,[^,]+){6})(.*)/\3/;/./{g;N;s/\n//;bA' -e '};g' infile
    – ctac_
    Apr 2, 2018 at 13:41
  • im using sed gnu. but thank you very much for helping me, it works now! thank you :)
    – Harry McKenzie
    Apr 4, 2018 at 11:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .