2

I have a file with ; separators ; my application parses the data, based on those separators. String fields are between "" and in columns, I might have the separator as text. I would like to replace the separator in that file ( for example changing it to |) ignoring the values between "". An example follows:

Input:

"BARCELONA";"";"Country.666;53;98";15;19;"test";4343;63647;655848

OUTPUT

"BARCELONA"|""|"Country.666;53;98"|15|19|"test"|4343|63647|655848

3

To get your precise output,

$ perl -MText::CSV -lpe '
    BEGIN{$p = Text::CSV->new({sep_char => ";"})} 
    $_ = join "|", map { /^\d+$/ ? $_ : qq("$_") } $p->fields() if $p->parse($_)
  ' INPUT
"BARCELONA"|""|"Country.666;53;98"|15|19|"test"|4343|63647|655848

If you don't insist on the unconventional quoting, then

$ csvformat -d';' -D'|' INPUT
BARCELONA||Country.666;53;98|15|19|test|4343|63647|655848

Forcing quote non-numeric for both input and output almost works the way you want (but apparently promotes the numeric fields to floating-point):

$ csvformat -d';' -u2 -D'|' -U2 INPUT
"BARCELONA"|""|"Country.666;53;98"|15.0|19.0|"test"|4343.0|63647.0|655848.0
1

Changing data in fields is not safe.

For parsing csv files, you could use csvtool

$ echo '"BARCELONA";"";"Country.666;53";15' | csvtool -t ';' col 1- -
BARCELONA,,Country.666;53,15

or for particular fields

$ echo '"BARCELONA";"";"Country.666;53";15' | csvtool -t ';' col 1,3 -
BARCELONA,Country.666;53    
0

I thought it would be easy to do it with awk, but it ended up becoming a bit of a mess. However, since I went through the effort of solving it, here is a potential solution with awk that takes advantage of the FPAT feature:

cat file.txt | awk -v FPAT='[^;]+|"[^"]+"' '{ for( col=1; col<=NF; col++ ) { printf "%s|", $col; } print "" }'

Basically, it uses the FPAT regex to define what is classified as a item (either no semicolon or quotes), and reprint them with a (|) character between columns. Originally, I wanted to use the OFS option, but it looks like it doesn't play well with the FPAT option

  • It is not working. echo '"BARCELONA";"";"Country.666;53";15' | awk -v FPAT='[^;]+|"[^"]+"' '{ for( col=1; col<=NF; col++ ) { printf "%s|", $col; } print "" }' "BARCELONA";"";"Country.666;53";15| – Mathew Linton May 17 at 5:42
  • 1
    @Jason K Lai The cat someFile | awk <awkCommands> construct is a useless use of cat. awk <awkCommands> someFile is enough. – Httqm May 17 at 6:58
  • I really like this one. – Lucas Ramage May 18 at 2:11
0

You could do this using GNU sed editor with extended regexes enabled:

$ sed -Ee 's/(("[^"]*")+|[^;]+);/\1|/g' in.csv

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.