transform csv file to mysql insert statement

Question

I'm trying to generate MySQL file from CSV data

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

is there is a way that I could use awk or sed to generate mysql insert statement as follows

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

Thank you

using awk with field separator as | you can get all the fields separately. Then you need to relate 1st field to id, 2nd field to value, and so on.... — Bill
– Bill, Commented May 14, 2013 at 4:17
I tried to cat the file and pipe it to awk to add the "insert syntax, then the values between (), but I stumbled upon getting the syntax right — Deano
– Deano, Commented May 14, 2013 at 4:20

Bill · Accepted Answer · 2013-05-14 04:44:23Z

3

To extract a particular field from your input....

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

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

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

So to 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, then use the following script (or adapt this to your needs)

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

edited May 14, 2013 at 4:44

answered May 14, 2013 at 4:21

Bill

5,9427 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TrueY Over a year ago

I would suggest to use bound values instead of explicitly define the values. It makes slower if a lot of such lines are inserted. See topic about bind values in this related SO topic.

p.s.w.g · Accepted Answer · 2013-12-05 18:14:58Z

1

Just to refine the above written AWK statement, instead of while loop we can use

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)}' a.txt >> result.txt

I hope this works

edited Dec 5, 2013 at 18:14

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

answered Dec 5, 2013 at 17:57

user3071463

111 bronze badge

Comments

potong · Accepted Answer · 2013-05-14 10:32:14Z

0

This might work for you (GNU sed):

sql="insert into table (id,value,status,starttime,endtime,number,phone,number) values"
sed 'y/|/,/;s/.*/'"$sql"' (&);/' file

answered May 14, 2013 at 10:32

potong

59.3k6 gold badges55 silver badges92 bronze badges

Comments

masterxilo · Accepted Answer · 2019-02-07 09:16:02Z

0

You could use csvkit, see How to let csvkit/csvsql generate insert statements for csv file?

The statements will be for sqlite, the database engine behind csvkit, but conversion should be easy.

answered Feb 7, 2019 at 9:16

masterxilo

2,8263 gold badges36 silver badges37 bronze badges

Comments

Olli · Accepted Answer · 2019-02-07 09:25:46Z

0

Although the question already is pretty old, someone might fall over it...

so another way of doing this is using standard ways for reading CSV files with LOAD DATA syntax which allows to use CSV files to read data and inserting it into a table:

LOAD DATA INFILE '/tmp/test.csv' INTO TABLE test FIELDS TERMINATED BY '|';

which takes the data from the CSV file and splits the valies on the | symbol.

answered Feb 7, 2019 at 9:25

Olli

1,76810 silver badges22 bronze badges

1 Comment

avatarofhope2 Over a year ago

FWIW, once loaded, you can then use mysqldump to create a .sql file. Not quite the same as generating inserts though.

Collectives™ on Stack Overflow

transform csv file to mysql insert statement

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related