Read (using Odbc)and write using (POI api)

[zuzi]

Hi ALL,
I tried to to read and write the data through excel files.But i am facing some issues while reading and writting the data.The exceptions which are coming are mentioned below
__________________________________________________ _________________________________
Exception:

sqlselect 'villno','fno','w_name', 'age','idno','adcode','caste','educ','height' from [studyhb$]
java.sql.SQLException: Column not found
at sun.jdbc.odbc.JdbcOdbcResultSet.findColumn(Unknown Source)roundSQL
select `month1`,`year1`,`round1`, `ttab1`,`teat1`,`m_wt1`,`m_pre1`, `eff11`,`eff12`,`eff13`,`hedu1`,`other1` from [studyhb$] where villno='' and fno='' and w_name='' and idno=''

at sun.jdbc.odbc.JdbcOdbcResultSet.getString(Unknown Source)
at db.ReadOdbc.WriteFirstRowOfInputSheet(ReadOdbc.jav a:283)
at db.ReadOdbc.main(ReadOdbc.java:66)
java.sql.SQLException: Column not found
at sun.jdbc.odbc.JdbcOdbcResultSet.findColumn(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(Unknown Source)
at db.ReadOdbc.GetSQLString(ReadOdbc.java:240)
at db.ReadOdbc.WriteFirstRowOfInputSheet(ReadOdbc.jav a:300)
at db.ReadOdbc.main(ReadOdbc.java:66)
java.sql.SQLException: [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unkno wn Source)
at db.ReadOdbc.WriteFirstRowOfInputSheet(ReadOdbc.jav a:305)
at db.ReadOdbc.main(ReadOdbc.java:66)
java.lang.NullPointerException
__________________________________________________ _______________________________

I want that the data i am reading should be wriiten in excellsheet(output).The write file is now writting only headers.
Please guide me how to do this

[masijade]

java.sql.SQLException: [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression.

That means that one of these

where villno='' and fno='' and w_name='' and idno=''

is probably a number field and not a text field (and they would have to be a text field in order to match a text criteria of ''.

[zuzi]

Hi masijade,

all fields are define as a string but still i m facing same issue

[Tolls]

My solution would be...don't treat Excel as a database.
It isn't.

[masijade]

Hi masijade,

all fields are define as a string but still i m facing same issue

Do they actually contain strings, or do they contain numbers. If they contain numbers then the ODBC driver will treat them number fields even if you used the cell formatting to tell excel to display numbers as strings. And, if a column contains both numbers in some cells and strings in other cells, then the ODBC driver will determine the type based on which is more populous. IOW, as mentioned before, do not treat excel as a DB, unless you are aware of the problems and know that that sheet won't trigger any of them (and there are a lot of possible triggers).