How can I apply 'date' command to specific columns, in a BASH script?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How can I apply 'date' command to specific columns, in a BASH script?
# 1  
Old 06-14-2018
Hammer & Screwdriver How can I apply 'date' command to specific columns, in a BASH script?

Hi everyone,

I have a situation in which I have multiple (3 at last count) date columns in a CSV file (, delim), which need to be changed from:
January 1 2017 (note, no comma after day)
to:
YYYY-MM-DD

So far, I am able to convert a date using:
date --date="January 12, 1990" +%Y-%m-%d

However, I need to read a specific column in the CSV, then replace what's there with the new format. It doesn't matter which commonly used GNU utility I use, I just need to do it.

So far, I have been trying to write an AWK function in a BASH shell script to do it, but I'm completely lost.

Can anyone offer suggestions on the easiest way to accomplish this from within a BASH script? The attached sample.txt gives an idea of what the data looks like, only greatly reduced. The really data has about 3000 rows and 35 columns.

Thanks,

Rich
# 2  
Old 06-14-2018
what's your OS/awk version?
Do you have gawk?
# 3  
Old 06-14-2018
Quote:
Originally Posted by vgersh99
what's your OS/awk version?
Do you have gawk?
GNU Awk 4.1.3, API: 1.1 (GNU MPFR 3.1.4, GNU MP 6.1.0)

Using Ubuntu on Windows 10 Enterprise in the Linux subsystem.
Thanks,

R
# 4  
Old 06-14-2018
awk -f rich.awk myInput.csv where rich.awk is:
Code:
BEGIN {
 FS=OFS=","
 mon="January|February|March|April|May|June|July|August|September|October|November|December"
   monN=split(mon, monA, "|");
   for(i=1; i<=monN; i++) {
     monA[monA[i]]=i;
     delete monA[i];
   }

   if (!col) col="3"

   colN=split(col, colA, " ")
}

function conv(str,  strA)
{
   split(str, strA, " ")
   return(sprintf("%d-%02d-%02d", strA[3], monA[strA[1]], strA[2]))
}

FNR>1 {
   for(i=1; i<= colN; i++)
     $colA[i]=conv($colA[i])
}
1

By default this will convert column 3. You can specify multiple columns to be converted by:
awk -v col='3 5 whateverColNum' -f rich.awk myInput.csv
This User Gave Thanks to vgersh99 For This Post:
# 5  
Old 06-14-2018
You wanted to use the date command ... try
Code:
grep -Eo "[A-Z][a-z]* [0-9]{1,2} [0-9]{4}" /tmp/sample.txt |
LC_ALL=C date -f- +"%B %-d %Y:%F" |
awk '
NR == FNR       {T[$1] = $2
                 next
                }
FNR > 1         {$3 = T[$3]
                 $5 = T[$5]
                }
1
' FS=: - FS=, OFS=, /tmp/sample.txt

This User Gave Thanks to RudiC For This Post:
# 6  
Old 06-14-2018
Thank you both! I will try both approaches.

Smilie

R
# 7  
Old 06-14-2018
Another variety:
Code:
awk '
  BEGIN {
    FS=OFS=","
    split("January,February,March,April,May,June,July,August,September,October,November,December", MNUM)
    for(i in MNUM)
      MON[MNUM[i]]=i                                                                         
  }                          
  {
    for(i=1; i<=NF; i++)
      if ($i ~ /^[[:alpha:]]+ [0-9]{1,2} [0-9]{4}$/) {
        split($i, D, " ")
        $i=sprintf("%d-%02d-%02d",D[3],MON[D[1]],D[2])
      }                                               
    print
  }      
' file


Last edited by Scrutinizer; 06-15-2018 at 04:33 AM.. Reason: FS was defined in the wrong place. Thanks RudiC..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to apply functions to multiple columns dynamically

Hello, I have a requirement to apply hashing algorithm on flat file on one or more columns dynamically based on header sample input file ID|NAME|AGE|GENDER 10|ABC|30|M 20|DEF|20|F say if i want multiple columns based on the header example id,name or id,age or name,gender and hash and... (13 Replies)
Discussion started by: mkathi
13 Replies

2. Shell Programming and Scripting

Bash to select text and apply it to a selected file in bash

In the bash below I am asking the user for a panel and reading that into bed. Then asking the user for a file and reading that into file1.Is the grep in bold the correct way to apply the selected panel to the file? I am getting a syntax error. Thank you :) ... (4 Replies)
Discussion started by: cmccabe
4 Replies

3. Shell Programming and Scripting

Script to check for a specific number of columns in a file

Hi All I have a file which has five columns in each rows. cat file.txt a|b|c|d|e 1|2|3|4|5 a1|a2|a3|a4|a5 . . . I need to make sure that there are no less than five or more than five columns (in all the rows) by mistake. I tried this : cat file.txt | awk 'BEGIN{FS="|"};{print... (3 Replies)
Discussion started by: chatwithsaurav
3 Replies

4. Shell Programming and Scripting

Perl script to accept specific columns from excel

Hi All, I have below perl script which writes xml from .xls file. Now i want to add below two conditions in this script : 1. to check if the the input .xls file has ony two columns , if more tahn two columns then script should pop up an error. 2. If there are two columns , then first column... (4 Replies)
Discussion started by: omkar.jadhav
4 Replies

5. Shell Programming and Scripting

Read Two Columns - Apply Condition on Six other columns

Hello All, Here is my input univ1 chr1 100 200 - GeneA 500 1 0 0.1 0.2 0.3 0.4 0.5 univ1 chr1 100 200 - GeneA 600 1 0 0.0 0.0 0.0 0.0 0.1 univ1 chr1 100 200 - GeneA 700 1 0 0.4 0.4 ... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

6. UNIX for Dummies Questions & Answers

Want to get list of Linux commands used on specific date through HISTORY command

I want to get list of linux commands used on Jan 01 2014 with the help of HISTORY command or some other linux commands,. Kindly help. (3 Replies)
Discussion started by: karthick nath
3 Replies

7. Shell Programming and Scripting

Need to view command in history for specific date

hi i want to find a command in history for specific date . is it possible that i can view ?? (1 Reply)
Discussion started by: scriptor
1 Replies

8. Shell Programming and Scripting

find command to filter specific type of files older than certain date.

Hi I need to find the list of files in a directory and to do some specific operations based on the type of files. suppose in a directory am having .dat , .log, .err, .rej file types. i need to filter out .dat and .log only which are older than six months. i used the below query but the... (2 Replies)
Discussion started by: msathees
2 Replies

9. Shell Programming and Scripting

script for get lines with specific date

I'm a newbie in AIX, i want to make a script for grep any lines with date bellow 20 PRINT0089-88615 data1 3072 Mon Dec 19 17:53:49 WITA 2011 PRINT0089-88616 data1 4096 Mon Dec 19 17:53:49 WITA 2011 PRINT0089-88618 data1 5120 Mon Dec 19... (7 Replies)
Discussion started by: michlix
7 Replies

10. Shell Programming and Scripting

Perl script to get info from specific rows & columns (.xls file)

Hi all, I want to read some specific rows & columns in the .xls file with my script to get the data to be manipulated. Now, I can read the .xls file correctly & i can go to the specific sheet that I want but i have a problem to specify the specific rows & columns. I mean, I want to get the info... (0 Replies)
Discussion started by: Yohannita
0 Replies
Login or Register to Ask a Question