Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Convert csv to pipe delimited except the ones in double quotes

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert csv to pipe delimited except the ones in double quotes
# 1  
Old 08-07-2012
Convert csv to pipe delimited except the ones in double quotes
I have a csv data file :
Code:
A,B,C,D,"A,B",E,"GG,H"
E,F,G,H,I,J,"S,P"

I need to replace all "," with "|" except the ones between double quotes i.e
Code:
A|B|C|D|"A,B"|E|"GG,H"
E|F|G|H|I|J|"S,P"

CAn someone assist?
Last edited by Franklin52; 08-07-2012 at 04:10 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 08-07-2012
https://www.unix.com/shell-programmin...ited-file.html
# 3  
Old 08-07-2012
Borrowing from Scrutinizer's idea...Smilie
Code:
awk 'NR%2{gsub(/,/,"|")}1;END{printf("\b")}' RS='"' ORS='"' infile

The action corresponding to the END pattern will take care of the last ORS (extra).
Last edited by elixir_sinari; 08-08-2012 at 04:21 AM..
# 4  
Old 08-07-2012
The post closes there with no result on awk.

Only the perl approach works, I am looking out for awk approach

Code:
perl -MText::ParseWords -nle'
  print join "|", parse_line(",",0, $_);
  ' infile

awk -F'"' '{gsub(/,/,"|",$1);gsub(/,/,"|",$3);} 1' yourFile

# 5  
Old 08-07-2012
Quote:
Originally Posted by Shivdatta
The post closes there with no result on awk.
Have you seen my post???
# 6  
Old 08-07-2012
yes thanks, that works fine but w/ nawk, btw can you please explain what NR%2 is doing here and what does '1' at the end mean in gsub

Code:
$ cat dd
A,"B,B",C,D,"A,B",E,"GG,H"
"E,F",F,G,H,I,J,"S,P"

$ nawk 'NR%2{gsub(/,/,"|")}1' RS='"' ORS='"' dd
A|"B,B"|C|D|"A,B"|E|"GG,H"
"E,F"|F|G|H|I|J|"S,P"

# 7  
Old 08-07-2012
As you might have realised, the input (and also the output) record separator is a ". So, all odd-numbered records (NR%2) are outside the double-quotes "realm" and all the , within these records need to be replaced with |. The other records do not need to be bothered about. The 1 at the end of the script prints all the records irrespective of the substitution.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Linux convert Comma delimited file to pipe

I have file in linux with comma delimited and string fields in double quotations ", I need to convert them to pipe delimiter please share your inputs. Example: Input: "2017-09-30","ACBD,TVF","01234",NULL,18,NULL,"686091802","BANK OF ABCD, LIMITED, THE",790456 Output: ... (4 Replies)
Discussion started by: shieksir
4 Replies

2. Shell Programming and Scripting

Convert pipe demilited file to vertical tab delimited

Hi All, How can we convert pipe delimited ( or comma ) file to vertical tab (VT) delimited. Regards PK (4 Replies)
Discussion started by: prasson_ibm
4 Replies

3. Shell Programming and Scripting

Replace Double quotes within double quotes in a column with space while loading a CSV file

Hi All, I'm unable to load the data using sql loader where there are double quotes within the double quotes As these are optionally enclosed by double quotes. Sample Data : "221100",138.00,"D","0019/1477","44012075","49938","49938/15043000","Television - 22" Refurbished - Airwave","Supply... (6 Replies)
Discussion started by: mlavanya
6 Replies

4. UNIX for Dummies Questions & Answers

Need to convert a pipe delimited text file to tab delimited

Hi, I have a rquirement in unix as below . I have a text file with me seperated by | symbol and i need to generate a excel file through unix commands/script so that each value will go to each column. ex: Input Text file: 1|A|apple 2|B|bottle excel file to be generated as output as... (9 Replies)
Discussion started by: raja kakitapall
9 Replies

5. Shell Programming and Scripting

Pipe delimited to csv

I have a small quandry. I had server reports that I pulled from a database that came out pipe "|" delimited. The developers have now changed the format to CSV. The issue is that some fields have quotes around the text and other fields are blank with strings of commas denoting each field. To further... (2 Replies)
Discussion started by: dagamier
2 Replies

6. Shell Programming and Scripting

Pivoting csv field from pipe delimited file

Hello All, Thanks for taking time to read through the thread and for providing any possible solution. I am trying to pivot a comma separated field in a pipe delimited file. Data looks something like this: Field1|Field2 123|345,567,789 234|563,560 345|975,098,985,397,984 456|736 Desired... (8 Replies)
Discussion started by: svks1985
8 Replies

7. Shell Programming and Scripting

How to convert a space delimited file into a pipe delimited file using shellscript?

Hi All, I have space delimited file similar to the one as shown below.. I need to convert it as a pipe delimited, the values inside the pipe delimited file should be as highlighted... AA ATIU2345098809 009697 005374 BB ATIU2345097809 005445 006518 CC ATIU9685098809 003215 003571 DD... (7 Replies)
Discussion started by: nithins007
7 Replies

8. Shell Programming and Scripting

Convert CSV file (with double quoted strings) to pipe delimited file

Hi, could some help me convert CSV file (with double quoted strings) to pipe delimited file: here you go with the same data: 1,Friends,"$3.99 per 1,000 listings",8158here " 1,000 listings " should be a single field. Thanks, Ram (8 Replies)
Discussion started by: Ram.Math
8 Replies

9. Shell Programming and Scripting

How to remove extra double quotes from string in a delimited file??

Hi Unix Gurus.. I am new to Unix. Please help me. The file I am getting is as follows: Input File "2011-07-06 03:53:23","0","I","NOT SET ",,,,"123985","SAW CUT CONCRETE SLAB 20"THICK",,"98.57","","EACH","N" "2011-07-06 03:53:23","0","I","NOT SET ",,,,"204312","ARMAFLEX-1 3/8 X... (2 Replies)
Discussion started by: BICC
2 Replies

10. Shell Programming and Scripting

convert a pipe delimited file to a':" delimited file

i have a file whose data is like this:: osr_pe_assign|-120|wg000d@att.com|4| osr_evt|-21|wg000d@att.com|4| pe_avail|-21|wg000d@att.com|4| osr_svt|-11|wg000d@att.com|4| pe_mop|-13|wg000d@att.com|4| instar_ready|-35|wg000d@att.com|4| nsdnet_ready|-90|wg000d@att.com|4|... (6 Replies)
Discussion started by: priyanka3006
6 Replies
Login or Register to Ask a Question