Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replace date_time to unixtime in csv.file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace date_time to unixtime in csv.file
# 1  
Old 05-05-2010
Replace date_time to unixtime in csv.file
Hello,
since hours I am trying to replace in a csv-file the date_time to unixtime.
I tried sed, awk but not successful. I can not call a shell command within awk.
Probably there is an easier way.
Thanks in advance for your help
Regards, telemi

test.csv:
Code:
2010-04-22 01:59:01;test1;0.0;0.0;0.0
2010-04-22 01:59:02;test1;0.0;0.0;0.0
2010-04-22 01:50:01;test1;0.0;0.0;0.0
2010-04-22 01:01:04;test1;0.0;0.0;0.0
2010-04-22 02:00:11;test2;0.0;0.0;0.0
2010-04-22 01:59:06;test1;0.0;0.0;0.0
2010-04-22 01:59:11;test1;0.0;0.0;0.0
2010-04-22 01:14:01;test1;0.0;0.0;0.0
2010-04-22 01:59:01;test1;0.0;0.0;0.0


Code:
awk '
BEGIN { FS = "[;]" }
{
   sub($1, Shell-Command date -d "$1" "+%s")
   print
}
' test.csv

Last edited by Scott; 05-05-2010 at 06:44 PM.. Reason: Please use code tags
# 2  
Old 05-05-2010
okay, so I'll confess that my system lacks the GNU version of the date util, so I'm not sure what %s relates to...however, a poor man's approach might be this:

Code:
cut -d';' -f2,3,4,5 test.csv |sed "s/^/$(date +date_string%S\; )/g"

# 3  
Old 05-05-2010
Code:
awk -F";" -v qt=\" '
{
   "date -d " qt$1qt" "qt"+%s"qt  | getline ts
   sub($1, ts)
   print
} ' file

# 4  
Old 05-06-2010
Code:
awk -F";" '{ print $1 ;}' test.csv| xargs -I{} date -d {} +%s

# 5  
Old 05-08-2010
It works now!
Hi,
No very nice command line but at least it works:

Code:
/tmp$ awk -F";" '{ print $1 ;}' test.csv| xargs -I{} date -d {} +%s > temp.csv ; paste -d ';' tt.csv test.csv; rm -f temp.csv


1271894341;2010-04-22 01:59:01;test1;0.0;0.0;0.0
1271894342;2010-04-22 01:59:02;test1;0.0;0.0;0.0
1271893801;2010-04-22 01:50:01;test1;0.0;0.0;0.0
1271890864;2010-04-22 01:01:04;test1;0.0;0.0;0.0
1271894411;2010-04-22 02:00:11;test2;0.0;0.0;0.0
1271894346;2010-04-22 01:59:06;test1;0.0;0.0;0.0
1271894351;2010-04-22 01:59:11;test1;0.0;0.0;0.0
1271891641;2010-04-22 01:14:01;test1;0.0;0.0;0.0
1271894341;2010-04-22 01:59:01;test1;0.0;0.0;0.0

Last edited by Scott; 05-08-2010 at 09:10 AM.. Reason: Code tags, please...
# 6  
Old 05-08-2010
Since you do have GNU date, I will assume you have gawk too. In that case this will be more efficient and simpler:
Code:
gawk -F\; -v OFS=';' '{x=$1;gsub(/:/," ",x);print mktime(x),$0}' test.csv

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

[bash] - Replace blank and string in csv file

Hi all, i have a .csv file with only two columns, like: Login;Status Luca;S Marco; Stefano; Elettra;S Laura; ... I need to replace the blank space on Status column whit Enabled end, on the same column, S whit Disabled, like: Login;Status Luca;Disabled Marco;Enabled Stefano;Enabled... (10 Replies)
Discussion started by: kamose
10 Replies

2. Shell Programming and Scripting

CSV file REPLACE COLUMN if it matches

I have a file cat 1.txt AAAA , BBBB , CCCC , DDDD DFDF , DFDF , DFDF , FDDD AA11 , DFDF , 0000 , UTIO ADSD , WERT, 0000 , JKJL If the 3rd column is not equal to "0000" , then it should replace "0000" with "XXXX" and if its equal to "0000" then print the line as it is. need help. (9 Replies)
Discussion started by: aravindj80
9 Replies

3. Shell Programming and Scripting

Replace value in csv file with match from another file

Good morning everyone and hello unix.com! I'm trying to solve a problem and was unable to find a solution after a forum research. So hopefully you can help me get this solved. I have these two csv files: file1.csv ID,REFERENCE,STATUS 1,2,0 2,4,1 3,1,0 file2.csv... (2 Replies)
Discussion started by: RKos
2 Replies

4. Shell Programming and Scripting

HELP with AWK or SED. Need to replace the commas between double quotes in CSV file

Hello experts, I need to validate a csv file which contains data like this: Sample.csv "ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0 "ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0 I just need to check if all the records contain exactly the number of... (5 Replies)
Discussion started by: shell_boy23
5 Replies

5. Shell Programming and Scripting

search and replace in csv file

I have csv file where I want the second column has to be replaced with value 1. Source file 919568760477,1,2011-07-11T22:34:27.000+05:30, 919557735692,2,2011-07-11T22:36:16.000+05:30, 917417384969,2,2011-07-11T22:33:26.000+05:30, Final file ... (30 Replies)
Discussion started by: dondilip
30 Replies

6. Shell Programming and Scripting

Replace 2nd column of CSV file with numbers on line

I have a csv file with occasional multiple entries in the second column. 111111,104,07-24-2011,3.15,N, 222222,020 140,07-24-2011,10.00,N,I want the result 111111,104,07-24-2011,3.15,N, 222222,020,07-24-2011,10.00,N, 222222,140,07-24-2011,10.00,N, I know I can get the output of the second... (5 Replies)
Discussion started by: ffdstanley
5 Replies

7. Shell Programming and Scripting

Replace a particular field in all records in a csv file

hi, i have various csv files, the file format is as follows Entry: "1",4,2010/08/15-10-00-00.01,,"E",,,,,,,,,120,0,"M4_","C","KEW-011-5337140-20100916163456-540097","1234567890","N N 0 ",,,"NUK 800100200",,,"NN",,,,,,,,,,,,"0000000001|0001|20150401... (2 Replies)
Discussion started by: niteesh_!7
2 Replies

8. Shell Programming and Scripting

find & replace comma in a .csv file.

HI, Please find the text below. I receive a .csv file on server. I need the comma(,) in the second column to be replaced by a semi-colon( ; ). How to do it. Please help. Sample text: "1","lastname1,firstname1","xxxxxx","19/10/2009","23/10/2009","0","N","Leave"... (2 Replies)
Discussion started by: libin4u2000
2 Replies

9. Shell Programming and Scripting

replace value with double quotes of specific coulmn value in csv file

Hi, I am trying to replace a specific column values in a csv file with double quotes. Example: SNO,NAME,ZIPCODE,RANK 1,Robert,74538,12 2,Sam,07564,13 3,Kim, Ed,12345,14 Desired Output: SNO,NAME,ZIPCODE,RANK 1,Robert Ken,74538,12 2,Sam Mik,"07564",13 3,"Kim, Ed",12345,14 I... (3 Replies)
Discussion started by: techmoris
3 Replies

10. Shell Programming and Scripting

replace a field in a CSV file

Hello all, I've a CSV file and need to replace 5th field if its value is "X". The exact requirement is to replace 5th field (column) with "Y" if a. it's value is "X" AND b. the line must start with ABC string i guess this can be done with awk. Pl help. For security reasons, the... (2 Replies)
Discussion started by: prvnrk
2 Replies
Login or Register to Ask a Question