Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access 7.0.033
  HXTT Cobol 4.0.033
  HXTT DBF 7.0.033
  HXTT Excel 6.0.034
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Paradox 7.0.033
  HXTT PDF 1.1.033
  HXTT Text(CSV) 7.0.033
  HXTT Word 1.0.031
  HXTT XML 3.0.033
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
Currency Fields have Inconsistent format
Jim Hobday
2011-08-16 00:38:12.0
Dear HXTT,

I am testing reading a spreadsheet column that contains Currency which does not get read the same way for each line even though they are all formatted the same in the XLS document.

For example:
10873.23
"£"11,465.84
6462.65
"£"2,667.12

I changed the parameter ?maxScanRows=0 so that it reads all the data because I thought that the Datatype of VARCHAR(12) was perhaps not long enough, but it doesn't seem to be that.

I shall email the spreadsheet with the same subject title. The column I am talking about is "LazyTest.xls"."Product Analysis".ValueSold but it happens with other currency columns as well.

Cheers
Jim
Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-16 02:33:13.0
Checked then supported now.
Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-16 06:24:58.0
Please download the latest package.
Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-16 07:16:21.0
The latest package is not doing what I hoped. The Currency results are of type JAVA_OBJECT so there is no information about which Currency they are. Even ResultSetMetaData.isCurrency() answers false.

What you had before was better: VARCHAR, with either a prefix or a suffix like [$EUR]. I was hoping that the currency would be included on *every* result. My original problem was that it was missing from some of the results.

For the Percentage, Scientific2dp and Scientific4dp columns, this is also a JAVA_OBJECT rather than a BigDecimal.

So there is no information like:
ResultSetMetaData.getPrecision() or
ResultSetMetaData.getScale()

Am I missing something?
Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-16 07:47:42.0
SELECT ValueSold from "LazyTest1.xls"."Product Analysis";
ValueSold DECIMAL 11 11 2
10873.23
11465.84
6462.65
2667.12
10088.63
2483.97
3321.13
3880.86
12373.18
2512.64
1788.37
2003.13
406.47
6220.53
10948.06
1143.46
3474.73
2370.99
6410.85
3717.21
265.12
357.69
3704.38
2113.19
4400.68
2549.39
5805.14
840.95
null
The total row number of resultset: 29
Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-16 07:51:01.0
SELECT * from "LazyTest3.xls"."Formatting";
Percentage JAVA_OBJECT 0 0 0
Scientific2dp JAVA_OBJECT 0 0 0
Scientific4dp JAVA_OBJECT 0 0 0
Currency GBP JAVA_OBJECT 0 0 0
Currency Mixed JAVA_OBJECT 0 0 0
Yeah. I will check it.
Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-16 07:57:09.0
Sorry - I was not clear originally. I was hoping to get the extra information all of the time rather than loose it.

I have an idea.

The columns are definitely numbers, so their SQL Types can be what you have done with ValueSold - DECIMAL - so a call to getObject() returns a java.math.BigDecimal. - as you have now. I agree that ValueSold is working fine, but the other number columns in Formatting are not.

What I am doing is asking you for more than the JDBC standard gives. To know about the Excel formatting as well. so ...

Would it be possible to overide the getString() method for formatting to include the formatting - e.g. [$GBP] 12.34 or 12.34% or 12.34 [$EUR] or "£"12.34?

Thanks
Jim
Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-16 08:05:19.0
SELECT * from "LazyTest3.xls"."Formatting";
Percentage DECIMAL 5 5 2
Scientific2dp DECIMAL 5 5 2
Scientific4dp DECIMAL 7 7 4
Currency GBP DECIMAL 11 11 2
Currency Mixed JAVA_OBJECT 0 0 0
Time TIME 12 0 0
Time2 TIME 12 0 0
Date DATE 10 0 0
Date2 DATE 10 0 0
Timestamp TIMESTAMP 20 0 0
Fraction DOUBLE 20 16 16
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-16 08:08:02.0
Can I just ask why the Currency Mixed is still a JAVA_OBJECT? Shouldn't it be a DECIMAL as well?
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-16 08:16:31.0
>Would it be possible to overide the getString() method for formatting to include
> the formatting - e.g. [$GBP] 12.34 or 12.34% or 12.34 [$EUR] or "£"12.34?
Because java.sql.ResultSet is used to store rows with formated values, maybe a conection property, for instance, formatValue=true to return values to include the formatting - e.g. [$GBP] 12.34 or 12.34% or 12.34 [$EUR] or "£"12.34?
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-16 08:25:11.0
That would mean having two connections at the same time - one to get the values and one to get the formatting.

But I am asking you to do more than the JDBC specification, so if you can do that, it would be a great help. Can you solve the original problem where the formatting was not always included?

I was just thinking that GetBigDecimal() and getString() could return different things, one a value and one formatted.

Any help would be good - and there is no rush because I finish for the day in the next few minutes.

Thanks for all your help so far. I really appreciate it.

Jim
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-16 09:48:30.0
>Can I just ask why the Currency Mixed is still a JAVA_OBJECT?
>Shouldn't it be a DECIMAL as well?
That's resulted by some user-defination formating rule doesn't be supported by Java.

Now it will return:
SELECT * from "LazyTest3.xls"."Formatting";
Percentage NUMERIC 5 5 2
Scientific2dp NUMERIC 5 5 2
Scientific4dp NUMERIC 7 7 4
Currency GBP NUMERIC 11 11 2
Currency Mixed NUMERIC 16 16 2
Time TIME 12 0 0
Time2 TIME 12 0 0
Date DATE 10 0 0
Date2 DATE 10 0 0
Timestamp TIMESTAMP 20 0 0
Fraction DOUBLE 20 16 16
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-16 09:53:03.0
>I was just thinking that GetBigDecimal() and getString()
> could return different things, one a value and one formatted.

>Any help would be good - and there is no rush because I finish for the day in the >next few minutes.
We can. Then it will be available after about 2 days. The supports for "Currency Mixed" won't be available today.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-16 23:53:05.0
That would be great - thanks again. And 2 or 3 days is fine.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-30 00:55:18.0
Any news on when the next version will be available?
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-30 07:55:32.0
Failed to support MS Excel format pattern for user define format.
Now we can get
£1.23
0.223 (error)
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-30 07:58:17.0

Failed to support MS Excel format pattern for user define format.
Now we can get


£10,873.23
£11,465.84
£6,462.65
£2,667.12
£10,088.63
£2,483.97
£3,321.13
£3,880.86
£12,373.18
£2,512.64
£1,788.37
£2,003.13
£406.47
£6,220.53
£10,948.06
£1,143.46
£3,474.73
£2,370.99
£6,410.85
£3,717.21
£265.12
£357.69
£3,704.38
£2,113.19
£4,400.68
£2,549.39
£5,805.14
£840.95

£1.23
0.223 (error)
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-30 07:59:19.0
?-2 0.01 (error)
EUR 0.88
CNY 1.00
GBP 0.12
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-30 09:46:01.0
You can use withFormat=true connection property to try it.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-31 00:17:32.0
Version 2011-08-30: Now I cannot read anything, in any workbook.

Executing SQL: SELECT count(*), count(Percentage), min(Percentage), max(Percentage) from "LazyTest2.xls".Formatting

java.sql.SQLException: Maybe you are trying to access a closed table.
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.dl.a(Unknown Source)
at com.hxtt.sql.cg.a(Unknown Source)
at com.hxtt.sql.cg.a(Unknown Source)
at com.hxtt.sql.ae.w(Unknown Source)
at com.hxtt.sql.excel.cg.cs(Unknown Source)
at com.hxtt.sql.du.a(Unknown Source)
at com.hxtt.sql.c9.f(Unknown Source)
at com.hxtt.sql.du.a(Unknown Source)
at com.hxtt.sql.bu.a(Unknown Source)
at com.hxtt.sql.bu.a(Unknown Source)
at com.hxtt.sql.bu.a(Unknown Source)
at com.hxtt.sql.al.a(Unknown Source)
at com.hxtt.sql.al.a(Unknown Source)
at com.hxtt.sql.al.executeQuery(Unknown Source)
at com.sentences.rdb.RdbMappingChapter.sampleRdbData(RdbMappingChapter.java:4394)

The xls file is not read-only. Only the Excel_JDBC40.jar file has been updated.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-31 02:17:05.0
Failed to recur your issue. Please email us your sample.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-31 02:23:57.0
But I click Excel_JDBC40.jar, and can run nomal with your xls sample.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-31 02:43:45.0
I am not clicking your jar file, I am writing java code to use it as a JDBC driver.

My XLS file is in the directory E:\MS-Excel\

connection string:
jdbc:excel:/E:/MS-Excel?maxScanRows=0&withFormat=true

Connection connection = DriverManager.getConnection(connectionDetails.getString(), connectionDetails.getUser(), connectionDetails.getPassword());

Statement stmtCount = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

String sqlCount = "SELECT count(*), count(Percentage), min(Percentage), max(Percentage) from \"LazyTest2.xls\".Formatting"

ResultSet rsCount = stmtCount.executeQuery(sqlCount);

SQLException:

java.sql.SQLException: Maybe you are trying to access a closed table.
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.dl.a(Unknown Source)
at com.hxtt.sql.cg.a(Unknown Source)
at com.hxtt.sql.cg.a(Unknown Source)
at com.hxtt.sql.ae.w(Unknown Source)
at com.hxtt.sql.excel.cg.cs(Unknown Source)
at com.hxtt.sql.du.a(Unknown Source)
at com.hxtt.sql.c9.f(Unknown Source)
at com.hxtt.sql.du.a(Unknown Source)
at com.hxtt.sql.bu.a(Unknown Source)
at com.hxtt.sql.bu.a(Unknown Source)
at com.hxtt.sql.bu.a(Unknown Source)
at com.hxtt.sql.al.a(Unknown Source)
at com.hxtt.sql.al.a(Unknown Source)
at com.hxtt.sql.al.executeQuery(Unknown Source)

If you use java code do you see the problem?
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-31 02:47:26.0
If I use the jar file from 2011-08-16 with the same java code I do not get this SQLException.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-31 03:56:59.0
Recured. It happened with withFormat=true for your sample. The fixed version will be available in about 2 hours.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-31 04:33:39.0
Thanks - I guess not many people are still using XLS then? All moved to XLSX?
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
HXTT Support
2011-08-31 04:41:05.0
Supported. Please download it.

>I guess not many people are still using XLS then? All moved to XLSX?
No. Many is using XLS. HXTT Excel supports both of them.
Re:Re:Re:Re:Re:Re:Re:Re:Currency Fields have Inconsistent format
Jim Hobday
2011-08-31 06:21:02.0
I can confirm that the withFormat=true works as I expected.

Thank you very much. This is excellent.

Cheers
Jim

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 | Sitemap