Tuesday, December 1, 2015

SQLite and Excel/VBA

In the comment section of  http://yetanothermathprogrammingconsultant.blogspot.com/2013/10/gdx2sqlite.html it is mentioned that Excel and VBA (via ODBC) has troubles with fields that are declared as TEXT. Although I have added an option to GDX2SQLITE to generate VARCHAR(255) columns I am wondering what the problem can be. Internally SQLite will use a storage type TEXT for all character types (https://www.sqlite.org/datatype3.html).

To generate a test case I just ran some GAMS models and generated a GDX file out of this: 

trnsport1

This converts easily and quickly into a SQLite database:

trnsport2

I had no troubles importing this into Excel:

image

Anyway, I have added an option –varchar to generate VARCHAR(255) types for the string columns in the CREATE TABLE statements.

Note: see also the discussion in http://sqlite.1065341.n5.nabble.com/TEXT-columns-with-Excel-VBA-td85907.html. I have come to the conclusion that the reliance on VARCHAR is probably in some user code.