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


Login or Register to Reply

 
Thread Tools Search this Thread
# 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 02:42 AM.. Reason: Add CODE and ICODE tags, again.
# 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

# 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 05: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 (11-30-2016)
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
awk to parse comma separated field and removing comma in between number and double quotes as7951 Shell Programming and Scripting 3 04-14-2018 02:09 PM
awk to ignore whitespace in field cmccabe Shell Programming and Scripting 4 08-29-2017 08:29 AM
awk to update field using matching value in file1 and substring in field in file2 cmccabe Shell Programming and Scripting 2 06-18-2017 07:38 AM
awk to adjust coordinates in field based on sequential numbers in another field cmccabe Shell Programming and Scripting 3 01-30-2017 07:39 AM
String has * as the field delimiter and I need echo/awk to escape it, how? newbie_01 UNIX for Beginners Questions & Answers 13 01-07-2017 07:29 AM
Add a field separator (comma) inside a line of a CSV file Tawpie UNIX for Dummies Questions & Answers 9 06-15-2014 08:09 PM
awk :how to change delimiter without giving all field name Lakshman_Gupta Shell Programming and Scripting 2 10-21-2013 06:52 AM
AWK: Pattern match between 2 files, then compare a field in file1 as > or < field in file2 right_coaster Shell Programming and Scripting 4 10-06-2011 06:07 PM
how to find the nth field value in delimiter file in unix using awk Jairaj Shell Programming and Scripting 4 08-15-2011 06:17 AM
awk output field delimiter jimmy_y Shell Programming and Scripting 1 08-08-2011 01:06 AM
need to convert a decimal value to an ascii char with awk for the field delimiter script_op2a Shell Programming and Scripting 1 12-06-2010 06:40 PM
awk, comma as field separator and text inside double quotes as a field. kevintse Shell Programming and Scripting 8 11-15-2010 05:31 PM
Add field delimiter for the last field mehimadri Shell Programming and Scripting 5 07-22-2010 01:17 PM
Printing Field with Delimiter in AWK/cut Jahn UNIX for Advanced & Expert Users 1 09-02-2009 08:32 AM
Set a variable field delimiter using awk Klashxx Shell Programming and Scripting 2 03-24-2006 08:10 AM