Sponsored Content
Operating Systems Linux How do I format a Date field of a .CSV file with multiple commas in a string field? Post 302864085 by dhruuv369 on Tuesday 15th of October 2013 11:53:57 PM
Old 10-16-2013
How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below:

Code:
column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1, name","890","88","11-OCT-11","12"
"4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455","author2, name","12","455","12-OCT-11","55"
"11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3, name","333","22","13-OCT-11","232"

The 9th field is the date field in the format "DD-MMM-YY". I have to convert it to the format YYYY/MM/DD. I am trying to use the below code, but of no use.

Code:
awk -F, '
 BEGIN {
 split("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC", month, " ")
 for (i=1; i<=12; i++) mdigit[month[i]]=i
 }
 { m=substr($9,4,3)
 $9 = sprintf("%02d/%02d/"20"%02d",mdigit[m],substr($9,1,2),substr($9,8,20))
 print
 }' OFS="," file.csv > temp_file.csv

The out put of the file temp_file.csv after executing the above code is as shown below.

Code:
column1,column2,column3,column4,column5,column6,column7,Column8,00/00/2000,Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1,00/00/2000,"890","88","11-OCT-11","12"
"4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455",00/00/2002, name","12","455","12-OCT-11","55"
"11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3,00/00/2000,"333","22","13-OCT-11","232"

As far as I understand, the issue is with the commas in the double quote as my code is taking them into consideration too... Please suggest on the below questions:

1) Does the double quoting all the values in all the fields make any difference? If they make any difference, how do I get rid of them from all the values except the strings with commas in them?
2) Any modifications to my code so I could format the 9th field which in the format "DD-MMM-YYYY" to YYYY/MM/DD
 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

change field 2 date format

from this input WEBELSOLAR,29122009,1:1 WIPRO,15062010,2:3 ZANDUREALT,18012007,1:3 i want output as WEBELSOLAR,20091229,1:1 WIPRO,20100615,2:3 ZANDUREALT,20070118,1:3 basically input is in ddmmyyyy format and i was to convert it to yyyymmdd format (1 Reply)
Discussion started by: manishma71
1 Replies

2. Shell Programming and Scripting

csv file field needs to be changed current system date with awk

HI, I have csv file with records as shown below. 4102,Bangalore,G10,21,08/17/2011 09:28:33:188,99,08/17/2011 09:27:33:881,08/17/2011... (1 Reply)
Discussion started by: raghavendra.nsn
1 Replies

3. Shell Programming and Scripting

Replace field with commas with field without commas

Hey guys, I have the following text: 1,2,3,4,5,6,'NULL','when',NULL,1,2,0,'NULL' 1,2,3,4,5,6,'NULL','what','NULL',1,2,0,1 I need the same text with the word NULL without commas u know something like this: 1,2,3,4,5,6,NULL,'when',NULL,1,2,0,NULL 1,2,3,4,5,6,NULL,'what','NULL',1,2,0,1 ... (1 Reply)
Discussion started by: lmyk72
1 Replies

4. Shell Programming and Scripting

CSV with commas in field values, remove duplicates, cut columns

Hi Description of input file I have: ------------------------- 1) CSV with double quotes for string fields. 2) Some string fields have Comma as part of field value. 3) Have Duplicate lines 4) Have 200 columns/fields 5) File size is more than 10GB Description of output file I need:... (4 Replies)
Discussion started by: krishnix
4 Replies

5. Shell Programming and Scripting

Field validations in multiple files CSV

Hi, I am regular reader of this forum. My advanced thanks to everyone. Below given are the sample files INDATA (Main data) Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. Fild1Çfld2Çfld3….. . . N records (140000) eg GRPDATA (Reference file) (2 Replies)
Discussion started by: hyperion.krish
2 Replies

6. Shell Programming and Scripting

awk - CSV file - field with single or multiple spaces

Hi, In a csv file, I want to select records where first column has zero or multiple spaces. Eg: abc.csv ,123,a ,22,b ,11,c a,11,d So output should be: ,123,a ,22,b ,11,c Please advise (5 Replies)
Discussion started by: vegasluxor
5 Replies

7. Shell Programming and Scripting

Print particular string in a field of csv file

Hi, all I need your help and suggestions. I want to print particular strings in a field of a csv file and show them in terminal. Here is an example of the csv file. SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw... (9 Replies)
Discussion started by: refrain
9 Replies

8. Shell Programming and Scripting

Print particular string in a field of csv file - part 2

Hi, all I need your help and suggestions. I want to print particular strings in a field of a csv file and show them in terminal. Here is an example of the csv file. SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw... (7 Replies)
Discussion started by: refrain
7 Replies

9. Shell Programming and Scripting

CSV Split field to check multiple codes

Hello, For work i am trying to generate a combined csv file excisting out of 2 other csv files. The problem i am facing is that the first field on both files have multiple values in there which arent always the same. This first field is also the joining part. The layout of the files is as... (16 Replies)
Discussion started by: SDohmen
16 Replies

10. UNIX for Advanced & Expert Users

Help changing date format in the nth field

Hi, I have two (2) things that I want to do. First is to change the date format that is in the nth field from MM/DD/YY to YY/MM/DD. Preferably, I wish I know how to make it a 4-digit year but I don't. Problem is I can only assume it is a 20 century Second is somehow know how to figure out... (1 Reply)
Discussion started by: newbie_01
1 Replies
DEALER.DPP(6)							User Documentation						     DEALER.DPP(6)

NAME
dealer.dpp - preprocessor for dealer scripts SYNOPSIS
dealer.dpp [inputfile] DESCRIPTION
dealer.dpp stands for dealer-pre-processor. Its main purpose it to preprocess input files for Hans van Staveren's great dealer program. SYNTAX
The program expands shape specifications of the form: shape{COMPASS, shp1 + shp2 + ... + shpM - shm1 - ... - shmN} regular shapes 7xxx, 4432, 4xx5 have the same meaning as in original dealer. the `at least' operator whenever you put a `+' after a suit-length, it means `at least'. shape{north, 5+xx5+}: at least 5/5 in the blacks, -->shape(north,5xx5 + 5xx6 + 6xx5 + 5xx7 + 6xx6 + 7xx5 + 5008 + 6007 + 7006 + 8005) the `at most' operator: same as the `at least', just use a `-': shape{north, 2-xxx}: at most 2 spades, -->shape(north,0xxx + 1xxx + 2xxx) the `range' operator: the range operator operates on a single suit. It comes with [], like in Perl. shape{north, x[3-5]x[13]}: (3 to 5) hearts, and one or 3 clubs -->shape(north,x3x1 + x4x1 + x3x3 + x5x1 + x4x3 + x5x3) Note that shape{north, 3-xxx} is equivalent to shape{north,[0-3]xxx} You can write compound ranges if you want: shape{north, [013-68]xxx} means: 0,1,3,4,5,6 or 8 spades. the `permutation' operator: the permutation operator will appears as () like in the WBF rules of specifying shapes. shape{north, (4432)}: any 4432 hand. -->shape(north,any 4432) what's best is that the () operator can operate on restricted groups of cards: shape{north, 5s(431)}: 5 spades, the others suits being(431). -->shape(north,5134 + 5314 + 5143 + 5341 + 5413 + 5431) What you have to do is: o specify the suit names (s,h,d,c in english) in the left part of the shape, in any order, o and enclose the rest of the permutation into () in the right part. A (complicated) example: shape{north, 4+c3+d(2+2+)}: at least 5 clubs, at least 3 diamonds, and no short major. -->shape(north,3334 + 4234 + 2434 + 3244 + 2344 + 2254 + 3235 + 2335 + 2245 + 2236) the `Major' operator: has to be used together with the () operator. `M' stands for `a major' The `M' operator can only be used in the left part (never put a M inside the parens) shape{north, 5M(xxx)} -->shape(north,x5xx + 5xxx) shape{north, 5+M3+c(31)} -->shape(north,1534 + 3514 + 1633 + 3613 + 5134 + 5314 + 6133 + 6313) the `minor' operator: same as the `Major' operator, but in lowercase `m'. shape{north, 5M5m(xx)} -->shape(north,x5x5 + 5xx5 + x55x + 5x5x) the `conditional' operator: you've got the possibility of specifying complex conditions to be verified that cannot be simply expressed by other operators. In order to do that, you use the `:' operator, just after the atomic-shape specification: shape{north, shp1:condition1,condition2,...} The conditions use the letters (s,h,d,c) as variables for the suit lengths, and the usual C-operators (*,+,-,/,>,<,==,!,?, and so on). Note that the `,' stands for the `and-logical' operator. You can use `or' for the `or-logical' operator. shape{north, 4+s4+h(xx):d>c,h+s==10} -->shape(north,6421 + 6430 + 5521 + 5530 + 4621 + 4630) EXAMPLES
a balanced hand (3+3+3+2+) a french 1H opening x5+xx:h>s,h>=d,h>=c my No_trump opening hn = hcp(north) n1NT = (hn > 14 and hn < 18 and shape{north, 5m(332) + 5m2s(42)} ) or (hn > 13 and hn < 17 and shape{north, 6m(322)} ) or (hn > 15 and hn < 18 and shape{north, 5M(332)} ) or (hn > 15 and hn < 19 and shape{north, (4432) + (4333)} ) SEE ALSO
dealer(6), /usr/share/doc/dealer/Manual. AUTHORS
Francois Dellacherie Manpage: Christoph Berg <cb@df7cb.de> LICENSE
This program is hereby put in the public domain. Do with it whatever you want, but I would like you not to redistribute it in modified form without mentioning the fact of modification. I will accept bug reports and modification requests, without any obligation of course, but fixing bugs someone else put in is beyond me. Dealer 2012-03-04 DEALER.DPP(6)
All times are GMT -4. The time now is 03:38 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy