ODBC preserve leading zeros (Qt4.7.4)

Lachrymology

Hi all,

currently I'm trying to read data from an excel sheet via QODBC and Qt4.7.4

It works in the first attempts very well. Along the way I figured out that Excel or the driver itself has a problem with fields containing leading zeros. The returned variant is of the type double and the value is zero.

Here my current code:

QSqlDatabase vDb = QSqlDatabase::addDatabase( "QODBC", "database_excel"  );
vDb.setDatabaseName( "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=D:\\data.XLS;Extended Properties=\"HDR=No;IMEX=1\"" );
vDb.open();
QSqlQuery query( vDb );
query.setForwardOnly( true );
query.exec( "SELECT `MyRow` FROM `MySheet$`" );
while( query.next() )
{
    qDebug() << query.value( 0 ).toString();
    //01234 would be within QVariant of type double and of value 0, therefore toString() is "0"
}

The row MyRow is within Excel formatted as text, I tried standard too without any effect.

To get rid of the data type double within QVariant and to have a native QString I changed the query for example to:

query.exec( "SELECT CStr( [MyRow] ) FROM `MySheet$`" );
//or 
query.exec( "SELECT left( [MyRow], 20 ) FROM `MySheet$`" );

Nevertheless, the result of 01234 is in this case empty.

(I need to mention that the excel table contains over 100 sheets with over 10k rows in each. So a manual export or a manual editing is nearly impossible.)

Does anyone of you have a clue how to get this done or how to work-a-round this?

Really any little piece of information or idea will be appreciated as much as possible.

Kind regards,
Mike

hskoglund

Hi, from what I remember, the problem is supposed to be solved by setting IMEX=1 in the connection string (which you have done).

So, try checking in your registry HKLM\Software\Microsoft\Microsoft\Jet\4.0\Engines\Excel for the ImportMixedTypes string key, and make sure it's set to "Text".

There are some information on this when googling, for example here

Lachrymology

Hi hskoglund,

thanks for your fast and detailed reply. I've Excel 2010 on Windows 10 x64 installed.

Therefore my RegEdit is a bit different. Nevertheless I searched for ImportMixedTypes and found it within:
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel
and
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Sadly to say, that both entries are still set to "Text".
-.-

I tried Qt5.10.1 too; still the same results.

Kind regards,
Mike

hskoglund

Hmm, have you tried changing the TypeGuessRows setting to 0?

Lachrymology

Hi hskoglund,

TypeGuessRows did the trick BUT only for the numbers with leading zeros. Now numbers like 2345 (in the same column) are empty.

I'm starting to get headache about this. ^^

In the meantime I found this thread.
They said to insert somewhere in the affected column a dummy row containing characters > 255.
This trick did the same as setting TypeGuessRows to 0; 2345 is for example empty.

It's odd isn't it?

Kind regards,
Mike

hskoglund

Agreed re. headache, you need coffee for this.

Just tried a small test .xls file, using Qt 5.10.1, Excel 2010 and default registry settings (i.e. TypeGuessRows = 8)

If I just enters numbers with or without leading zeros I get the same result as you. But, if I open the spreadsheet in Excel and specifically reformats the column in question to Text (by rightclicking on the column header), save and try again in Qt, it works and I get the leading zeros (as well as the normal chaps).

Lachrymology

Hi hskoglund,

thank you very much for trying it on your own.

I tried again to reformat the entire column and it changed nothing.

After a tiny bit of anger I created a new Excel file with one sheet and only one column containing data in form of numbers with and without leading numbers. In this case ODBC was successful ""to guess"" that it's only text and returned both types of numbers.

So the conclusion is? Mmhhh .. something in my big Excel file is disturbing ODBC to parse the data only as string/text.
Therefore:
I removed the scroll locking of the first row, I removed every format (bold, underline, etc.), I sorted the column descending, I removed empty rows, I removed scripts, I removed buttons and I move the column to the first position.

It won't let me read both number types. (Currently only those with leading zeros.) This is getting ridiculous.

Kind regards,
Mike

hskoglund

Yeah as usual small test files work fine but the real ones do not.

One last tip: to wash/rinse/lather your big Excel file: you could try exporting it to a more ancient and dumber format, like .sylk or .csv. Then create a new Excel file and import the data back into it.

Edit: one more suggestion: try changing TypeGuessRows to something humongous (ie. bigger than 8) and not 0.

Lachrymology

Hi hskoglund,

to change the TypeGuessRows bigger than 8 had no effect.

To copy one of the sheets info a new workbook had no effect.

To copy one sheet to a new workbook, closing it, converting it to .csv, closing it again and finally saving it as .xls WORKED.
-.-'''

So I need to do the monkey work to get it to work. Thanks for your help hskoglund.

hskoglund

Nice!

P.S. I guess a moral of this story is:
.csv = simple file format --> simple errors
.xls = complicated file format --> complicated errors

Lachrymology

Yeah - can't say anything against this moral guessing.

Btw. I did some monkey work for 10 sheets now and the final xls with the cleaned sheets got parsed without any of the previous behaviors. ^^