Friday, August 12, 2011

convert csv to sql

Generally you can use either awk or sed to generate sql files from a csv input. However on my current project we are using oracle as our db and needed the ability to write a commit instruction every few hundred lines.

After a bit of reading about, awk actually keeps track of lines read and was well suited to generate such a sql.

Here is my shell script. Usage : ./csv.sh inputfile.csv

#!/bin/bash
csvName=$1

awk -F',' '
BEGIN {
lines=0;
}
{
lines ++;
print "insert into db.table values ("$1","$2", "$3");"
if ( lines%100==0 ){
print "commit;"
}
}
END {
print "commit;";
}
' < $csvName > insert.sql