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
  HXTT Json v1.0
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  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 Text(CSV)
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

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