Qt & Excel xlsx outputs

[Touchoco]

@JonB I tried that a long time ago. It didn't solve the problem : error message :(

[JonB]

@Touchoco
Maybe you still get the error message for another reason, but that does not alter the fact that string you had/have is wrong. So I don't see how you will get anywhere at all with an incorrect literal string. I said that was where to start from.

Where did you get your exact proposed Excel/ODBC connection string from? Yours appears to be for Microsoft Excel 2007 ODBC Driver. I would at least try a more recent one, e.g. from https://www.connectionstrings.com/excel/ ?

[Touchoco]

@JonB The page looked interesting, but I still can't figure it out. I tried with xls or xlsx, Dbq or Excel file, ...

Result :
QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Nom de source de données trop long")
"Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Dbq=D:\sabin\Documents\test.xls;"

Code :
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
//QString connectionString("Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Dbq=D:\sabin\Documents\test.xls;");
QString connectionString("Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Excel File=D:\sabin\Documents\test.xls;");
db.setDatabaseName(connectionString);
if(db.open())
{
//Not happening any time soon...
}
else
{
qDebug()<<db.lastError();
qDebug() << connectionString ;
}

[JonB]

@Touchoco
Whether it is the issue, as I have already said your C++ literal string is wrong, just as before. Please read up on C++. There is no point my keep telling you the same thing and you do not act on it, so I leave you to it.

[J.Hilk]

@Touchoco are you aware of this wiki page?

https://wiki.qt.io/Handling_Microsoft_Excel_file_format

contains all you need to know + examples

[Touchoco]

@JonB Maybe because I don't see what's wrong in it. We all begin one day. I tried a set of thing and keep what works. In this case nothing.

[Touchoco]

@J-Hilk Thank you for your answer. Yes, that's one of the first I got. It is still in my browser favorites.

I had to adapt it to avoid this error message :
QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long and driver name not specified")

Now I got this error message :
QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

One problem solved, one left to find in :

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\sabin\\Documents\\test.xls"));
db.setDatabaseName(connectionString);

Other sources I found :
Helper byte
QT Excel - Open to Read/Write
Ouvrir et communiquer avec Excel dans mon application Qt
Handling Microsoft Excel file format
Using ActiveX Object in Qt

[JonB]

@Touchoco said in Qt & Excel xlsx outputs:

QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\sabin\\Documents\\test.xls"));

OK, at last you have a legitimate C++ literal string; like you had back in your original code in the qDebug() statement :)

I don't know what you're doing wrong, or why that error message, which is obviously the clue. I've looked around but not getting any ideas. I don't think any of your reference links will help.

Which isn't very helpful :( FWIW, I'd chop the filename off completely, i.e. try just

QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)}");

and then open() it. I know it's lacking a filename and will fail, but I'm interested in what error message you get for this one?

Then I'd try the shortest file path:

QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));

It doesn't even matter too much whether you make that file exist (though if you can make it, great [upon reflection, do make it so the file exists, just in case]), at this point I think the error message you're getting about "Datasource name too long" is where the problem is.

BTW, you have installed the ODBC driver, haven't you? You can check which are installed under Windows.

[Touchoco]

@JonB

I tried :

 QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)}");

The error message is interesting, still the same : QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

I also tried with :

QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));

Error message : QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

The ODBC driver is working for my SQL request on SQL Server :

QSqlDatabase myDataBase = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName(QString("DRIVER={SQL Server Native Client 11.0};SERVER=lpc:COMPUTERNAME\\SQLEXPRESS;Database=database_name;Trusted_Connection=Yes;"));
myDataBase.setUserName("username");
myDataBase.setPassword("userpassword");

[J.Hilk]

@Touchoco stupid question, but important

you do not have that file open with excel, while you try to open it via Qt, right ?

[JonB]

@J-Hilk
Remember that he gets "data source too long" even without specifying a file.

@Touchoco
What does, say, QString connectionString("Driver={Microsoft Excel Driver}"); produce?

P.S. This is a 100% longshot, but there's no example in the world which has the spaces you have in Driver = {. Try Driver={... just in case....

[Touchoco]

@J-Hilk I closed all Excel files to be sure. That's not a stupid question ^^

[JonB]

@Touchoco
Did you try my suggestion of Driver={...? I am wondering whether the space you have after the = is causing the parser not to respect your { starter for the { ... } data source name...?

[Touchoco]

@JonB

I got differents messages by deplacing the space caracter.

With :

QString connectionString("Driver={Microsoft Excel Driver}");

or

QString connectionString("Driver={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));

or

QString connectionString("Driver={Microsoft Excel Driver(*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));

or

QString connectionString("Driver={Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));

or

QString connectionString("Driver ={Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));

or

QString connectionString("Driver= {Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));

I got :
QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Data source not found and driver name not specified")

With :

QString connectionString("Driver = {Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));

I got :
QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

[Christian Ehrlicher]

I assume you're using a 64Bit Qt - so do you also have installed a 64Bit Excel ODBC driver? You can check this by opening odbcad32.exe from <WinDir>\System32\ and take a look at your User DSNs if you can configure the Excel driver there.

[JonB]

@Touchoco
Well, I'm not 100%, but I do think we have found some improvement/difference. Only with extra spaces do you get Datasource name too long. Let's not do that, as none of the examples do, and nor does your working Native Client one.

The referenced pages gives:

To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1 and Windows 10 with QT 5.7) :

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection"); db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("c:\\path\\to\\your\\file\\file.xlsx")); if(db.open()) {

So, FWIW, let's stick to exactly that string, no changing in spacing or capitalization.

Meanwhile, I see @Christian-Ehrlicher has queried that you verify your ODBC Excel driver is working. I wonder whether you do not have this set up right, e.g. 32-bit only when you're using 64-bit. Like I said earlier you can check this from Windows, please follow what he has said about how to do that.

[J.Hilk]

@JonB your right

the issue is probably the use of Dbq instead of DBQ

A wokring database name from one of my projects:

db_XL.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + path );

[Touchoco]

@Christian-Ehrlicher thank you for your help.

I got the following screen but I don't understang how I could configure the Excel driver there.

I also tried to compile my program with MinGw 32 bit : the error remain unchanged.

[Touchoco]

@JonB

Thank you for your help.

I worked ! Sticking with exactly the same spaces to the same places. And with 32 bits compiler.
I tried to change to 64 bit compiler : KO. I tried with a very long and weird file name : it works.
Driver and dbq can be write : DRIVER, DBQ, driver, dbq, Driver, Dbq.

Here is the current working code :

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    QString connectionString("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("D:\\sabin\\Documents\\C++ et C\\C++\\BaF\\BaF\\Templates\\TEST.xls"));
    db.setDatabaseName(connectionString);
    if(db.open())
    {
        QSqlQuery query("select * from [" + QString("Infos_adm") + "$A1:E20]");

        while (query.next())
        {
            //reading columns
            QString column1= query.value(0).toString();
            qDebug()<<column1;
        }
        qDebug()<<"done";
        db.close();
        QString dbConnectionName(db.connectionName());
        db.~QSqlDatabase();
        QSqlDatabase::removeDatabase(dbConnectionName);
    }
    else
    {
        qDebug()<<db.lastError();
        qDebug() << connectionString ;
    }

[Christian Ehrlicher]

@Touchoco said in Qt & Excel xlsx outputs:

And with 32 bits compiler.
I tried to change to 64 bit compiler : KO

So my idea was wrong. Install a 64 Bit Excel if you want to use a 64Bit Qt (sad but true).