I have a pipe separated file with two major lines. One is header and another is detail line. Header starts with H and Detail start with D.
Sample Content:
Here Date format should be in mm/dd/yyyy but file has date format in different format - m/dd/yyyy or mm/d/yyyy or m/d/yyyy. If the format is not correct then a leading zero should be added to make it in mm/dd/yyyy like 4/5/2016 to 04/05/2016.
I tried few awk printf commands but nothing works out for multiple occurrences within the line.
Please let me know your possible way to crack this scenario.
Thanks,
Mannu
Moderator's Comments:
edit by bakunin: CODE, not ICODE-tags, please. Thank you for your consideration.
It looks to me as though when it's a header ($1 == 'H') you are looking at fields 5 and 6 ($5 and $6). When it's a detail line you are looking at fields 4 and 5.
I don't know awk but is seems to me you need a construct like:
As for the modification for the date you need to split on '/' and rebuild using printf.
I don't really know awk so the above construct is probably nonsensical.
Concentrate on modifying field 5 and then try putting the above construct in for the other fields.
Here Date format should be in mm/dd/yyyy but file has date format in different format - m/dd/yyyy or mm/d/yyyy or m/d/yyyy. If the format is not correct then a leading zero should be added to make it in mm/dd/yyyy like 4/5/2016 to 04/05/2016.
In your sample there seem to be no other data similar to dates, so the following abbreviation may (or may not, depending on how representative your sample is) work:
Replace every instance of a single digit followed by a slash by this sequence with a "0" prepended. This would amount to a simple sed-command:
This will replace in i.e. "|4/5/2016" the "|4/" with "|04/" and then the "/5/" to "/05/".
You can try this awk script. It looks for fields looking like dates, splits them into month, day and year and recombines them using a proper format.
Very old versions of awk, like /usr/bin/awk on Solaris do not understand sprintf. You should be on the safe side with GNU-awk or nawk though.
Hello hergp,
Thank you for nice code. I think you could change (match ($field, ".*/.*/.*")) to (match($field,/[0-9]+\/[0-9]+\/[0-9]+/)), which will get only digits(for matching dates only).
Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
Location: Saint Paul, MN USA / BSD, CentOS, Debian, OS X, Solaris
Posts: 2,288
Thanks Given: 430
Thanked 480 Times in 395 Posts
Hi.
A suite of programs dealing with many aspects of dates is dateutils, found in many repositories ( ArchLinux, Debian, Fedora, FreeBSD, Gentoo, NetBSD, OpenSuSE, OS, Slackware, Ubuntu ).
Most of the codes scan the input lines for data that looks like a date format, then processes it. In this case we ask dconv to read it and write it with the same format:
producing:
If not in your repository or you cannot change your system, see the github source -- that is how I originally obtained the codes until dateutils appeared in my repository.
In your sample there seem to be no other data similar to dates, so the following abbreviation may (or may not, depending on how representative your sample is) work:
Replace every instance of a single digit followed by a slash by this sequence with a "0" prepended. This would amount to a simple sed-command:
This will replace in i.e. "|4/5/2016" the "|4/" with "|04/" and then the "/5/" to "/05/".
I hope this helps.
bakunin
Not quite. If I have one of the OP's input lines:
the output I get is:
This is because the first match on the BRE is |1/ and you want to match /5/ to change the 5 to 05, but the leading / for the 2nd match was consumed as the trailing / in the previous match.
This can be fixed by applying that substitute command twice:
or with a slightly simpler second substitute command:
(using x as the substitution delimiter instead of / to avoid a few backslashes).
Note that this sed solution will also work if a date field also includes time (e.g., 1/5/2016 11:23:45) while the awk solutions suggested will fail in this case. And note that this sed solution may "fix" too much if there is a field containing a simple fraction where the numerator is a single digit (e.g., 1/2 off will become 01/2 off and 2/3 majority will become 02/3 majority). None of these are problems with the given sample input, but with all of those empty fields in the sample we have to wonder what else might appear in those field in other input lines???
Hi All,
I am using a informatica job to create a csv file and a unix script the mail the generated file.Everything is working fine but I am not seeing leading zeros in the csv file sent in the mail.These zeros were present when the .csv file was generated by informatica procees.
Is there any... (11 Replies)
I have a list of numbers extracted and need to delete the leading zeros from them, but when i do so, the command I am using also deletes numbers that end in Zero as well. eg 10, 20, 30, etc
this is part of a larger script and the only way I can think of is to try and detect the 10,20 30 etc in... (19 Replies)
Hi,
i have a variable which conatins values like 00001,0003,00067,00459.
I want to use the values one by one and in the same form as they are like 00001,0003,00067,00459.
Also can anyone tell me how to increment those numbers by 1,keeping the format as same like 00002,0004,00068,00460.... (5 Replies)
Hello Gurus,
Quick question. I have a file with the following records:
A~000000000000518000~SLP ~99991231~20090701~88.50~USD~CS~
A~000000000000518000~SLP ~99991231~20090701~102.00~USD~CS~
A~000000000000772000~SLP ~99991231~20100701~118.08~USD~CS~
I wold like to do the following:
1. Add... (1 Reply)
Hi
i need help in adding leading zero to filenames
e.g file name in my folder are
1_234sd.txt
23_234sd.txt
the output i need is
001_234sd.txt
023_234sd.txt
can i do this shell scripting
please help (2 Replies)
Hi all,
I have file with numeric values. I need to pad each value with leading zeros such that total lenght of each value is 16.
Example:
cat tmp.txt
502455
50255
5026
5027
5028
Output
0000000000502455
0000000000050255
0000000000005026
0000000000005027
0000000000005028
Any... (12 Replies)
Hi
I have a file in which I have 5 columns which are delimited by “|” as shown
ABC|12|YAK|METRIC|000000019.5
XYZ|10|ABX|META|000000002.5
Now my requirement is to take the last column trim the leading zero's for that column values and write back to the same file in the same... (7 Replies)
Hi All,
I am working with a fixed width file Forrmat.
C1 Number (10,3)
C2 Number (10,3)
e.g.
c1= 0000000100.000
c2= 0000000020.000
0000000100.0000000000020.000
I have to perform c1 - c2 . i.e. I want answer to be 0000000080.000. but I am loosing the leading zeros( only getting... (3 Replies)
How to insert leading zeros into a left-justisfied zip code?
e.g. Zip code is written as 60320 which is left-justified to make it be read as 0060320.
We have to move it to right-justifiable then insert 2 leading zeros into it... ;) (1 Reply)