Last post Nov 20, 2008 12:18 PM by ricolifts
Nov 18, 2008 01:30 PM|ricolifts|LINK
Ok interestingly enough I have some code that pulls data from an excel file and displays it to screen.
Taking a scenario where i have 3 rows and 1 column. If in two of the 3 rows i have Integers and in the 3rd row i have a string the dataset column gets typed as a integer and doesn' t bring in the string value. It works vice versa if in two of the rows
i have a string and the third row i ahve a integer the integer doesn't get brought in.
Has anyone experienced this and how do i get passed it?
Protected Function ExcelConnection() As OleDbCommand
' Connect to the Excel Spreadsheet
Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Session("LocalFile") & ";" & _
"Extended Properties=Excel 8.0;"
' create your excel connection object using the connection string
objXConn = New OleDbConnection(xConnStr)
' 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 "Sheet1" and is coded as: [Sheet1$]
Dim objCommand As New OleDbCommand("SELECT * FROM [Sheet1$]", objXConn)
Private Sub ViewSpreadsheet()
' Create a new Adapter
Dim objDataAdapter As New OleDbDataAdapter()
' retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = ExcelConnection()
' Create a DataSet
Dim objDataSet As New DataSet()
' Populate the DataSet with the spreadsheet worksheet data
Dim indColumnCount As Integer
For indColumnCount = 0 To objDataSet.Tables(0).Columns.Count - 1
Dim z As New BoundField
z.DataField = objDataSet.Tables(0).Columns(indColumnCount).ColumnName()
z.HeaderText = objDataSet.Tables(0).Columns(indColumnCount).ColumnName()
DataSet DataAdapter excel
Nov 20, 2008 02:44 AM|ronnyrunatserver|LINK
try using this
"Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Session("LocalFile") + ";Extended Properties='Excel 8.0;IMEX=1'";
IMEX=1 is used for mixed data types..
hope it helps
Nov 20, 2008 02:49 AM|ronnyrunatserver|LINK
have a look to this article ..it will give you a deep knowledge regarding the same....
Nov 20, 2008 12:18 PM|ricolifts|LINK
Thank you very much this worked great...
I will read th article now as it might clear some things up for me.