CODEWITHSUNDEEP
sqllinuxshellawk
Grimlockz
Grimlockz

Reputation: 2581

AWK csv file to sql file output

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

Upvotes: 4

Views: 6748

Answers (2)

Kent
Kent

Reputation: 195059

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.

Upvotes: 5

Adrian Cornish
Adrian Cornish

Reputation: 23868

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

Upvotes: 2

Related Questions