Wbimport from Excel

[Maurizio Scarpis]

Hi Thomas.

I have a problem when importing strings that contain "big" numbers from Excel, also if these cells are formatted as "text".

wbimport seems to read data not according to cell format.

If I convert xlsx file to csv it is all OK.

I suppose this is not a problem related to Informix (the DB I use) but to the wbimport function.

You will find in the attachments:

1) the script used (.sql file)

2) the Excel file

3) the CSV file

Thanks in advance

PS: SQL-WB/J version is 127.6, Java version is 14.0.2(64 bit)

[Thomas Kellerer]

Hi,

hmm, this does seem to be related to the database.
With Postgres the value "999000999" is stored exactly like that in the database.

However, when I check the information returned from the Excel library I use (Apache POI), the type of that column is reported as "numeric" and thus the value is read as such.

Maybe I can use the cell's format string to detect situations like that. I will have a look.

Regards
Thomas

[Maurizio Scarpis]

I forgot to write to you that this behaviour seems to happen sometimes and not always; I was not able to understand the cause of the wrong cases

Regards

Maurizio

[Thomas Kellerer]

I have added a fix (workaround) for this in 128.4. If that also fails, you can try to use -stringNumbers=true (similar to -stringDates=true) that ignores the data type reported by the POI library and always reads numbers as strings (pretty much as it's done with CSV files)

Thomas

[Maurizio Scarpis]

Hi Thomas.

Thanks for the fix. I will wait for 128.4 release.

Regards

Maurizio 

[Thomas Kellerer]

I published that yesterday.

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/7bf03944-56cd-4fa0-a4a1-30489ba4ec8cn%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/7bf03944-56cd-4fa0-a4a1-30489ba4ec8cn%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Maurizio Scarpis]

Well done !

Test was OK!

Regards and ... thanks as usual

Maurizio