Exporting to Excel using Microsoft.ACE.OLEDB.12.0 - VarChar limited to 255 characters - need longer alternative for up to 8000 chars

Question

I am trying to create an Excel spreadsheet using the Microsoft.ACE.OLEDB.12.0 provider in vb.net, and I cannot seem to define a field that allows more than 255 characters.

Here is my current code to create the table:

 create table [table_name] ([column_1] VarChar)

I understand that VarChar is limited to 255 characters, but I am unable to figure out an alternative that will hold more.

I've tried this:

 create table [table_name] ([column_1] LongVarChar)

and got a "Syntax error in field definition" exception.

Similarly, I've tried LongVarWChar, Memo, NText(8000),... with the same results. Any suggestions?

EDIT: Here is my connection string:

 "Provider=Microsoft.ACE.OLEDB.12.0;" &
 "Data Source=" & destinationFile & ";" &
 "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

EDIT 2: Here is the basic idea of what I'm trying to do

Imports System.Data.OleDb Imports System.Text

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        dim destinationFile = "c:\users\dell\desktop\test\test_" & Now.ToString("yyyyMMdd_HHmmssfffff") & ".xlsx"
        Dim oleDbConnString =
                "Provider=Microsoft.ACE.OLEDB.12.0;" &
                "Data Source=" & destinationFile & ";" &
                "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        Dim oleDbConn as New OleDbConnection(oleDbConnString)
        Dim oleDbComm as New OleDbCommand("create table Sheet1 (column_1 LongText);", oleDbConn)

        oleDbConn.Open
        oleDbComm.ExecuteNonQuery
        oleDbConn.Close

        Dim bigText as New StringBuilder()
        For i = 0 to 255
            bigText.Append(".")
        Next
        oleDbComm.CommandText = "insert into Sheet1(column_1) values ('" & bigText.ToString & "');"

        oleDbConn.Open
        oleDbComm.ExecuteNonQuery
        oleDbConn.Close

        Process.Start(destinationFile)
    End Sub
End Class

Exception gets thrown on the last ExecuteNonQuery call in the Button1_Click sub. In place of "LongText", I've tried many different data types, including a bunch that are not valid for an Excel data source using the Ace engine. I've found nothing that allows me to insert a string larger than 255 characters.

score 1 · Answer 1 · edited May 23 '17 at 12:00

1

You are getting a syntax error because you are using data types that don't exist (at least where you are trying to use them they don't, if at all). You need to specify your data type for column_1 as LONGTEXT

EDIT: The solution I gave is for the first error your reported, which comes up because you had an unknown data type in your create statement. As you have experienced, using LONGTEXT instead got rid of that error. However, this link:

Excel unable to insert more than 255 chars?

Seems to suggest that it's a limitation in Excel, that's a fairly old question, but I had the same problem when I just tried it in Office 2013. It could be the jet-engine itself. Another question here, link:

c# Error inserting to Excel when string data is more than 255 chars

Opts to use the Excel object directly... To me that seems the more viable solution. Why use OleDb when you can talk directly to Excel?

edited May 23 '17 at 12:00

Community

1
1

answered Sep 27 '16 at 23:20

Jeroen

460
6
14

That's kind of what I assumed, that the data types are not valid for the Ace driver. I tried LongText, and that throws the same exception. Additional information: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. I feel as though I'm missing something quite simple here, just can't figure it out. – user3613310 Sep 28 '16 at 00:15
Well as it turns out, closing the connection after creating the table, and then reopening it to insert data, causes the Ace driver to think the column is text since there are no datarows present when it re-opens the connection. All I had to do was keep the connection open between creating and inserting and it worked like a charm. Thanks for the guidance Jeroen, I always appreciate being able to talk something through with someone. – user3613310 Sep 28 '16 at 01:34
@user3613310 add that as an answer to this question and set it to the accepted answer. It will help someone else out. Glad you got it sorted! – Jeroen Sep 28 '16 at 01:36
I should also add that the reason I'm not using an excel object and talking directly to excel is that that method is markedly slower on larger data sets. I've done some testing with both ways and it is quite substantial a difference. – user3613310 Sep 28 '16 at 02:16
I believe it :-) Personally I'd like to stay away from either method. – Jeroen Sep 28 '16 at 06:00

user3613310 · Answer 2 · 2016-09-28T02:22:34.250

Here's the code that worked for me. The key is to create the table with field of type LongText, and then insert into the table without closing the connection. If you close the connection and reopen it, the Ace driver scans the first few rows to determine datatype. Since there would be no data present if you close the connection after creating the table, it decides that Text is the proper type, rather than LongText as intended.

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim destinationFile = "c:\users\dell\desktop\test\test_" & Now.ToString("yyyyMMdd_HHmmssfffff") & ".xlsx"
        Dim oleDbConnString =
                "Provider=Microsoft.ACE.OLEDB.12.0;" &
                "Data Source=" & destinationFile & ";" &
                "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        Dim oleDbConn as New OleDbConnection(oleDbConnString)
        Dim oleDbComm as New OleDbCommand("create table Sheet1 (column_1 LongText);", oleDbConn)

        Dim bigText as New StringBuilder()
        For i = 0 to 255
            bigText.Append(".")
        Next

        oleDbConn.Open
        oleDbComm.ExecuteNonQuery
        'after creating the table with column of datatype LongText,
        'you mustn't close the connection prior to inserting text longer
        'than 255 characters - keep it open!!
        oleDbComm.CommandText = "insert into Sheet1(column_1) values ('" & bigText.ToString & "');"
        oleDbComm.ExecuteNonQuery
        oleDbConn.Close

        Process.Start(destinationFile)
    End Sub
End Class

Exporting to Excel using Microsoft.ACE.OLEDB.12.0 - VarChar limited to 255 characters - need longer alternative for up to 8000 chars

2 Answers2