Linux – Convert csv files to mysql insert statements

Convert csv files to mysql insert statements… here is a solution to the problem.

Convert csv files to mysql insert statements

I’m trying to generate a MySQL file from CSV data

90927744|1356976814398|0|1356976815339|18563182571|18563182571|18568753009|18563574221

Is there a way to use awk or sed to generate a mysql insert statement like the following

insert into table (id,value,status,starttime,endtime,number,phone,number) values ( 90927744,1356976814398,0,1356976815339,18563182571,18563182571,18568753009,18563574221);

Thanks

Solution

Extract specific fields from your input….

$echo "90927744|1356976814398|0|1356976815339|18563182571|18563182571|18568753009|18563574221" |  awk -F "|" '{print $4}'
1356976815339

If you have only one line in A.txt (see the script below for multiple lines in the input file).

$cat a.txt | awk -F "|" '{print $4}'

So output the query you want —

$cat a.txt | awk -F "|" '{printf("insert into table (id,value,status,starttime,endtime,number,phone,number) values (%d, %d, %d, %d, %d, %d, %d, %d) \n", $1, $2, $3, $4, $5, $6, $7, $8)}'

If your file has multiple lines, use the following script (or adjust it for your needs).

while read line           
do           
     echo "$line" | awk -F "|" ........
done < a.txt

Related Problems and Solutions