Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Working with CSV files values enclosed with ""

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Working with CSV files values enclosed with ""
# 1  
Old 12-18-2013
Working with CSV files values enclosed with ""
I have a CSV file as shown below
Code:
"1","SANTHA","KUMAR","SAM,MILLER","DEVELOPER","81,INDIA"
"2","KAPIL","DHAMI","ECO SPORT","DEVELOPER","82,INDIA"

File is comma delimited.All the field values are enclosed by double quotes. But while using awk or cut, it interprets the comma which is present in text field (enclosed by "") as a seperate fields.

eg:
Code:
awk -F',' '{print NF}' File

above command will give output as 8 fields for first record,
7 fields for second record but actually it is 6

how can i neglect the comma which is enclosed in ".
Last edited by radoulov; 12-18-2013 at 02:35 PM..
# 2  
Old 12-18-2013
It's easy with Perl:
Code:
perl -MText::ParseWords -nle'
  print parse_line(",",0, $_)+0;
  ' infile

And with GNU awk >= 4:
Code:
awk '{ print NF }' FPAT='([^,]+)|("[^"]+")' infile

This User Gave Thanks to radoulov For This Post:
santhansk
# 3  
Old 12-18-2013
Thanks
Thanks for your response. could you please explain the awk command
Last edited by santhansk; 12-18-2013 at 02:46 PM..
# 4  
Old 12-18-2013
Read Remove the values from a certain column without deleting the Column name in a .CSV file by RudiC - Shell Programming and Scripting - Unix Linux Forums and adapt to your needs.
Last edited by RudiC; 12-18-2013 at 03:02 PM..
# 5  
Old 12-18-2013
You may try if awk < 4

Code:
$ cat file
"1","SANTHA","KUMAR","SAM,MILLER","DEVELOPER","81,INDIA"
"2","KAPIL","DHAMI","ECO SPORT","DEVELOPER","82,INDIA"

Code:
awk '      {
             column = 0
               $0   = $0","                                 
while($0)  {
             match($0,/ *"[^"]*" *,|[^,]*,/)
             substr($0,RSTART,RLENGTH)            
             ++column
             $0=substr($0,RLENGTH+1)                 
           }
             print column
           }
     ' file

Code:
$ sh tester.sh 
6
6

# 6  
Old 12-18-2013
Also some ideas on Remove the values from a certain column without deleting the Column name in a .CSV file - Page 2 | Unix Linux Forums | Shell Programming and Scripting
# 7  
Old 12-18-2013
Quote:
Originally Posted by santhansk
Thanks for your response. could you please explain the awk command
It's explained in the GNU awk manual, check Defining Fields By Content.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Bash script - Print an ascii file using specific font "Latin Modern Mono 12" "regular" "9"

Hello. System : opensuse leap 42.3 I have a bash script that build a text file. I would like the last command doing : print_cmd -o page-left=43 -o page-right=22 -o page-top=28 -o page-bottom=43 -o font=LatinModernMono12:regular:9 some_file.txt where : print_cmd ::= some printing... (1 Reply)
Discussion started by: jcdole
1 Replies

2. Shell Programming and Scripting

Using sed command to replace "|" with ^ for all *.dat files in a folder not working

I am trying to use the below sed command to replace all "|" to ^, in a folder had 50 dat files. when i tried with 1 file it worked but when i tried with wild card, is not working. sed -i 's/"|"/\^/g' *.dat Is this the proper way to use sed command thank you very much for help. (3 Replies)
Discussion started by: cplusplus1
3 Replies

3. Shell Programming and Scripting

Delete all log files older than 10 day and whose first string of the first line is "MSH" or "<?xml"

Dear Ladies & Gents, I have a requirement to delete all the log files in /var/log/test directory that are older than 10 days and their first line begin with "MSH" or "<?xml" or "FHS". I've put together the following BASH script, but it's erroring out: for filename in $(find /var/log/test... (2 Replies)
Discussion started by: Hiroshi
2 Replies

4. 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

5. 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

6. Shell Programming and Scripting

Get values from 2 files - Complex "for loop and if" awk problem

Hi everyone, I've been thinking and trying/changing all day long the below code, maybe some awk expert could help me to fix the for loop I've thought, I think I'm very close to the correct output. file1 is: <boxes content="Grapes and Apples"> <box No.="Box MT. 53"> <quantity... (8 Replies)
Discussion started by: Ophiuchus
8 Replies

7. Shell Programming and Scripting

"find . -printf" without prepended "." path? Getting path to current working directory?

If I enter (simplified): find . -printf "%p\n" then all files in the output are prepended by a "." like ./local/share/test23.log How can achieve that a.) the leading "./" is omitted and/or b.) the full path to the current directory is inserted (enclosed by brackets and a blank)... (1 Reply)
Discussion started by: pstein
1 Replies

8. Shell Programming and Scripting

awk command to replace ";" with "|" and ""|" at diferent places in line of file

Hi, I have line in input file as below: 3G_CENTRAL;INDONESIA_(M)_TELKOMSEL;SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL My expected output for line in the file must be : "1-Radon1-cMOC_deg"|"LDIndex"|"3G_CENTRAL|INDONESIA_(M)_TELKOMSEL"|LAST|"SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL" Can someone... (7 Replies)
Discussion started by: shis100
7 Replies

9. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies
Login or Register to Ask a Question