7

File1

12584,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc
25841,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc
87455,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc

Output

12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc

I have a csv file, which i have to conver into a text file delimited with pipe(|) I have done the shell script sed 's/^/"/;s/,/|/g;s/$/"/' $File > $Output

But the problem is the field "Capital of America, Inc." contains a comma, that's also replaced by the pipe (|). So I just wanted to replace all, with pipe except not inside the value is given double quotes " ".

Is there any shell script to do this?

22

Using csvkit:

$ csvformat -D '|' file.csv
12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc

csvkit is a collection of CSV manipulation/querying tools written in Python. These do proper CSV parsing and csvformat may be used to replace the default comma delimiter with any other character. The utility will make sure that the result is properly quoted according to CSV rules.

  • Quoting rules seem odd for this tool. A line like one , "one""two""t33" , two is converted to one |" ""one""""two""""t33"" "| two with default quoting rules, but after playing with it for a while I am not able to get the (imVHo) correct output of one |one"two"t33| two (because repeated quotes " represent a single "). Even adding the -b option. You may like my solution. – Isaac Jul 25 '18 at 23:09
  • @Isaac But that removes the spaces in the second field. All double quotes in your example's second field is part of the data since there's a space before "one" and after "t33". Double double quotes ("") are used to embed double quotes in quoted fields, and your second field is not quoted (due to the spaces). Removing the spaces (creating a quoted field) yields "one""two""t33" after reformatting with csvformat, which looks correct to me. – Kusalananda Jul 26 '18 at 6:00
  • Hmmm. I could understand the logic which claims that all characters (even spaces) delimited by commas are part of the field (only except when quotes start and end the field). I am not sure what should be the final rule. – Isaac Jul 26 '18 at 6:48
  • @Isaac The rule is that a quoted field starts with a double quote. Embedded double quotes should be doubled, and fields containing embedded double quotes should themselves be quoted. See points 5, 6 and 7 in the "Definition" section of RFC 4180: tools.ietf.org/html/rfc4180 – Kusalananda Jul 26 '18 at 6:55
  • 1
    (Cont.…) (2) Fields with leading or trailing spaces must be delimited with double-quote characters.. I can live with either of the interpretations. Let's agree to not agree on this point. … … I also have decided that an awk (and also sed or grep) solution is a bad idea, CSV records could extend for several lines and awk (sed and grep) are line-oriented. Making it difficult (at the very least) to implement a reasonable solution. +1 :-) – Isaac Jul 26 '18 at 11:57
8

At least on Debian-based systems, you should be able to install the OCaml-based csvtool

$ csvtool -u '|' cat file.csv
12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc

You could also use Perl's Text::CSV module:

$ perl -MText::CSV -lne '
  BEGIN{$p = Text::CSV->new()} 
  print join "|", $p->fields() if $p->parse($_)
' file.csv
12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
  • you have cat randomly placed in your csvtool command. – ctrl-alt-delor Jul 25 '18 at 11:25
  • 1
    @ctrl-alt-delor in this context cat is the internal csvtool command - the usage (at least the version I have) is csvtool [-options] command [command-args] input.csv – steeldriver Jul 25 '18 at 11:36
  • Fail if a quoted field has spaces: 12584," Capital of America, Inc.",,HORI yields 12584|" Capital of America, Inc."||HORI (quotes not removed). – Isaac Jul 25 '18 at 18:20
  • @Isaac are you sure about that? The perl version should (silently) skip lines that it fails to parse. (FYI you can likely make it parse your - broken - example by instantiating the parser with additional options like escape_char => "\\", allow_loose_quotes => 1.) – steeldriver Jul 25 '18 at 19:08
  • Hmm. correct, the error is also for csvtool. A line like 87455|"Capital of"" America"", Inc."||HORIZ yields Fatal error: exception Csv.Failure(5, 2, "Non-space char after closing the quoted field"). – Isaac Jul 25 '18 at 19:38
2

To fix your problem:

awk 'BEGIN{FS=",";OFS="|";} {print $1,$2","$3,$4,$5,$6,$7}' Test | tr -d \"

to generalized problems like this, GNU awk has a FPAT special variable to describe fields:

awk -vFPAT='[^,]*|("[^"]*")' -vOFS='|' '{$1=$1;print}' Test | tr -d \"
12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc

with awk and sed tools, you do not need an extra package. EDIT as Issak mentioned, I update my answer:

awk -vFPAT='[^,]*|(["].*["])' -vOFS='|' '{print $1,$2,$3,$4,$5,$6}' Test | sed 's/\"//g'
  • This will always preserve the second comma on a line, no matter where that comma occurs. Assuming that the file is larger than shown, it is fair to expect that some instances of the second field won't contain commas, or that it may contain more than one comma. – Kusalananda Jul 25 '18 at 10:33
  • my answer is correct for this question, anyway I edit it. – Hossein Vatani Jul 25 '18 at 10:59
  • This fail if a line like 87455,"Capital of" America", Inc.",,HOR. – Isaac Jul 25 '18 at 18:42
2

Using SED:

Option 1:

sed -e 's#,\([^ ]\)#|\1#g;s#"##g;s#|,#||#g' file

12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
  • \([^ ]\) replace all comma which is not followed by space.
  • Then remove " and replace the comma prefixed by |.

usually in a context, a comma will be with space. If not in your case, try the below code.

Option 2:

sed -e  's#^#\n#;:a;s#\n\([^,"]\|"[^"]*"\)#\1\n#;ta;s#\n,#|\n#;ta;s#\n##;s#"##g' file

12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
  • @ Isaac i hope you wouldn't try my second option yet. let me know if you have any concerns then. – msp9011 Jul 25 '18 at 19:46
  • @Petro i hope you wouldn't try my second option yet. let me know if you have any concerns then. – msp9011 Jul 25 '18 at 19:47
  • Regular expressions weren't designed for processing nested elements differently. While it's sometimes possible to have it parse such statements because of the non-regular extensions most engines have nowadays, it is still so easy to get wrong. It's just not a good tool for the job. – jpmc26 Jul 25 '18 at 22:21
  • For me sed -e 's#^#\n#;:a;s#\n([^,"]\|"[^"]*")#\1\n#;ta;s#\n,#|\n#;ta;s#\n##' file not working, not removing " " between Capital of America, Inc. – Juhan Jul 26 '18 at 5:16
  • @Juhan try my updated answer. – msp9011 Jul 26 '18 at 5:19
1

awk, one character at a time

Examine each line of input, one character at a time. Toggle a counter q between 0 and 1 each time you encounter a double-quote mark ", so that q equals 1 inside each pair of double-quote marks (ie, before each closing double-quote mark). Then, depending on q, change commas , into pipe characters |. After each line has been evaluated, print the modified line.

awk '{
  m=""
  q==0
  for (n=1;n<=length($0);n++) {
    p=substr($0,n,1)
    if (p=="\"") { p="" ; q=(q+1)%2 }
    if (p=="," && q==0) p="|"
    m=m p
    }
  print m
  }' file.csv

Input:

12584,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc
25841,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc
87455,"Capital of America, Inc.",,HORIZONCAPITAL,USA,......etc

Output:

12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
1

With standalone perl:

perl -pe 's{"(.*?)"|,}{$1 // "|"}ge' < "$File" > "$Output"

(it assumes the values don't contain |, " or newline characters).

0

With a very short Python script using csv module:

import csv,sys

with open(sys.argv[1]) as csvfile:
    csvr = csv.reader(csvfile)
    for line in csvr:
        print('|'.join(line))

That works as follows:

$ python3 csvfile.py input.csv
12584|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
25841|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc
87455|Capital of America, Inc.||HORIZONCAPITAL|USA|......etc

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.