AWK csv file to sql file output

Question

Can someone help me out here as I can't get my awk command to output some simple 3 column CSv to SQl

CSV

01Aug2011,user,12
02Aug2011,userb,34

AWK

I know this one works for me

awk -F',' '{ print "INSERT INTO Testing (`Date`,`User`,`Usage`) VALUES (" "'"$1"'""," """'"$2"'""," """'"$3"'"")" ""}' new.csv > output.log

but not this one

awk -F',' '{ print "INSERT INTO Testing (`Date`,`User`,`Usage`) VALUES (" "`"$1"`""," """`"$2"`""," """`"$3"`"")" ""}' new.csv > output.log

Are you using MySQL? LOAD DATA LOCAL INFILE will be easier. — Kevin
– Kevin, Commented Dec 12, 2011 at 16:27
A closer look shows that the first is using apostrophes/ticks, whilst the second is using backticks in the VALUES clause (only came clear after sticking it into a code block) — Callie J
– Callie J, Commented Dec 12, 2011 at 16:41

Kent · Accepted Answer · 2011-12-12 16:42:09Z

5

I am not very sure what is your problem.

but if the problem is the single quotes, you could try this

awk -F',' '{ printf "INSERT INTO Testing (\x27\Date\x27,\x27User\x27,\x27Usage\x27) VALUES (\x27%s\x27,\x27%s\x27,\x27%s\x27)",$1,$2,$3;print ""}' yourFile

with your example input lines, the output is:

INSERT INTO Testing ('Date','User','Usage') VALUES ('01Aug2011','user','12')
INSERT INTO Testing ('Date','User','Usage') VALUES ('02Aug2011','userb','34')

hope this is what you want.

answered Dec 12, 2011 at 16:42

Kent

197k36 gold badges248 silver badges317 bronze badges

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

Comments

Adrian Cornish · Accepted Answer · 2011-12-12 16:36:54Z

3

First it looks like you are missing the semicolon off the end of the insert statement.

Try pasting the SQL into a mysql client session - this will show you if you generated the SQL incorrectly. I am suspecting that mysql is rejecting the date (if it is a date column).

I would use the printf - it makes the code simpler to read - you have so many quotes there its hard to read - use as little quotes as possible - only add them if then need to be there.

awk -F',' '{ printf("INSERT INTO Testing (Date,User,Usage) 
VALUES('%s', '%s', %d);\n", $1, $2, $3); }' file.csv

answered Dec 12, 2011 at 16:36

Adrian Cornish

24k13 gold badges64 silver badges81 bronze badges

Collectives™ on Stack Overflow

AWK csv file to sql file output

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related