DataTable ColumnName Limitations?

Question

I'm trying to read data from an Excel File into a DataSet, but my problem is the DataTable Column Names aren't matching what's in my Excel. It seems to be only Columns with long Column Names. In the DataTable Column Names it cuts off at some point.

Here's my attempt:

DataSet mainExcelDataToImport = new DataSet();
using (OleDbConnection mainExcelOleDbConnection = new OleDbConnection())
{
    string theMainExcelConnectionString = this.ExcelConnectionString.Replace("{FullFilePath}", this.SelectedFilePath);
    mainExcelOleDbConnection.ConnectionString = theMainExcelConnectionString;

    // Open
    mainExcelOleDbConnection.Open();

    string mainExcelSQL = "SELECT * FROM [{ExcelSheet}$]";
    mainExcelSQL = mainExcelSQL.Replace("{ExcelSheet}", selectedExcelSheet);

    using (OleDbCommand mainExcelOleDbCommand = mainExcelOleDbConnection.CreateCommand())
    {
        mainExcelOleDbCommand.CommandText = mainExcelSQL;
        mainExcelOleDbCommand.CommandType = CommandType.Text;

        // Prepare
        mainExcelOleDbCommand.Prepare();

        using (OleDbDataAdapter mainOleDbDataAdapter = new OleDbDataAdapter(mainExcelOleDbCommand))
        {
            mainOleDbDataAdapter.Fill(mainExcelDataToImport);

            // Close
            mainExcelOleDbConnection.Close();
        }
    }
}

Here's a .xlsx I've tried:

A1234567890123456789012345678901234567890123456789012345678901234567890

TEST

'A12345.....' is the Column Name at A1

'TEST' is the Value at A2

When I check the `ColumnName` in the `DataTable` I get: 'A123456789012345678901234567890123456789012345678901234567890123' which is 64 characters.

How does the ColumnName MaxLength limitation work?

Can I get rid of it?

Is it maybe a limitation on the OleDbDataAdapter?

Any help / suggestions would be appreciated.

score 3 · Accepted Answer · answered May 05 '19 at 13:02

64 characters is unusually long for the name of a database column. I've never seen a column name come anywhere near that length.

It seems odd that you would have a column name so long.

They have to be stored somewhere of course and so there is likely to be a maximum on any database software eg SQL server has a maximum of 128. It would not surprise me if intermediate software like sqlclient had a lower limit. Very long names are so unusual.

It's probably a limitation of the adapter.

You could try some of the alternatives mentioned in this thread:

Best /Fastest way to read an Excel Sheet into a DataTable?

Or maybe you can just work with xml - and not a datatable. Xml will be able to handle any size field that excel can.

https://learn.microsoft.com/en-us/office/open-xml/understanding-the-open-xml-file-formats https://learn.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet I

DataTable ColumnName Limitations?

1 Answers1