Float and Date in ODBC schema files |
Robert McGlade |
2007-10-04 05:51:09 |
Hello,
I am trying you csv jdbc driver and setup the connection like so:
jdbc:text://///server/classic/DMS Files/undated extracts?odbcSchemaFile=schema.ini.hxt (using UNC path and shared folder)
the schema file contains this :
[bildtl.csv]
ColNameHeader=False
Format=Delimited(|)
MaxScanRows=25
CharacterSet=OEM
Col1=F1 Char Width 255
Col2=F2 Integer
Col3=F3 Char Width 255
Col4=F4 Integer
Col5=F5 Char Width 255
Col6=F6 Date
Col7=F7 Char Width 255
Col8=F8 Float
Col9=F9 Integer
Col10=F10 Float
Col11=F11 Char Width 255
Col12=F12 Float
Col13=F13 Char Width 255
Col14=F14 Char Width 255
Col15=F15 Integer
Col16=F16 Date
It does not like FLOAT so I have changed to be DOUBLE (this is ok)
It is OK with Date but you column always comes back empty when it should come back with 12/12/2007 or 12/12/2007 00:00:00.0 or 2007-12-12 or even 2007-12-12 00:00:00.0
the csv file contains 12/12/2007
|
Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-05 03:36:33 |
Who produce that shcmai.ini for bildtl.csv? What's your ODBC driver?
It will be translated into
create table "bildtl.csv" (F1 char( 255),F2 INTEGER,F3 char( 255),F4 INTEGER,F5 char( 255),F6 TIMESTAMP('yyyy-MM-dd hh:mm:ss'),F7 char( 255),F8 FLOAT,F9 INTEGER,F10 FLOAT,F11 char( 255),F12 FLOAT,F13 char( 255),F14 char( 255),F15 INTEGER,F16 TIMESTAMP('yyyy-MM-dd hh:mm:ss'),_CSV_Separator char(1) default '|',_CSV_Header boolean default false).
|
Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-05 03:38:16 |
We will support your schema.ini soon.
|
Re:Re:Re:Float and Date in ODBC schema files |
Robert McGlade |
2007-10-05 04:14:26 |
The schema was generated by Microsoft ODBC txt/csv driver version 4.00.6307.00
many thanks
Rob
|
Re:Re:Re:Float and Date in ODBC schema files |
Robert McGlade |
2007-10-05 04:17:21 |
the only real issue i have with the driver is that is does not return a pure Date field if it is not correctly formatted e.g. 12/12/2007 should return as 12-12-2007 00:00:00.0 or 2007-12-12 00:00:00.0 instead it returns the date empty.
thanks
rob
|
Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-05 04:52:53 |
>It is OK with Date but you column always comes back empty
>the only real issue i have with the driver is that is does not return a pure Date field if it is not correctly formatted
v3.1.079 can parse all kinds of date, time, timestamps format, without prefined date format, or default date format. Your schema.ini will be parsed as:
create table "bildtl.csv" (F1 char( 255),F2 INTEGER,F3 char( 255),F4 INTEGER,F5 char( 255),F6 DATE,F7 char( 255),F8 DOUBLE,F9 INTEGER,F10 DOUBLE,F11 char( 255),F12 DOUBLE,F13 char( 255),F14 char( 255),F15 INTEGER,F16 DATE,_CSV_Separator char(1) default '|',_CSV_Header boolean default false)
The latest package will be available in 24 hours. Thanks for your requirement.
|
Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
Robert McGlade |
2007-10-05 05:05:28 |
great stuff, I'll run with it for a week in load testing then you shall have an order for a 100 redist license - great support.
|
Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-05 07:35:50 |
Now you can download the latest package.
|
Re:Float and Date in ODBC schema files |
Robert McGlade |
2007-10-08 02:29:17 |
The jar file seems to be corrupt, could you check and upload again please
Regards
Rob
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-08 08:10:44 |
Have uploaded.
Please re-download it.
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
Robert McGlade |
2007-10-17 03:01:27 |
Sorry but you stilll have problems reading from schema.ini
Here is an example of schema.ini and the actual data
sample of csv file
60718400009|t,j|||130073|B0008|NO COVER AT TIME OF TREATMENT-|10/09/2007|10/09/2007|83.43|C||U|10/09/2007
60718400009|t,t|||PATIENT|B0008|NO COVER AT TIME OF TREATMENT-|10/09/2007|10/09/2007|83.43|D||U|10/09/2007
60718500009|y,y|||130073|B0008|NO COVER AT TIME OF TREATMENT-|10/09/2007|10/09/2007|47.19|C||U|10/09/2007
60718500009|u,i|||PATIENT|B0008|NO COVER AT TIME OF TREATMENT-|10/09/2007|10/09/2007|47.19|D||U|10/09/2007
60716200024|r,r|||130073|B0004|EXCESS ON THIS POLICY-|10/09/2007|10/09/2007|40|C||U|10/09/2007
60716200024|e,e|||PATIENT|B0004|EXCESS ON THIS POLICY-|10/09/2007|10/09/2007|40|D||U|10/09/2007
60723400007|e,e;s|||145005|B0003|INSURANCE TO PATIENT TRANSFER-|10/09/2007|10/09/2007|18.69|C||U|10/09/2007
60723400007|a,a;a|||PATIENT|B0003|INSURANCE TO PATIENT TRANSFER-|10/09/2007|10/09/2007|18.69|D||U|10/09/2007
60724100002|s,s;s|||145005|B0003|INSURANCE TO PATIENT TRANSFER-|10/09/2007|10/09/2007|9.34|C||U|10/09/2007
60724100002|w,w;w|||PATIENT|B0003|INSURANCE TO PATIENT TRANSFER-|10/09/2007|10/09/2007|9.34|D||U|10/09/2007
L0724300025|q,q;q|||160001|B0006|ITEMS NOT COVERED BY POLICY-|10/09/2007|10/09/2007|256.27|C||U|10/09/2007
L0724300025|e,e;e|||PATIENT|B0006|ITEMS NOT COVERED BY POLICY-|10/09/2007|10/09/2007|256.27|D||U|10/09/2007
L0724000040|q,q|||160001|B0004|EXCESS ON THIS POLICY-|10/09/2007|10/09/2007|46.61|C||U|10/09/2007
L0724000040|q,q|||PATIENT|B0004|EXCESS ON THIS POLICY-|10/09/2007|10/09/2007|46.61|D||U|10/09/2007
L0724300026|w,w|||160001|B0004|EXCESS ON THIS POLICY-|10/09/2007|10/09/2007|35.14|C||U|10/09/2007
L0724300026|e,e|||PATIENT|B0004|EXCESS ON THIS POLICY-|10/09/2007|10/09/2007|35.14|D||U|10/09/2007
end of file
sample of schema.ini for the csv file
[baltrn.csv]
ColNameHeader=False
Format=Delimited(|)
MaxScanRows=25
CharacterSet=OEM
Col1=F1 Char Width 255
Col2=F2 Char Width 255
Col3=F3 Integer
Col4=F4 Integer
Col5=F5 Integer
Col6=F6 Char Width 255
Col7=F7 Char Width 255
Col8=F8 Date
Col9=F9 Date
Col10=F10 Float
Col11=F11 Char Width 255
Col12=F12 Char Width 255
Col13=F13 Char Width 255
Col14=F14 Date
actual results returned was none, tried on a different file and dates still don't work as expected.
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-17 03:36:11 |
It said:
java.sql.SQLException: Failed to get a double value from java.lang.String: PATIENT
After changed
Col5=F5 Integer
into
Col5=F5 Char Width 255
It can return values:)
You can know your issue if you used the following code:
catch( SQLException sqle )
{
do
{
System.out.println(sqle.getMessage());
System.out.println("Error Code:"+sqle.getErrorCode());
System.out.println("SQL State:"+sqle.getSQLState());
//sqle.printStackTrace();
}while((sqle=sqle.getNextException())!=null);
}
If you use ignoreDirtyData connection property, you can still use Col5=F5 Integer .
>dates still don't work as expected.
Please download the latest package.
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-17 03:39:06 |
BTW, odbcSchemaFile is only using for old ODBC compatibility. If you hasn't odbc schema file information, you need to prepare it in advance, and let HXTT Text (CSV) to do auto data detection.
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
Robert McGlade |
2007-10-17 07:38:47 |
I tried downloading the file again but the date is the same as the last one! and the above example shows that dates come back empty
thanks the schema file was wrong for col 5
regards
rob
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
Robert McGlade |
2007-10-17 07:49:40 |
also if i try auto detection as you suggested I only get two columns, it doesn't detect the | as a seperator
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-17 08:13:25 |
>the above example shows that dates come back empty
In my test, it will show 2007-10-09 .
>it doesn't detect the | as a seperator
You shoud use _CSV_Separator=| connection property. For instance, "jdbc:csv:/f:/textfiles?_CSV_Separator=|;_CSV_Header=true;
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
HXTT Support |
2007-10-17 08:27:42 |
Checked. You should be using http://www.hxtt.com/text.zip, please login through http://www.hxtt.com/download.jsp?product=text, then you can download the latest package, for instance Text_JDBC30.jar.
|
Re:Re:Re:Re:Re:Re:Re:Re:Float and Date in ODBC schema files |
Robert McGlade |
2007-10-17 09:40:20 |
Great stuff, that fixed it - will be testing mode now
many thanks for your quick responses and help
regards
robert
|