CODEWITHSUNDEEP
bashsedawk
KillDash9
KillDash9

Reputation: 909

BASH parsing and generating MYSQL insert

I have the following text line :

"Field1":"Data1","Field2":"Data2","Field3":"Data3","Field4":"Data4" ...

And I need to generate the following INSERT statement :

INSERT INTO data (Field1,Field2,Field3,Field4 ... ) VALUES(Data1,Data2,Data3,Data4 ... );

Any ideas on how to do it in BASH ?

Thanks in advance!

Upvotes: 1

Views: 895

Answers (4)

cdarke
cdarke

Reputation: 44344

Since you specifically asked for a BASH solution (rather than awk, perl, or python):

data='"Field1":"Data1","Field2":"Data2","Field3":"Data3","Field4":"Data4"'

data=${data//,/$'\n'}     # replace comma with new-lines
data=${data//\"/}         # remove the quotes
while IFS=':' read -r field item
do
    if [[ -n $fields ]]
    then
        fields="$fields,$field"
        items="$items,$item"
    else
        fields=$field
        items=$item
    fi
done < <(echo "$data")

stmt="INSERT INTO data ($fields) VALUES($items);"
echo "$stmt"

Upvotes: 1

NeronLeVelu
NeronLeVelu

Reputation: 10039

sed -n 's/$/) VALUES(/
: next
   s/"\([^"]*\)":"\([^"]*\)"\(.*\)) VALUES(\(.*\)/\1\3) VALUES(\4,\2/
   t next
   s/VALUES(,/VALUES(/
   s/.*/INSERT INTO data (&)/
   p
   ' YourFile

Assuming there is no " in data value nor ) VALUES( (could be treated also if needed)

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 203169

$ cat file
"Field1":"Data1","Field2":"Data2","Field3":"Data3","Field4":"Data4"
$
$ cat tst.awk
BEGIN { FS="^\"|\"[:,]\"|\"$" }
{
    fields = values = ""
    for (i=2; i<NF; i+=2) {
        fields = fields (i>2 ? "," : "") $i
        values = values (i>2 ? "," : "") $(i+1)
    }
    printf "INSERT INTO data (%s) VALUES(%s);\n", fields, values
}
$
$ awk -f tst.awk file
INSERT INTO data (Field1,Field2,Field3,Field4) VALUES(Data1,Data2,Data3,Data4);

Upvotes: 2

user000001
user000001

Reputation: 33307

You could try this awk command:

$ cat file
"Field1":"Data1","Field2":"Data2","Field3":"Data3","Field4":"Data4"
$ awk -F'[:"]+' '{s=(NR>1?",":""); fields=fields s $2;data=data s $3}END{printf "INSTERT INTO data(%s) VALUES(%s)\n", fields,data}' RS="," file
INSTERT INTO data(Field1,Field2,Field3,Field4) VALUES(Data1,Data2,Data3,Data4)

Or a bit more readable

#!/usr/bin/awk -f
BEGIN {
    FS ="[:\"]+";
    RS=",";
}
{
    s=(NR>1?",":"")
    fields=fields s $2
    data=data s $3
}
END{
    printf "INSTERT INTO data(%s) VALUES(%s)\n", fields,data
}

Save it in a file named script.awk, and run it like:

./script.awk file

Upvotes: 1

Related Questions