1

I'm using the Microsoft ODBC Desktop Database Drivers to create an excel (xlsb) file from my application. I made various attempts to create a text column with more than 255 characters. But either the text is truncated to 255 characters or I'm getting an error. Do you know how to use the driver to make a text column with more characters?

My attempts so far:

  1. Calling SQLExecDirect with CREATE TABLE [Test] ([TestCol] TEXT (1000)) results in the error: SQLSTATE = 37000; Size of field 'TestCol' is too long
  2. CREATE TABLE without (1000) and calling SQLBindParameter with a NULL terminated string with more than 255 characters and a ColumnSize argument with the value 0 results in an excel sheet where the column is truncated.
  3. Like 2. but with a ColumnSize with the length of the string results in the error: SQLSTATE = S1104; Invalid Precision Value.

The connection is opened with the connection string Driver="{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=C:\Temp\test.xlsb; FIL={excel 12.0 xml};DriverID=1046;ReadOnly=False"

All the links and Workarounds about the truncation to 255 characters I found so far (e.g. Data truncated to 255 characters with Excel ODBC driver) are about reading an excel worksheet. I like to write data.

Christian Ammer
  • 7,464
  • 6
  • 51
  • 108
  • Can you use "managed" C++? – xmojmr Dec 03 '14 at 14:34
  • @xmojmr: It's a old VC6 project which is ported to VC 12.0. I don't know if there is a technical restriction, basically I don't know how to include managed code into this C++ project. But if it makes things easier, I'm open to it. – Christian Ammer Dec 03 '14 at 14:45
  • If your dataset is reasonably small and there will be Excel installed on the machine doing the export I'd go the way of Google:`site:stackoverflow.com c++ excel automation`. If you can use .NET then I'd go the way of Google:`site:stackoverflow.com c++ excel open xml sdk`. Your choice of creating *.xlsb file through ODBC may also have a solution but it is the most abandoned way which I would not explore (avoid the driver hell, the "memo" problem etc.). BTW: Doing Excel export in C# [is easy](http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp) – xmojmr Dec 03 '14 at 15:50

0 Answers0