Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
  HXTT Excel v6.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Json v1.0
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT Excel
Problems reading columns
Raul Avila
2010-08-03 05:21:09
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?
Re:Problems reading columns
HXTT Support
2010-08-03 06:39:03
>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?
Re:Re:Problems reading columns
Raul Avila
2010-08-03 23:38:38
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();
}

}

}



Re:Re:Re:Problems reading columns
HXTT Support
2010-08-04 04:14:16
Checked, and supported. Please download the latest package.

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Refund | Sitemap