Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How can awk ignore the field delimiter like comma inside a field?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How can awk ignore the field delimiter like comma inside a field?
# 1  
Old 11-29-2016
How can awk ignore the field delimiter like comma inside a field?
We have a csv file as mentioned below and the requirement is to change the date format in file as mentioned below.

Current file (file.csv)
----------------------
Code:
 empname,date_of_join,dept,date_of_resignation
ram,08/09/2015,sales,21/06/2016
"akash,sahu",08/10/2015,IT,21/07/2016

Required output file
----------------------
Code:
 empname,date_of_join,dept,date_of_resignation
ram,2015-09-08,sales,2016-06-21
"akash,sahu",2015-10-08,IT,2016-07-21

We are using below script for this. This script works fine for first record, but it does not give correct output for second record. Please see the actual output and script below-

Awk Script (This is .ksh file)
-------------------------------

Code:
 #!/bin/ksh
awk 'BEGIN{ FS=OFS="," 
            nf=split("2,4", f)                           # array of input field numbers
            nd=split(",7,4,-,4,2,-,1,2", d)        # array of date subfield info (in output order): prefix(out),pos(in),len(in)         
     }
     { for(i=1; i<=nf; i++){ 
           if($f[i]) {
               fmod=""
               for(j=1; j<=nd; j+=3) fmod=fmod sprintf("%s", d[j] substr($f[i], d[j+1], d[j+2]))
               $f[i] = fmod
           }
       } print
     }' file.csv

Actual Output which is not correct
--------------------------------------
Code:
 ram,2015-09-08,sales,2016-06-21
"akash,-u"-sa,08/10/2015,--IT,21/07/2016

Please suggest me, how Awk can handle the second record which contain comma inside a field?
Last edited by Don Cragun; 11-29-2016 at 03:42 AM.. Reason: Add CODE and ICODE tags, again.
# 2  
Old 11-29-2016
if you have gawk, then you can use FPAT variable to split the data.

The GNU Awk User’s Guide
# 3  
Old 11-29-2016
How about
Code:
awk -F, -vOFS=, '{for (i=1; i<=NF; i++) {split ($i, T, "/"); if (T[1] < 32 && T[2] < 13 && T[3] > 2000) $i = T[3] "-" T[2] "-" T[1]}} 1' file
empname,date_of_join,dept,date_of_resignation
ram,2015-09-08,sales,2016-06-21
"akash,sahu",2015-10-08,IT,2016-07-21

# 4  
Old 11-29-2016
Wrench
Hi Rudi,

Can you please tell me, on which version you have tried? Since I am working in Solaris, it is not working for me.
# 5  
Old 11-29-2016
Where and how does it fail? Any error message? Wrong output?
It works here on linux and FreeBSD.

And, of course:
Quote:
Originally Posted by Don Cragun
If you are using a Solaris/SunOS system, use /usr/xpg4/bin/awk or nawk instead of awk.
# 6  
Old 11-29-2016
Hi Rudi,

On Solaris, awk is giving below error message-

Code:
awk: syntax error near line 1
awk: bailing out near line 1

but when I am tried with nawk, it is giving output but removing all commas like below-

Code:
ram 2015-09-08 sales 2016-06-21
"akash sahu" 2015-10-08 IT 2016-07-21

Here date conversion is working fine but I want comma also between the fields. Please see the below required output-

Code:
ram,2015-09-08,sales,2016-06-21
"akash,sahu",2015-10-08,IT,2016-07-21


Moderator's Comments:
Mod Comment Please wrap code, files, input & output/errors in CODE tags, like this:-
Quote:
[CODE]This is my code[/CODE]
to produce the following (fixed character width, space respected):-
Code:
This is my code

Not only does it make posts far easier to read, but CODE and ICODE sections respect multiple space and have fixed width characters, which is important for easily seeing input/output requirements.
Last edited by rbatte1; 11-29-2016 at 06:19 AM.. Reason: Added CODE tags
# 7  
Old 11-29-2016
Try a little modification:
Code:
-v OFS=","

This User Gave Thanks to RudiC For This Post:
gopal.biswal
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to parse comma separated field and removing comma in between number and double quotes

Hi Experts, Please support I have below data in file in comma seperated, but 4th column is containing comma in between numbers, bcz of which when i tried to parse the file the column 6th value(5049641141) is being removed from the file and value(222.82) in column 5 becoming value of column6. ... (3 Replies)
Discussion started by: as7951
3 Replies

2. Shell Programming and Scripting

awk to ignore whitespace in field

The awk below executes and update the desired field in my first awk. However, the white space between nonsynonymous SNV in $9 is being split into tabs and my attempt to correct this does not update the field unless it is removed. I am not sure what I am doing wrong? Thank you :). file1 ... (4 Replies)
Discussion started by: cmccabe
4 Replies

3. UNIX for Beginners Questions & Answers

String has * as the field delimiter and I need echo/awk to escape it, how?

Hi, I am trying to read an Oracle listener log file line by line and need to separate the lines into several fields. The field delimiter for the line happens to be an asterisk. I have the script below to start with but when running it, the echo command is globbing it to include other... (13 Replies)
Discussion started by: newbie_01
13 Replies

4. UNIX for Dummies Questions & Answers

Add a field separator (comma) inside a line of a CSV file

Hi... I can't find my little red AWK book and it's been a long while since I've awk'd. But I need to take a CSV file and convert the first word of the fifth field to its own field by replacing a space with a comma. This is for importing a spreadsheet of issues into JIRA... Example: a line... (9 Replies)
Discussion started by: Tawpie
9 Replies

5. Shell Programming and Scripting

awk :how to change delimiter without giving all field name

Hi Experts, i need to change delimiter from tab to "," sample test file cat test A0000368 A29938511 072569352 5 Any 2 for Ģ1.00 BUTCHERS|CAT FOOD|400G Sep 12 2012 12:00AM Jan 5 2014 11:59PM Sep 7 2012 12:00AM M 2.000 group 5 ... (2 Replies)
Discussion started by: Lakshman_Gupta
2 Replies

6. Shell Programming and Scripting

awk output field delimiter

Dear All, 1.txt (tab in between each value in a line) a b c a b c a c d you can see below, why with ~ i can output with tab, but = cannot? # awk -F'\t' '$2 ~ /b/' 1 a b c a b c # awk -F'\t' '$2 = "b"' 1 a b c a b c a b d ... (1 Reply)
Discussion started by: jimmy_y
1 Replies

7. Shell Programming and Scripting

awk, comma as field separator and text inside double quotes as a field.

Hi, all I need to get fields in a line that are separated by commas, some of the fields are enclosed with double quotes, and they are supposed to be treated as a single field even if there are commas inside the quotes. sample input: for this line, 5 fields are supposed to be extracted, they... (8 Replies)
Discussion started by: kevintse
8 Replies

8. Shell Programming and Scripting

Add field delimiter for the last field

I have a file with three fields and field delimiter '|' like: abc|12:13:45|123 xyz|12:87:32| qwe|54:21:09 In the file the 1st line has proper data -> abc|12:13:45|123 ,the 2nd line doesnt has data for the 3rd field which is okay , the 3rd line doesnt has data for the 3rd field as well the... (5 Replies)
Discussion started by: mehimadri
5 Replies

9. UNIX for Advanced & Expert Users

Printing Field with Delimiter in AWK/cut

Hello, I had posted earlier about printing fields using AWK, but now I have a slightly different problem. I have text files in the format: 1*2,3,4,5 and wish to print the first, third, and fifth fields, including the asterisk and commas. In other words, after filtering it should look... (1 Reply)
Discussion started by: Jahn
1 Replies

10. Shell Programming and Scripting

Set a variable field delimiter using awk

How can i set a variable field delimiter using awk?? I wanna do something like this ,but i canīt get the correct syntaxis : VARI=TEST echo "0121212TESTxvcshaashd"|awk 'FS="$VARI" {print $2}' Thanks. (2 Replies)
Discussion started by: Klashxx
2 Replies
Login or Register to Ask a Question