By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,638 Members | 2,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,638 IT Pros & Developers. It's quick & easy.

concatenate a CREATE TABLE statement with my values from the first

P: 9
i am trying to write a program in java where i would concatenate a CREATE TABLE statement with my values from the first row of excel file and hold the fields name to create the SQLStatement.

To be more specific i would like to create a table to store the data in a database where it will have as field the content of the first row of the excel file. I have a couple of excel files and for each of them i would like to create a table. Can anyone help me?

The excel file contain data as the above:

ID NAME SURNAME TELEPHONE
12 XXX YYYY 45782148
... ... ... ....
etc.

I am using APACHE POI to read the data and i store them in a list.

Expand|Select|Wrap|Line Numbers
  1. public class readexcel{
  2. public static void main(String[] args) throws Exception {
  3. String filename = "C:\...\example.xls";
  4. List sheetData = new ArrayList();
  5. FileInputStream fis = null;
  6. try {
  7. fis = new FileInputStream(filename);
  8. HSSFWorkbook workbook = new HSSFWorkbook(fis);
  9. HSSFSheet sheet = workbook.getSheetAt(0);
  10. Iterator rows = sheet.rowIterator();
  11. while (rows.hasNext()) {
  12. HSSFRow row = (HSSFRow) rows.next();
  13. Iterator cells = row.cellIterator();
  14. List data = new ArrayList();
  15. while (cells.hasNext()) {
  16. HSSFCell cell = (HSSFCell) cells.next();
  17. data.add(cell);
  18. }
  19. sheetData.add(data);
  20. }
  21. } catch (IOException e) {
  22. e.printStackTrace();
  23. } finally {
  24. if (fis != null) {
  25. fis.close();
  26. }
  27. }
  28. private static void showExcelData(List sheetData) {
  29. for (int i = 0; i < sheetData.size(); i++) {
  30. List list = (List) sheetData.get(i);
  31. for (int j = 0; j < list.size(); j++) {
  32. Cell cell = (Cell) list.get(j);
  33. if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
  34. System.out.print(cell.getNumericCellValue());
  35. } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  36. System.out.print(cell.getRichStringCellValue());
  37. } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
  38. System.out.print(cell.getBooleanCellValue());
  39. }
  40. if (j < list.size() - 1) {
  41. System.out.print(", ");
  42. }
  43. }
  44. System.out.println("");
  45. }
  46. }
  47. }
and for store the data in database:

Expand|Select|Wrap|Line Numbers
  1. Statement  stmt = con.createStatement();
  2.  
  3.  
  4. try
  5. {
  6. stmt.executeUpdate( "USE databasename;" );
  7. }
  8. catch( SQLException e )
  9. {
  10. System.out.println( "SQLException: " + e.getMessage() );
  11. System.out.println( "SQLState:     " + e.getSQLState() );
  12. System.out.println( "VendorError:  " + e.getErrorCode() );
  13. }
  14.  
  15. try
  16. {
  17. **String table = "CREATE TABLE newtable( "
  18. + "id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,"
  19. +......
  20. + "year INTEGER(4)"
  21. +....
  22. + ");";
  23. System.out.println( "Create a new table in the database..." );**
  24. stmt.executeUpdate( table );
  25. }
  26. catch( SQLException e ) 
  27.  
  28. .....
etc

What i want is the String table = "CREATE TABLE newtable... to give the first column of the excel with the fields that contain and that has read from the previous code.Which fuction i have to use?
Apr 10 '13 #1
Share this Question
Share on Google+
2 Replies


10K+
P: 13,264
Again, Java is an object oriented language. You will find life a lot easier if you think objects every time.
Separate the activities by,

1.) create a Column class that has properties name and type.
2.) create a Table class that has properties
Expand|Select|Wrap|Line Numbers
  1. String name
,
Expand|Select|Wrap|Line Numbers
  1. List<Column> columns
and
Expand|Select|Wrap|Line Numbers
  1. List<Records> tableData
where Record is the class that you created earlier to represent each excel row record.
3.) write a method that takes your excel file and produces a List<Table> records by reading the values from excel using poi.
4.) That a method that accepts a List<Table> parameter and creates the required sql scripts.
Apr 10 '13 #2

P: 9
Could you provide me with code?
Apr 10 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.