The Issue:
I am trying to import an SQLite database into Power Pivot ('PP') via the SQLite3 ODBC and MS OLE DB for ODBC. I have solved the first hurdle and managed to import a small SQLite DB into PP.
However, when I tried to import a bigger test SQLite DB with 2001 columns/fields, PP reported: No tables were found in the data source. Check the connection information and login credentials.
I have traced it to the cut-off between 2000 fields/columns (which imported OK) and 2001 fields (which gave the above error). I suspect the reason is due to SQLite's default Max_Column. Quoting the official SQLite website:
The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767.
Question:
How do I set the maximum column per table parameter for SQLite3 ODBC and/or MS OLE DB for ODBC? I am using the 64-bit version of SQLite3 ODBC 0.9991 (latest version).
What I've tried but didn't work:
Reading Microsoft's article and this, I tried adding Extended Properties:"KAGPROP_MAXCOLUMNSINTABLE=5000" into the Connection String, but the following error occurred when I tested the connection:
I tried various permutations of the above line, e.g. KAGPROP_MAXCOLUMNSINTABLE=5000, Extended Properties:KAGPROP_MAXCOLUMNSINTABLE=5000, etc. but none worked.
I went through the SQLiteODBC Documentation - unfortunately, I still could not find the answer.
Would truly appreciate if the experts at SO can assist. Thanks in advance!
Aucun commentaire:
Enregistrer un commentaire