Sunday, June 1, 2008

Generating DB Scripts with data from Excel


My web application was running successful with thousands of data running behind. Suddenly, a mail came from onsite saying all data has been wiped off and i was asked to insert data from the attached excel sheet. Murmured slightly for some additional effort adding to the already threatening deadline, i opened the excel. Fallen to death after rolling the scroll button, there are some 45000... rows. Then i thought of some cool code that can wisely convert these data to a beautiful massive DB scripts. It would definitely going to take time for learning this stuff, but i wanted to go wih it. Actually, this approach starts off late but reaches the goal well in time realistically.


This is my excel sheet. Only got few data after all it's an example. I wanted is to write DB Scripts for DB2 and its of the form

INSERT INTO <table name> (<column names>) VALUES (<actual data>)

Now comes the part of some Java API that i could use to ease my job in accessing some vendor specific document. i chose rather simple one, the jexcel api amidst some poor performance and bug claims with it.
i created a Java project in my eclipse and added the downloaded jxl.jar through Right Click - Project -> Properties -> Build Path -> Libraries -> Add External Jars.
Then created a Java Class, named which is given below.

public class TestJXL {
public static void main(String [] args) {
try {
Workbook workbook = Workbook.getWorkbook(new File("C:/SampleExcel.xls")); // take care of a potential IOException here
Sheet sheet = wb.getSheet(0); // 0 indicates first sheet. You can also get it by calling their names using an overloaded method of getSheet(String);

int rows = sheet.getRows();
int columns = sheet.getColumns();

//initializing my script as string
String schemaName = "A1G1.";
String tableName = "Employee";
String columnNames = "(EMP_ID,EMP_NAME,EMP_AGE)";
String script = "INSERT INTO " + schemaName + tableName + columnNames + VALUES;
// holder for data in cells
String [] holder = new String[3];

FileWriter fileWriter = new FileWriter("C:\\Output.txt"); // To use \
u need to escape it with another \.
otherwise stick with unix style, i.e /, just one.

String finalScript = null;
//Here we go...
for (int row = 1; row < rows; row ++) { // why row = 1 and not 0, well,
to just get away with the heading if you have any

for (int col = 0; col < columns; col ++) {
holder[col] = sheet.getCell(col,row).getString();
finalScript = script + "('" + holder[0] + "','" + holder[1] + "'," + holder[2] + ");";
fileWriter.write('\n'); // To write the next script on a new line
// A must
// poor exception handling though its forgiven here as it's an example
catch (Exception e) {


It took around one hour to finally come up with this working code but then, it produced lines of DB scripts in no time and finally i was well on my schedule to meet the deadline.