asked on

Using ADO.Net to retrieve data from Excel 2007 - Cannot import more than 255 columns

Hello there,

I have an application in VB.Net in which I import data from Excel to SQL server database.

I'm trying to retrieve a table in Excel 2007 worksheet, which has more than 255 columns, using Ado.net 2

The problem is the dataset will always import a maximum of 255 columns. I've posted this problem recently and got a reply from one of the experts saying that Ado.net tables can handle even 10,000 columns and that the problem might be in MDAC, being an older version.

I've checked my system and found that I have the latest MDAC version (2.8).

I'm attaching here the code I'm using in the hope that I can get some advice as to how to overcome this limitation in Ado.net

The problem is clearly in the da.Fill(dsXLData, "Ratings") command, as I have checked the number of filled columns in the dataset and it was 255, whereas my worksheet has around 280 (Excel 2007)

Appreciate any help on the above

'The connection string.
            Dim xlCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=" & lblImportFile.Text & _
                                ";Extended Properties=""Excel 12.0;HDR=YES"""
 
 
            Try
 
                    'Excel "SELECT" statement
                    XlsSQL = "Select * from [Sheet1$]
 
                    'Declare and instantiate the connection object.
                    Dim con As New OleDbConnection(xlCon)
 
                    'Declare and instantiate the command object.
                    Dim com As New OleDbCommand()
 
                    'Declare and instantiate a DataAdapter which fill the dataset with data.
                    Dim da As New OleDbDataAdapter
 
                    'Add values to the command object's properties.
                    With com
                        .Connection = con
                        .CommandType = CommandType.Text
                        .CommandText = XlsSQL
                    End With
 
                    With da
                        .AcceptChangesDuringFill = False
                        .SelectCommand = com
                        'Add data to the dataset.
                        .Fill(dsXLData, "Ratings")
                    End With
 
                    MsgBox(dsXLData.Tables("Ratings").Rows.Count & " Rows")
                    MsgBox(dsXLData.Tables("Ratings").Columns.Count & " Clmns")
                    da.Dispose()
                    con.Dispose()

Open in new window

John Gates, CISSP, CDPSE

Silly question but where is your closing quote?

Shouldn't this:

'Excel "SELECT" statement
XlsSQL = "Select * from [Sheet1$]

be :

'Excel "SELECT" statement
XlsSQL = "Select * from " & [Sheet1$]

-D-

Mehawitchi

ASKER

You are absolutely right.

In fact, the actual line reads:

XlsSQL = "Select * from [" & SheetNames(ShtNo, 0).ToString & _
"$" & SheetNames(ShtNo, 1).ToString & "]"

But I just simplified the above line to make it easier for Experts to read my code

SheetNames is an array that holds the names of all sheets in the workbook together with the used range

'Store Xls Sheetnames in array
Dim i As Integer = 0
ReDim SheetNames(xlsWB.Worksheets.Count - 1, 1)

For Each xlsSheet In xlsWB.Worksheets
SheetNames(i, 0) = xlsSheet.Name.ToString
SheetNames(i, 1) = Replace(xlsSheet.UsedRange.Address, "$", "")
i = i + 1
Next xlsSheet

ASKER CERTIFIED SOLUTION

John Gates, CISSP, CDPSE

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Mehawitchi

ASKER

I made some further experiments and it turned out that the problem lies in the "Select * from [Sheet1$]" statement.

Apparently, the select statment will only pick a maximum of 255 columns from the Excel sheet.

I hope someone could be able to help on that front

Thanks