Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

convert file into sql insert stmt

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting convert file into sql insert stmt
# 1  
Old 02-18-2011
convert file into sql insert stmt
My file is now cleaned, sanitized & prepped:

Code:
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:
Code:
01/24/2011 12:58:24  Installation  76699 complete

to
Code:
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;
Code:
's/^.*/INSERT INTO example (Date, Time, Type, Number, Status, Notes) VALUES (/' infile > outfile

Last edited by dba_frog; 02-18-2011 at 03:25 PM..
# 2  
Old 02-18-2011
Code:
$ 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  
Old 02-18-2011
Ah, so I can ref the items as columns with this:
Code:
$1,$2,$3,$4,$5)

# 4  
Old 02-18-2011
yessss Smilie
This User Gave Thanks to itkamaraj For This Post:
dba_frog
# 5  
Old 02-18-2011
Something like this:
Code:
awk -v q="\"" '{
  print "INSERT.....(" $1 "," $2 "," q  $3 q "," $4 "," q $5 q "," q q ");"
}' file

# 6  
Old 02-18-2011
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  
Old 02-18-2011
If you really really really wanted to get it done with sed, the following sed script should do it:
Code:
# 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:
Code:
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
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert sql file to csv file

How to convert name.sql file into name.csv file. Basically my input source file is .sql file, I need to verify data after the import of that exported sql file. Thanks for any help. (3 Replies)
Discussion started by: wamqemail2
3 Replies

2. Shell Programming and Scripting

Create SQL DML insert statements from file using AWK or similar

Hi all. This is my first post on this forum. I've previously found great help in the huge knowledgebase that is here, but this time I have not been able to find a solution to my problem. I have a large text file that looks like this: typedef struct ABC_struct_nbr1_ { char attr1; /*... (0 Replies)
Discussion started by: Yagi Uda
0 Replies

3. Programming

[SQL] Insert content file to mysql

dear all, i want to insert string in file to mysql i just want how to do that cause i am poor in sql languages ... so this file like this DATA.txt doni|student|westjava|123412|lombok| iwan|student|westjava|1234412|utankayu| rio|student|westjava|12342|cempedak| so i want insert DATA.txt to... (2 Replies)
Discussion started by: zvtral
2 Replies

4. Shell Programming and Scripting

Convert sql output to csv file via bash tools

hi Can anybody help me with converting such structure into csv file for windows : BAT_ID ID_num CVS_LINE A_SEG SKILL_TO A_CUSTOMER_TYPE --------- ---------- --------------------------------- ---------- ------------------ ----------- 14-MAY-11 777752 ... (4 Replies)
Discussion started by: kvok
4 Replies

5. Shell Programming and Scripting

Help with Dates and SQl stmt?

hi All Please explain the below statement in RED?What does this mean? perl /HDS/common/operations/Quality_Team/Nirvana/WEEKLY_OOPS/sql_dump.pl --sql "select * from WEEKLY_REPORT order by test_case, type" --username=ddb_qa --password=ddb_qa123 --sid=pldeldb --output... (1 Reply)
Discussion started by: SVS_2017
1 Replies

6. Shell Programming and Scripting

I want to get the Unix variable value in the sql stmt

Hi All I have a requirement where in I am stuck. There is a shell script that is being developed by me. It consist of the sql stmt also. I need to export a variable called HOMEPAGE with a value say www.abc.com. and then use this $HOMEPAGE variable in the sql stmt. My ultimate aim is to fetch all... (1 Reply)
Discussion started by: amitsinha
1 Replies

7. Emergency UNIX and Linux Support

Insert data into sql queries from a file

Hello friends, I need to insert data from a file to another. I need this to form an sql query file which will consist of 50.000 INSERT INTO sentences. my sql query file will consist of 50.000 times the below line consecutively: insert into subscriber... (6 Replies)
Discussion started by: EAGL€
6 Replies

8. Shell Programming and Scripting

Cron to Auto Insert SQL to DB

Hi People.. This is driving me mad. I have approx 100 files received daily, normally in 5 min intervals in the format of.. ******-logfile.sql These are auto uploaded to a folder on server called filelist The format of each file is as follows. DELETE FROM `webuserdata` WHERE... (0 Replies)
Discussion started by: OkiesPlace
0 Replies

9. Shell Programming and Scripting

sql insert command

Hi, sqlplus -s / <<EOF > /dev/null insert into table1 (a1, a2, a3) values ('a',1,'b'); commit; EOF in the above code can i pass the values to the insert command from shell script like this: insert into table1 (a1, a2, a3) values ('$a',$b,'$c'); If yes, how is it passed?? Any... (2 Replies)
Discussion started by: abey
2 Replies
Login or Register to Ask a Question