Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Handling Comma in string values in a CSV file

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Handling Comma in string values in a CSV file
# 1  
Old 08-11-2014
Handling Comma in string values in a CSV file
Hi have a comma separated file which has numeric and string columns. String columns are quoted and can have comma in between the quotes. How to identify the columns with FS =","

sample records
Code:
"prabhat,kumar",19,2000,"bangalore,India"

In awk it should be
Code:
$1 = prabhat,kumar

$2=19

$3=2000 and

$4=bangalore,india

Setting FS="," is creating the problem.


Moderator's Comments:
Mod Comment Please wrap CODE tags for code, input & output/errors
Last edited by rbatte1; 08-11-2014 at 09:20 AM.. Reason: Added CODE & ICODE tags
# 2  
Old 08-11-2014
Hello Prabhat,

Please refer the forum rules and use the code tags for the commands and codes. Also please let us know your problem in detailed manner with input and expected output too.


Thanks,
R. Singh
# 3  
Old 08-11-2014
Hello R.SIngh


My Input records in file is
Code:
"prabhat,kumar",19,2000,"bangalore,India"

While handling in awk with FS=","

My 1st column should be prabhat,kumar
2nd column : 19
3rd column 2000
4th column bangalore,india

My question is how to treat comma between the quotes as a part of the string value not as a field separator

thanks you..
m new to this forum..
Smilie
Last edited by rbatte1; 08-11-2014 at 09:20 AM..
# 4  
Old 08-11-2014
Searching these fora sometimes helps; in this case it shows (amongst others):Help with cut or awk command
How to match fields surrounded by double quotes with commas?
Please be inspired and feel free to adapt to your needs...
# 5  
Old 08-11-2014
Hello Prabhat,

Please use the following code for same.

Code:
awk -F"," '{for(i=1;i<=NF;i++){if($i ~ /^\"/ || $i ~ /\"$/) {a=a OFS $i;j++;{if(j%2==0){sub(/^[[:space:]]/,X,a); print a;j=0;a=""}}} else {print $i}}}' filename

Output will be as follows. So you can save this in a variable in spite of printing it. kindly let me know if you have any queries for same.

Code:
"prabhat kumar"
19
2000
"bangalore India"


Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
prabhat.diwaker
# 6  
Old 08-11-2014
What if my output should be
Code:
"prabhat,kumar"|19|2000|"bangalore,India"

plz help



Moderator's Comments:
Mod Comment Please use CODE tags for code, input & output/errors
Last edited by rbatte1; 08-11-2014 at 09:54 AM.. Reason: Added CODE tags
# 7  
Old 08-11-2014
Hello Prabhat,

kindly use the following code for same.

Code:
awk -F"," '{for(i=1;i<=NF;i++){if(i%NF==0){ORS="\n"} {if($i ~ /^\"/ || $i ~ /\"$/) {a=a OFS $i;j++;{if(j%2==0){sub(/^[[:space:]]/,X,a); print a;j=0;a=""}}} else {print $i}}}}' ORS="|"  filename

Output will be as follows.

Code:
"prabhat kumar"|19|2000|"bangalore India"


EDIT:

Following code will work for multiple lines. Please use the following for a file previous one will work only for a line.

Code:
awk -F"," '{for(i=1;i<=NF;i++){if(i%NF==0){ORS="\n"} {if($i ~ /^\"/ || $i ~ /\"$/) {a=a OFS $i;j++;{if(j%2==0){sub(/^\,/,X,a); print a;j=0;a=""}}} else {print $i}}} {ORS="|"}}' ORS="|" OFS=,   filename


output will be as follows.

Code:
"prabhat,kumar"|19|2000|"bangalore,India"
"prabhat,kumar"|19|2000|"bangalore,India"

When input file is as follows.

Code:
cat test3
"prabhat,kumar",19,2000,"bangalore,India"
"prabhat,kumar",19,2000,"bangalore,India"


Thanks,
R. Singh
Last edited by RavinderSingh13; 08-11-2014 at 10:14 AM.. Reason: Adding a better solution
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to delete a columns of a CSV file which has cell values with a string enclosed in " , "?

Hi How can I delete a columns from a CSV file which has comma separated value with a string enclosed in double quotes or square bracket and a comma in between? I have a csv file with below format. Template,Target Server,Target Component,Rule Group,Rule,Rule Reference Number,Rule... (7 Replies)
Discussion started by: Litu19
7 Replies

2. Shell Programming and Scripting

How to delete a column/columns of a CSV file which has cell values with a string enclosed in " , "?

How can I delete a column from a CSV file which has comma separated value with a string enclosed in double quotes and a comma in between? I have a file 44.csv with 4 lines including the header like the below format: column1, column2, column3, column 4, column5, column6 12,455,"string with... (6 Replies)
Discussion started by: dhruuv369
6 Replies

3. Shell Programming and Scripting

Need Help - comma inside double quote in comma separated csv,

Hello there, I have a comma separated csv , and all the text field is wrapped by double quote. Issue is some text field contain comma as well inside double quote. so it is difficult to process. Input in the csv file is , 1,234,"abc,12,gh","GH234TY",34 I need output like below,... (8 Replies)
Discussion started by: Uttam Maji
8 Replies

4. Shell Programming and Scripting

handling CSV file to get desired output

Hi All , i have a CSV file , pattern is given below :- Group # name # host # account # stop # # start # # check -------------------------------------------------------------------------- file format and data exmaple :- RBP2,RB0112,sihrb001,tksrb011,. ./.profile 1>/dev/null 2>&1;stop_olc_dmn... (0 Replies)
Discussion started by: deepakiniimt
0 Replies

5. UNIX for Advanced & Expert Users

Replacing the comma in .csv file in unix

Hi All, Could some one help me on one of my requirement below: I have a sequential file with 4fields in it and it is a comma (,) seperated file. Delimeter is 'comma'. But in of the file column for ex: 3rd column it is 'Description' (column name) I am getting the values with commas.... (6 Replies)
Discussion started by: eskay_s
6 Replies

6. Shell Programming and Scripting

Using awk/sed in handling csv file.

Please study the below script and the output Script: echo "Minimum ${host} ${process} response time=${min} ms" >> ${OUTDIR}/${OUTFILE}; echo "Maximum ${host} ${process} response time=${max} ms" >> ${OUTDIR}/${OUTFILE}; echo "Average ${host} ${process} response time=${avg} ms" >>... (0 Replies)
Discussion started by: ajincoep
0 Replies

7. Shell Programming and Scripting

Need to compare two csv files values and write into another csv file

Hi all, Am new to scripting. So i just need your ideas to help me out. Here goes my requirement. I have two csv files 1.csv 2.csv abc,1.24 abc,1 def,2.13 def,1 I need to compare the first column of 1.csv with 2.csv and if matches then need to compare... (2 Replies)
Discussion started by: chinnahyd
2 Replies

8. Shell Programming and Scripting

Handling .CSV( Comma seperated value) in awk

Hi Guys, I am trying to reading in comma seperated values in awk. I can set the delimiter to be a comma, but the tricky part is that commas that appear within quotes are not to be considered as delimiters. Could someone please help. Regards, Laud (1 Reply)
Discussion started by: Laud12345
1 Replies

9. UNIX for Advanced & Expert Users

How to load comma seperated values file (*.csv) into Oracle table

Hi all I need to input values in a .csv file into my Oracle table running in Unix, I wonder what would be the command to do so... The values are recorded in an excel file and I tried using a formatted text file to do so but failed because one of the field is simply too large to fit in the... (4 Replies)
Discussion started by: handynas
4 Replies

10. UNIX for Dummies Questions & Answers

How to load comma seperated values file (*.csv) into Oracle table

Hi all I need to input values in a .csv file into my Oracle table running in Unix, I wonder what would be the command to do so... The values are recorded in an excel file and I tried using a formatted text file to do so but failed because one of the field is simply too large to fit in the... (5 Replies)
Discussion started by: handynas
5 Replies
Login or Register to Ask a Question