Generating SQL Insert scripts from Excel sheet

[Parameswaran Thangavel]

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]

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.

[Kiran Joshi]

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.