Link to home
Start Free TrialLog in
Avatar of rleyba828
rleyba828Flag for Australia

asked on

Need assistance crafting a bash script to convert csv to date-time format

Hi Team,

  Just requesting some help converting a csv file to a date-time format so that i can import it properly to a mysql database.  The original file looks like this

08/11/2012,232444,av12345,configure terminal,10.2.2.2
08/11/2012,232602,av12345,configure terminal,10.2.2.199
08/11/2012,232616,av12345,configure terminal,10.2.2.200
08/11/2012,233022,av12345,configure terminal,10.2.2.201
08/11/2012,233026,av12345,configure terminal,10.2.3.202
08/11/2012,233026,av12345,configure terminal,10.2.3.4
08/11/2012,233026,av12345,configure terminal,10.1.2.3
08/11/2012,234030,av12345,configure terminal,10.9.8.7
09/11/2012,160831,av12346,configure terminal,10.8.7.6
09/11/2012,160906,av12346,configure terminal,10.1.2.3

Open in new window


And I would like to have the first field in the csv converted like so:
2012-11-08 23:24:44,av12345,configure terminal,10.2.2.2
2012-11-08 23:26:02,av12345,configure terminal,10.2.2.199
2012-11-08 23:26:16,av12345,configure terminal,10.2.2.200
2012-11-08 23:30:22,av12345,configure terminal,10.2.2.201
2012-11-08 23:30:26,av12345,configure terminal,10.2.3.202
2012-11-08 23:30:26,av12345,configure terminal,10.2.3.4
2012-11-08 23:30:26,av12345,configure terminal,10.1.2.3
2012-11-08 23:40:30,av12345,configure terminal,10.9.8.7
2012-11-09 16:08:31,av12346,configure terminal,10.8.7.6
2012-11-09 16:09:06,av12346,configure terminal,10.1.2.3

Open in new window


Any tips with the bash script would be much appreciated.  I have basic knowledge of awk and grep (and almost none of sed) but am not sure how I can use these commands when it comes to shuffling characters around.

Thanks very much.
Avatar of skullnobrains
skullnobrains

08/11/2012,232444,av12345,configure terminal,10.2.2.2
2012-11-08 23:24:44,av12345,configure terminal,10.2.2.2

Open in new window


sed -e 's;^\([0-9][0-9]\)/\([0-9][0-9]\)/\([0-9]\{4}\),\([0-9][0-9]\)\([0-9][0-9]\)\([0-9][0-9]\);\3-\2-\1,\4:\5:\6;

pipe the input foile into this sed command, and read the output. should do it

you can also edit the file in place using this syntax (replace the .... with the above ugly stuff)

sed -i tmp -e 's;...' filename
Avatar of rleyba828

ASKER

Hi,

  I tried your solution above but I seem to be getting some syntax errors.  I tried playing with the '   delimiter which appears to be missing from the example above, but I just get the response below.  I am using centos 5.5 by the way.      pass-1.txt is my input file.

thanks


 

[root@myserver]# sed -i tmp -e 's;^\([0-9][0-9]\)/\([0-9][0-9]\)/\([0-9]\{4}\);' pass-1.txt
sed: -e expression #1, char 47: unterminated `s' command
[root@myserver]# sed -e 's;^\([0-9][0-9]\)/\([0-9][0-9]\)/\([0-9]\{4}\);' pass-1.txt
sed: -e expression #1, char 47: unterminated `s' command

Open in new window

i forgot a quote at the end of the command i posted

and you forgot part of the sed command

sed -i tmp -e 's;^\([0-9][0-9]\)/\([0-9][0-9]\)/\([0-9]\{4}\),\([0-9][0-9]\)\([0-9][0-9]\)\([0-9][0-9]\);\3-\2-\1,\4:\5:\6;' pass-1.txt

this should do
Hi....actually, you are correct...I missed part of the command as when I got the errors...I tried to simplify the commands to the point where it might show where the error started...  although on my terminal I did type in the full command..... I am getting the error below this time.

I'm also curious about your s; construct.  I was doing man page of sed and in there and in most of examples i see, it was always s/.      What does s; mean?

I did trial and error but no luck.

This is what I am getting now.  could it be because we are not using the same bash version?

I also printed out my pass-1.txt file.


[root@root@myserver]# sed -i tmp -e 's;^\([0-9][0-9]\)/\([0-9][0-9]\)/\([0-9]\{4}\),\([0-9][0-9]\)\([0-9][0-9]\)\([0-9][0-9]\);\3-\2-\1,\4:\5:\6;' pass-1.txt
sed: -e expression #1, char 108: Invalid content of \{\}
[root@root@myserver]# cat pass-1.txt
08/11/2012,232444,av12345,configure terminal,10.2.2.2
08/11/2012,232602,av12345,configure terminal,10.2.2.199
08/11/2012,232616,av12345,configure terminal,10.2.2.200
08/11/2012,233022,av12345,configure terminal,10.2.2.201
08/11/2012,233026,av12345,configure terminal,10.2.3.202
08/11/2012,233026,av12345,configure terminal,10.2.3.4
08/11/2012,233026,av12345,configure terminal,10.1.2.3
08/11/2012,234030,av12345,configure terminal,10.9.8.7
09/11/2012,160831,av12346,configure terminal,10.8.7.6
09/11/2012,160906,av12346,configure terminal,10.1.2.3
[root@myserver]#

Open in new window

SOLUTION
Avatar of arober11
arober11
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi team,

These are excellent responses!   Thanks to all for the insights and the explanations.