When I try to read the metadata from an Excel archive, using the class DatabaseMetadata, sometimes the method retrieves wrong names of the columns, giving the header of the sheet (A, B, C, D...) instead of the names on the first row (Field1, Field2...whatever). I invoke the method like this:
DatabaseMetadata md...
ResultSet rs=md.getColumns(null, null, sheet_name, null);
(...)
String column_name = properties.get("COLUMN_NAME");
It happens that reading the same excel archive, after writing some information in it (without changing the first row, of course), retrieves the right names at first, but it starts to retrieve the wrong names, and I'm not able to get it right.
Maybe it's recommended to use another method of the class DatabaseMetadata, or there is another method to get the column names.
Does anybody know a solution to this?
|
>retrieves the right names at first, but it starts to retrieve the wrong names,
>and I'm not able to get it right.
Whether we can see your test sample and xls file?
|
Here's the xls file: http://dd.eionet.europa.eu/GetXls?obj_type=dst&obj_id=2810
Executing the code enclosed at the end of the message, I get the next result:
START
BiotaConcentrations
FIELD: A
FIELD: B
FIELD: C
FIELD: D
FIELD: E
FIELD: F
FIELD: G
FIELD: H
FIELD: I
FIELD: J
FIELD: K
FIELD: L
FIELD: M
FIELD: N
FIELD: O
FIELD: P
FIELD: Q
DirectDischarges
FIELD: A
FIELD: B
FIELD: C
FIELD: D
FIELD: E
FIELD: F
FIELD: G
FIELD: H
FIELD: I
FIELD: J
FIELD: K
FIELD: L
FIELD: M
FIELD: N
FIELD: O
DO_NOT_DELETE_THIS_SHEET
FIELD: A
FluxStationsTCM
FIELD: A
FIELD: B
FIELD: C
FIELD: D
FIELD: E
FIELD: F
FIELD: G
FIELD: H
(...)
I have realized that if I open the xls file with Microsoft Excel (version 2003), and save the file without changing anything, if I repeat the test, I obtain the right results:
START
BiotaConcentrations
FIELD: CountryCode
FIELD: NationalStationID
FIELD: Year
FIELD: Month
FIELD: Day
FIELD: SampleID
FIELD: DeterminandHazSubsName
FIELD: CASNumber
FIELD: Unit_HazSubs
FIELD: Species
FIELD: Tissue
FIELD: LimitFlag
FIELD: Concentration
FIELD: Basis
FIELD: DryWetRatio
FIELD: Fat
FIELD: Remarks
DirectDischarges
FIELD: CountryCode
FIELD: WaterBodyID
FIELD: WaterBodyName
FIELD: SeaAreaName
FIELD: SeaRegionName
FIELD: SeaConventionArea
FIELD: Year
(...)
Here's the test code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.*;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Vector;
public class Example {
protected static Vector dumpResultSet(ResultSet rs) throws Exception {
Vector result=new Vector();
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next())
{
Hashtable record = new Hashtable(rsmd.getColumnCount());
for (int i=1; i<=rsmd.getColumnCount(); i++)
{
Object value = rs.getObject(i);
if (value==null){
value="";
}
record.put(rsmd.getColumnLabel(i), value.toString());
}
result.add(record);
}
return result;
}
public static void main(String[] args)
{
try {
System.out.println("START");
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
String url = "jdbc:Excel:/c:/files/Marine.xls";
Connection con = DriverManager.getConnection(url, "", "");
String[] table_types = {"TABLE"}; // SYNONYM son tablas vinculadas en Access
ResultSet rs = con.getMetaData().getTables(null, null, "%", table_types);
Vector tables = dumpResultSet(rs);
for(Iterator i=tables.iterator();i.hasNext();)
{
Hashtable row = (Hashtable)i.next();
String table_name = (String)row.get("TABLE_NAME");
System.out.println(table_name);
ResultSet rs_c = con.getMetaData().getColumns(null, null, table_name, null);
Vector fields_all_info = dumpResultSet(rs_c);
for(Iterator i2=fields_all_info.iterator();i2.hasNext();)
{
Hashtable row2 = (Hashtable)i2.next();
String field_name = (String)row2.get("COLUMN_NAME");
System.out.println("FIELD: "+field_name);
}
rs_c.close(); //ravila
}
con.close();
System.out.println("END");
}
catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
|
Checked, and supported. Please download the latest package.
|