wood burning stoves 2.0*
The moose likes JDBC and the fly likes Generating SQL Insert scripts from Excel sheet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Generating SQL Insert scripts from Excel sheet" Watch "Generating SQL Insert scripts from Excel sheet" New topic
Author

Generating SQL Insert scripts from Excel sheet

Parameswaran Thangavel
Ranch Hand

Joined: Mar 01, 2005
Posts: 485
hi all
I am having the set of excel sheets which has the data to be populated to the table.

I need to create insert scripts to populate table rather than the importing the excel sheet as it is.

Any idea how can i do this? it can be either through any tool or Java class or even through Macro will do for me.

Thanks
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

Parameswaran,
If you just want to import the columns in a different order, you can often configure your database's import facility to do so.

For something more complicated, you can write a script in any language that accesses the database. I'll assume you know Java since you are asking here. In Java, you would loop through the Excel spreadsheet and use a PreparedStatement to update the database. You'd probably want to use a batch update so it goes faster.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Kiran Joshi
Ranch Hand

Joined: Sep 04, 2005
Posts: 54
Parameswaran ,

The best way is to use excel itself.
You can use expressions in excel cell.

Example assume following data

codeDescription
1015

say this is in cell A1,A2,B1,B2

You can write following expression in the cell C2

="insert into table mymaster values (" &A2&","&B2&");"

this will create the insert statement for those two values.

You can simply select C2 cell at right bottom & drag it down. That will create the insert statements for all inputs present in the column A & B.
 
jQuery in Action, 2nd edition
 
subject: Generating SQL Insert scripts from Excel sheet