Wbimport from Excel

21 views
Skip to first unread message

Maurizio Scarpis

unread,
Aug 30, 2022, 11:02:06 AMAug 30
to SQL Workbench/J - DBMS independent SQL tool
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)
test0.csv
test0.xlsx
test0.sql

Thomas Kellerer

unread,
Sep 1, 2022, 12:37:29 PMSep 1
to sql-wo...@googlegroups.com
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

unread,
Sep 2, 2022, 11:37:16 AMSep 2
to SQL Workbench/J - DBMS independent SQL tool
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

unread,
Sep 6, 2022, 3:34:09 AMSep 6
to sql-wo...@googlegroups.com
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

unread,
Sep 6, 2022, 4:02:06 AMSep 6
to SQL Workbench/J - DBMS independent SQL tool
Hi Thomas.
Thanks for the fix. I will wait for 128.4 release.
Regards
Maurizio 

Thomas Kellerer

unread,
Sep 6, 2022, 4:03:35 AMSep 6
to sql-wo...@googlegroups.com
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

unread,
Sep 14, 2022, 3:43:19 AMSep 14
to SQL Workbench/J - DBMS independent SQL tool
Well done !
Test was OK!
Regards and ... thanks as usual
Maurizio 

Reply all
Reply to author
Forward
0 new messages