concatenate a CREATE TABLE statement with my values from the first

[ef_mag]

Hi,
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.

public class readexcel{
public static void main(String[] args) throws Exception {
String filename = "C:\...\example.xls";
List sheetData = new ArrayList();
FileInputStream fis = null;
try {
fis = new FileInputStream(filename);
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
Iterator cells = row.cellIterator();
List data = new ArrayList();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
data.add(cell);
}
sheetData.add(data);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (fis != null) {
fis.close();
}
}
private static void showExcelData(List sheetData) {
for (int i = 0; i < sheetData.size(); i++) {
List list = (List) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = (Cell) list.get(j);
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.print(cell.getNumericCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
System.out.print(cell.getRichStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
System.out.print(cell.getBooleanCellValue());
}
if (j < list.size() - 1) {
System.out.print(", ");
}
}
System.out.println("");
}
}
}

and for store the data in database:
Expand|Select|Wrap|Line Numbers
Statement stmt = con.createStatement();

try
{
stmt.executeUpdate( "USE databasename;" );
}
catch( SQLException e )
{
System.out.println( "SQLException: " + e.getMessage() );
System.out.println( "SQLState:     " + e.getSQLState() );
System.out.println( "VendorError:  " + e.getErrorCode() );
}

try
{
**String table = "CREATE TABLE newtable( "
+ "id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,"
+......
+ "year INTEGER(4)"
+....
+ ");";
System.out.println( "Create a new table in the database..." );**
stmt.executeUpdate( table );
}
catch( SQLException e ) 

.....
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?

[riahc3]

Lets see because I might not understand some of what you said :)

You have a Excel file. Lets say with the following cells:

A1: How
A2: Are
A3: You
B1: I
B2: Am
B3: Fine

You want to create something like (example purposes)

CREATE TABLE how (
         are VARCHAR(100),
         you VARCHAR(100)
       );

INSERT INTO how (are,you) VALUES ('I','Fine');

Is this something of what you want?

[ef_mag]

no, let's say i have the excel file you said. I made a program with java to read the data. And then i want to put them in a database. I would like to create a database that would have tables for each one excel file and the content of the table would be the types that i read with the first program that i created. In particular, would take the fields for the table from here:

private static void showExcelData(List sheetData) {
for (int i = 0; i < sheetData.size(); i++) {
List list = (List) sheetData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = (Cell) list.get(j);
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.print(cell.getNumericCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
System.out.print(cell.getRichStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
System.out.print(cell.getBooleanCellValue());
}
if (j < list.size() - 1) {
System.out.print(", ");

Could you help me?

[ef_mag]

How i would give the command in CREATE TABLE to make what i need?

[riahc3]

OK, it made more sense.

Lets say you have example1.xls and example2.xls.

You want to

CREATE TABLE example1;
CREATE TABLE example2;

But now where you have lost me is:

the content of the table would be the types that i read with the first program that i created

Lets say you had

A1: Hi
A2: 2
A3: true  





CREATE TABLE example (
    a1 VARCHAR(100),
    a2 INT(100),
    a3 BOOLEAN
    );

Is this what you want?

[ef_mag]

for CREATE TABLE I want to take the fields from the first row and then from INSERT VALUES the data from the other rows!