1

Please support
I have below data in file in comma seperated, but 4th column is containing comma in between numbers, bcz of which when i tried to parse the file the column 6th value(5049641141) is being removed from the file and value(222.82) in column 5 becoming value of column6.

Below is command used 

awk -F"," -v OFS="|" '{gsub(/\,/,"",$4);gsub(/\"/,"");gsub(/^[[:space:]]+|[[:space:]]+$/,"",$4);gsub(/^[[:space:]]+|[[:space:]]+$/,"",$5);print $1, $2, $3, $4, $5, $6}' input.csv > output.csv

Input file

 Bill.Doc.,Item,Exch.Rate,    Net value, Tax amount,Sales Doc.
6400392211,10,1," 1,271.19 ", 228.82 ,5049641141
6400392212,10,1, 635.59 , 114.40 ,5049641143
6400392213,10,1, 635.59 , 114.40 ,5049641145
6400392214,10,1," 1,271.19 ", 228.82 ,5049641147

output file(from command mentioned above)

  Bill.Doc. |Item|Exch.Rate|Net value|Tax amount|Sales Doc.
6400392211|10|1|1|271.19|228.82
6400392212|10|1|635.59|114.40|5049641143
6400392213|10|1|635.59|114.40|5049641145
6400392214|10|1|1|271.19|228.82
6400392215|10|1|0.01|-|5049641149
6400392216|10|1|4.23|0.76|5049641151

Expected output

Bill.Doc. |Item|Exch.Rate|Net value|Tax amount|Sales Doc.
6400392211|10|1|1271.19|228.82|5049641141
6400392212|10|1|635.59|114.40|5049641143
6400392213|10|1|635.59|114.40|5049641145
6400392214|10|1|1271.19|228.82|5049641147
6400392215|10|1|0.01|-|5049641149
6400392216|10|1|4.23|0.76|5049641151
as7951
  • 187
  • 3
  • 11
  • Lol, I wrote an answer, forgot to hit Save and went for coffee. :D Oh well, should've had that coffee earlier. – James Brown Apr 13 '18 at 06:25
  • @JamesBrown...cant see your answer..how can i view..can you please help – as7951 Apr 13 '18 at 06:45
  • I was using `BEGIN{FPAT = "([^,]*)|(\"[^\"]+\")"}` instead of `-F","`. Check out the linked questions, they had some nice examples. – James Brown Apr 13 '18 at 06:49
  • @JamesBrown..Apologies..but can you please post complete answer with BEGIN{FPAT = "([^,]*)|(\"[^\"]+\")"} – as7951 Apr 13 '18 at 07:10

0 Answers0