0

My CSV file looks like this -

1,2,3,4,5,6,7,8
"AK","87","86","81","83","78",,

How do I transform it like this -

1,2,3,4,5,6,7,8
"AK","87","86","81","83","78","",""
2
  • Why two ,"",""? Shouldn't it be: "AK","87","86","81","83","78",""? – Arkadiusz Drabczyk Sep 19 '19 at 19:44
  • @ArkadiuszDrabczyk note the two commas as the end. CSV delimits fields with a comma, so each comma should be surrounded by fields, so there is a field after the second comma, too. – derobert Sep 19 '19 at 19:48
3

I think a simple sed command will do:

sed -e 's/^,/"",/' -e 's/,$/,""/' -e 's/,,/,"",/g' file

Ouput (if there's a leading comma):

1,2,3,4,5,6,7,8
"","AK","87","86","81","83","78","",""

The first expression deals with a bare comma at the beginning of the line; the second expression deals with a bare comma at the end of the line; the third expression deals with misssing fields in the middle.

0
0

Sounds like you're doing csv processing manually. I don't know of any built-in csv reader in any language that wouldn't be able to process those empty fields without explicit quotes. You might want to rethink the way you process your csv file instead of trying to patch it.

5
  • pgfutter is not able to process it. – eTEs Sep 19 '19 at 21:15
  • \COPY doesn't fit the bill? – Michael Kelso Sep 19 '19 at 21:23
  • No. If you use \copy, you have to create the table ahead of time. – eTEs Sep 19 '19 at 21:50
  • contact pgfutter dev. tell him to fix it. – Michael Kelso Sep 19 '19 at 22:11
  • Wouldn’t it be better to ask the developer if he would consider fixing it? – Stephen Kitt Sep 20 '19 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.