convert file into sql insert stmt

Shell Programming and Scripting
1

My file is now cleaned, sanitized & prepped:

07/07/2008 21:18:51  Installation  52016 complete *BUT NOTHING CHANGED*
07/21/2008 15:28:15  Removal  52016 complete
07/21/2008 15:34:15  Removal  55856 complete
12/08/2009 19:30:40  Installation  62323 complete
12/08/2009 19:39:06  Installation  64869 complete
12/09/2009 15:11:24  Preview  63753 complete
12/09/2009 15:16:21  Installation  63753 complete
01/20/2011 14:39:50  Preview  76441 complete
01/21/2011 8:56:40  Preview  76699 complete
01/24/2011 12:55:05  Installation  76441 complete
01/24/2011 12:58:24  Installation  76699 complete

the questions are
a) should I convert the date/time to unix datestamp/timestamp
b) is there an easy way to sed this from:

01/24/2011 12:58:24  Installation  76699 complete

to

INSERT INTO example (Date, Time, Type, Number, Status, Notes) VALUES (01/24/2011,12:58:24, "Installation", 76699, "complete","");

I don't mind multiple passes, but ....I'm struggling with the date time part as the first 2 col.
step 1;

's/^.*/INSERT INTO example (Date, Time, Type, Number, Status, Notes) VALUES (/' infile > outfile
2 
$ awk '{printf ("INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (%s\,%s,\"%s\",%s,\"%s\",\"\");\n",$1,$2,$3,$4,$5)}' query 
awk: warning: escape sequence `\,' treated as plain `,'
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (07/07/2008,21:18:51,"Installation",52016,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (07/21/2008,15:28:15,"Removal",52016,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (07/21/2008,15:34:15,"Removal",55856,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (12/08/2009,19:30:40,"Installation",62323,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (12/08/2009,19:39:06,"Installation",64869,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (12/09/2009,15:11:24,"Preview",63753,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (12/09/2009,15:16:21,"Installation",63753,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (01/20/2011,14:39:50,"Preview",76441,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (01/21/2011,8:56:40,"Preview",76699,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (01/24/2011,12:55:05,"Installation",76441,"complete","");
INSERT INTO example (Date,Time,Type,Number,Status,Notes) VALUES (01/24/2011,12:58:24,"Installation",76699,"complete","");
3

Ah, so I can ref the items as columns with this:

$1,$2,$3,$4,$5)
4

yessss :slight_smile:

5

Something like this:

awk -v q="\"" '{
  print "INSERT.....(" $1 "," $2 "," q  $3 q "," $4 "," q $5 q "," q q ");"
}' file
6

Not sure it is the best way to perform massive data load .
See this post
Maybe you should first parse your file so that it match the standard convention for your standard load tools (may depend on your DB, but it should have one).

(look for SQL loader as well as oracle_loader if you have Oracle 10g and above)

7

If you really really really wanted to get it done with sed, the following sed script should do it:

# 1.  Squeeze all occurrences of consecutive spaces into a single comma.
#     This makes the rest of the job simpler.
# 2.  We're only interested in the first five fields.  If there's a fifth comma,
#     it marks the beginning of data we are not interested in.  Change it to a space.
# 3.  If there's a space, delete it and everything that follows it.
# 4.  Quote the third field.
# 5.  Quote the fifth field.
# 6.  Wrap the resulting values in the sql statement.
s/  */,/g
s/,/ /5
s/ .*//
s/,\([^,]*\)/,"\1"/2
s/,\([^,]*\)/,"\1"/4
s/.*/INSERT INTO example (Date, Time, Type, Number, Status, Notes) VALUES (&,"");/

Or with sh:

format_string='INSERT INTO example (Date, Time, Type, Number, Status, Notes) VALUES (%s, %s, "%s", %s, "%s", "");\n'
while read -r date time type number status notes; do
    printf "$format_string" "$date" "$time" "$type" "$number" "$status"
done

Regards,
Alister