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
Vlastimil Burián
  • 28,462
Uncelvel
  • 345

1 Answers1

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.)

ilkkachu
  • 138,973
Philippos
  • 13,453
  • @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 '17 at 07: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 '17 at 07: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 '17 at 07: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 '17 at 08:19
  • @ilkkachu Thank you so much, I learned something new again – Philippos May 31 '17 at 08:36
  • Thanks brothers @ilkkachu and @Philippos that amazing. This solution is better than https://unix.stackexchange.com/questions/344794/bash-converting-date-in-a-csv-file-with-awk-or-other-linux-tool-csvcut in my case. – Uncelvel Jun 01 '17 at 01:38