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?



# Environment
filelines=`cat $filename`

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

1 Answer 1


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:

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"

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

  • @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 .