Sunday, June 1, 2008

Generating DB Scripts with data from Excel


Prologue:


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.



Arena:


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 TestJXL.java 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(finalScript);
fileWriter.write('\n'); // To write the next script on a new line
}
// A must
fileWriter.flush();
fileWriter.close();
}
// poor exception handling though its forgiven here as it's an example
catch (Exception e) {
e.printStackTrace();
}
}
}



Epilogue:


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.