3

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

Deano
  • 11,582
  • 18
  • 69
  • 119
  • 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 May 14 '13 at 04: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 May 14 '13 at 04:20
  • I have added an answer with examples...hope it helps. – Bill May 14 '13 at 04:32

5 Answers5

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
Bill
  • 5,263
  • 6
  • 35
  • 50
  • 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](http://stackoverflow.com/questions/9587743/bind-values-insert-into-mysql-perl). – TrueY May 14 '13 at 07:31
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

p.s.w.g
  • 146,324
  • 30
  • 291
  • 331
user3071463
  • 11
  • 1
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
potong
  • 55,640
  • 6
  • 51
  • 83
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.

masterxilo
  • 2,503
  • 1
  • 30
  • 35
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.

Olli
  • 1,708
  • 10
  • 21