I am trying to get the contents of one row, columns 2 to 241 out of an EXCEL workbook and into a data table and I get an error saying "Too many fields defined". All the cells contain integers. The columns 1 to 141 are given the type 'number with no decimal
places'.
I've noticed a lot of comment in various posts about problems which seem very similar, but there seems no clear resolution.
I would be quite happy even if I could put the contents of a single EXCEL cell into a variable. I could loop and get the rest.
<code>
Protected Function SetsConnection(ByVal n_cust As Integer) As OleDbCommand
'
' Create the connection string for the EXCEL file containing the filename and Provider settings.
'
Dim xx = 0
'
Dim filepath As String = "d:\websites\XYZ.com\BoxStore\Box_num.xls"
'Dim conxString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";Extended Properties=""Excel 8.0;HDR=Yes;"""
Dim conxString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";Extended Properties=""Excel 8.0;"""
Dim objXConn As New OleDbConnection(conxString)
objXConn.Open()
' use a SQL Select command to retrieve the data from the Excel Spreadsheet
' the "table name" is the name of the worksheet within the spreadsheet
' in this case, the worksheet name is "Members" and is coded as: [Members$]
Dim objCommand As New OleDbCommand("SELECT (cells(n_cust,2), cells(n_cust,241)) FROM [SETS$]", objXConn)
'Dim objCommand As New OleDbCommand("SELECT * FROM [SETS$] WHERE aa = n_cust", objXConn)
'Dim objCommand As New OleDbCommand("SELECT * FROM [SETS$]", objXConn)
Return objCommand
999: xx = 0
'
End Function
The code gives an error at the line in the code below which I've marked with asterixes **** below, but the origin of the error is in the SELECT statement in my previous post (above). As you can see, I have been fiddling about trying to get something to work.
Initially I had
<code>SELECT * from [SETS$] where aa=n_cust </code>
and this gave an error "Too many field defined". So I tried to make a more definite selection. I'm not very familiar with SELECT in this context. I am free to format, alter, doctor the EXcel worksheet, so long as the actual data itself is not changed. With
the current statement
<code>SELECT (cells(n_cust,2), cells(n_cust,241)) FROM [SETS$]</code>
the error says "Syntax Error (comma) in query expression"
If you could suggest something I'd be grateful.
<code>
Public Function GetFromSets(ByVal n As Integer, ByVal kol As Integer) As Integer
'
' This will look in worksheet("SETS") and get the number (n_piece) sitting
' in n_piece=SETS.Cells(n,kol).
'
Dim jyx As Integer = 20, xx As Integer
' Create a new Adapter
Dim objDataAdapter As New OleDbDataAdapter()
' retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = SetsConnection(n)
' Create a DataTable
Dim objSetsTable As New DataTable()
' Populate the DataTable with the Excel worksheet data
' The table is the same shape as the Excel worksheet BUT
' in the datatable the lefthand most column is col 0 (NOT col 1)
' and the topmost row is row 0
objDataAdapter.Fill(objSetsTable) ' **************
'GoTo 999
' Now need to get the bit of the data which we want
Dim MyRow As DataRow = objSetsTable.Rows.Item(0)
Dim MyCol As DataColumn = objSetsTable.Columns.Item(3) ' Item kol ???
jyx = MyRow(MyCol)
'
999: xx = 0
Return (jyx)
'
End Function
dcwmorley
Member
510 Points
290 Posts
Importing Excel data. Too many fields ??
Jan 11, 2011 01:03 PM|LINK
I am trying to get the contents of one row, columns 2 to 241 out of an EXCEL workbook and into a data table and I get an error saying "Too many fields defined". All the cells contain integers. The columns 1 to 141 are given the type 'number with no decimal places'.
I've noticed a lot of comment in various posts about problems which seem very similar, but there seems no clear resolution.
I would be quite happy even if I could put the contents of a single EXCEL cell into a variable. I could loop and get the rest.
<code>
Protected Function SetsConnection(ByVal n_cust As Integer) As OleDbCommand
'
' Create the connection string for the EXCEL file containing the filename and Provider settings.
'
Dim xx = 0
'
Dim filepath As String = "d:\websites\XYZ.com\BoxStore\Box_num.xls"
'Dim conxString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";Extended Properties=""Excel 8.0;HDR=Yes;"""
Dim conxString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";Extended Properties=""Excel 8.0;"""
Dim objXConn As New OleDbConnection(conxString)
objXConn.Open()
' use a SQL Select command to retrieve the data from the Excel Spreadsheet
' the "table name" is the name of the worksheet within the spreadsheet
' in this case, the worksheet name is "Members" and is coded as: [Members$]
Dim objCommand As New OleDbCommand("SELECT (cells(n_cust,2), cells(n_cust,241)) FROM [SETS$]", objXConn)
'Dim objCommand As New OleDbCommand("SELECT * FROM [SETS$] WHERE aa = n_cust", objXConn)
'Dim objCommand As New OleDbCommand("SELECT * FROM [SETS$]", objXConn)
Return objCommand
999: xx = 0
'
End Function
</code>
excel ADODB asp.net Excel excel oledb
Lateef045
Star
7813 Points
1541 Posts
Re: Importing Excel data. Too many fields ??
Jan 11, 2011 02:16 PM|LINK
The method you have specified just shows the command preparation code.
What error are you getting exactly?
The select statement is not correctly written in the example you have shown above.
Make sure the datatable you are filling with the excel have these many number of rows.
dcwmorley
Member
510 Points
290 Posts
Re: Importing Excel data. Too many fields ??
Jan 11, 2011 06:24 PM|LINK
The code gives an error at the line in the code below which I've marked with asterixes **** below, but the origin of the error is in the SELECT statement in my previous post (above). As you can see, I have been fiddling about trying to get something to work. Initially I had
<code>SELECT * from [SETS$] where aa=n_cust </code>
and this gave an error "Too many field defined". So I tried to make a more definite selection. I'm not very familiar with SELECT in this context. I am free to format, alter, doctor the EXcel worksheet, so long as the actual data itself is not changed. With the current statement
<code>SELECT (cells(n_cust,2), cells(n_cust,241)) FROM [SETS$]</code>
the error says "Syntax Error (comma) in query expression"
If you could suggest something I'd be grateful.
<code>
Public Function GetFromSets(ByVal n As Integer, ByVal kol As Integer) As Integer
'
' This will look in worksheet("SETS") and get the number (n_piece) sitting
' in n_piece=SETS.Cells(n,kol).
'
Dim jyx As Integer = 20, xx As Integer
' Create a new Adapter
Dim objDataAdapter As New OleDbDataAdapter()
' retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = SetsConnection(n)
' Create a DataTable
Dim objSetsTable As New DataTable()
' Populate the DataTable with the Excel worksheet data
' The table is the same shape as the Excel worksheet BUT
' in the datatable the lefthand most column is col 0 (NOT col 1)
' and the topmost row is row 0
objDataAdapter.Fill(objSetsTable) ' **************
'GoTo 999
' Now need to get the bit of the data which we want
Dim MyRow As DataRow = objSetsTable.Rows.Item(0)
Dim MyCol As DataColumn = objSetsTable.Columns.Item(3) ' Item kol ???
jyx = MyRow(MyCol)
'
999: xx = 0
Return (jyx)
'
End Function
</code>
excel excel oledb excel ADODB asp.net
Lateef045
Star
7813 Points
1541 Posts
Re: Importing Excel data. Too many fields ??
Jan 11, 2011 06:48 PM|LINK
What version of Excel is installed on your machine.
I see from the connection string that you are trying to use Old Excel version.
Here at my end I have Excel 2007 Installed.
And even though I have defined the excel sheet to more than 350 columns, but while fetching data through asp.net, I am only able to get 256 columns.
Maybe since you are using old version of Excel it is not able to get all columns.
Which column No are you interested in?
dcwmorley
Member
510 Points
290 Posts
Re: Importing Excel data. Too many fields ??
Jan 14, 2011 04:40 PM|LINK
Sorry for the delay in replying, slight distraction but I'm back on the job now.
I have been using Excel 2003, but I will now try the later version 2007.
With the 2003, I found I could get the contents of part of the row, two columns ab and jg, by writing
<code>
"SELECT ab,jg FROM [SETS$] WHERE aa=3"
</code>
but I couldn't see how to pick 241 columns like this. If I write
<code>
"SELECT * FROM [SETS$] WHERE aa=3"
</code>
Then I get an error "Too many fields defined"