File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Generating SQL Insert scripts from Excel sheet" Watch "Generating SQL Insert scripts from Excel sheet" New topic

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.

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33111

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, 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


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.
I agree. Here's the link:
subject: Generating SQL Insert scripts from Excel sheet
It's not a secret anymore!