1

I have a csv file (named a.csv) exported from DB mongo like this:

...
ABC,DN,1000,RENEW_DAY,Thu Apr 27 2017 23:19:47 GMT+0700 (ICT),1,1,4,-1
ANSLAS,DN,1000,RENEW_DAY,Thu Apr 27 2017 23:19:52 GMT+0700 (ICT),1,1,4,-1
...

I want to import this file to MySQL but this date format cannot be inserted. So I have an idea to convert this to be b.csv before insert to MySQL

...
ABC,DN,1000,RENEW_DAY,2017-04-27 23:19:47,1,1,4,-1
ANSLAS,DN,1000,RENEW_DAY,2017-04-27 23:19:52,1,1,4,-1
...

This is the bash command bash I use to convert date in bash date -d"Thu Apr 27 2017 23:19:52 GMT+0700 (ICT)" +'%Y-%m-%d %H:%M:%S'

So how can I convert this automatically with the bash shell like this ./process.sh a.csv b.csv?

process.sh

#!/bin/bash 

# Environment
filename=$1
filelines=`cat $filename`

for phone in $filelines ;
do
    <HOW CAN CUT Filed Date and convert this and export to b.csv file>
done
Improve this question
0

1 Answer 1

Reset to default
3

You can use the read command to separate the fields. Therefore you must set the field separator variable to , to indicate that the fields are comma-separated. You can later change it back if you need to do other things in the script:

#!/bin/bash
oldIFS=$IFS
IFS=,
while read a b c d e f g h i; do
  echo "$a,$b,$c,$d,$(date -d"$e" +'%Y-%m-%d %H:%M:%S'),$f,$g,$h,$i"
done < "$1"
IFS=$oldIFS

(This assumes GNU date, -d in e.g. FreeBSD date does something different.)

Improve this answer
6
  • @ilkkachu Thanks for the edit. Can you please explain your setting of double quotes. Could some line contents break the script when you leave the double quotes away? Thanks.
    – Philippos
    May 31, 2017 at 7:19
  • unquoted variables go through word splitting and then wildcard expansion, see here and here. Though in this case, you'd probably get away with it, since the variables were already split by read (except maybe the last one, if there were more fields than expected), and the whole string probably won't match any file name even if there is a * or something in the variables. But since we don't want splitting or globbing, quoting is a good practice.
    – ilkkachu
    May 31, 2017 at 7:40
  • @ilkkachu Ah. I didn't care about splitting in the context of echo, but I didn't think of globbing. Thank you. I tried to generate an example that leads to failure, but didn't succeed.
    – Philippos
    May 31, 2017 at 7:53
  • 1
    Splits with echo would change the amount of white space, and with a changed IFS, it would change the separator to spaces. try IFS=,; read a b <<< 1,2,3 ; echo $a,$b; echo "$a,$b" . Though in this context you could just change IFS only for the read, with IFS=, read ... (without the semicolon in between)
    – ilkkachu
    May 31, 2017 at 8:19
  • @ilkkachu Thank you so much, I learned something new again
    – Philippos
    May 31, 2017 at 8:36

Not the answer you're looking for? Browse other questions tagged .