• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Generating SQL Insert scripts from Excel sheet

 
Parameswaran Thangavel
Ranch Hand
Posts: 485
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33713
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 54
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic