Results 1 to 10 of 10

Thread: Import Excel into a Dataset

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Import Excel into a Dataset

    Anybody happen to have a semi-current link on how to do this? Don't imagine it is too tough but have never tried to before. Thanks.

    Sean
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  2. #2
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    Re: Import Excel into a Dataset

    You can read an excel file using ADO, like any other database and import to a DataSet
    look at www.connectionstrings.com for the correct connection string

    This might help you also:

    http://www.shiningstar.net/aspnet_ar...tProject5.aspx

  3. #3
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Import Excel into a Dataset

    Reading a Excel 2007 sheet into a DataTable, all columns, all rows that have data. HDR=No in Extended Properties means the first row has data no column names. HDR=Yes treats the first row as column names.

    Code:
    Using cn As New System.Data.OleDb.OleDbConnection
        Using cmd As OleDbCommand = New OleDbCommand With _
        { _
                .Connection = cn, _
                .CommandText = "SELECT * FROM [Sheet1$]" _
        }
    
            Dim FileName As String = IO.Path.Combine( _ 
    			Application.StartupPath, "SomeFile.xlsx")
    
            Dim Builder As New OleDbConnectionStringBuilder With _
                { _
                    .DataSource = FileName, _
                    .Provider = "Microsoft.ACE.OLEDB.12.0" _
                }
    
            Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")
    
            cn.ConnectionString = Builder.ConnectionString
            cn.Open()
    
            Dim dt As New DataTable
            dt.Load(cmd.ExecuteReader)
    	End Using
    End Using
    Read Excel pre 2007, first row of the two sheets same as above, no column header, instead data.

    Code:
    Dim dtSheet1 As New DataTable
    Dim dtSheet2 As New DataTable
    Dim Builder As New OleDbConnectionStringBuilder _
        With _
            { _
                .DataSource = IO.Path.Combine(Application.StartupPath, "SomeFile.xls"), _
                .Provider = "Microsoft.Jet.OLEDB.4.0" _
            }
    
    Using cn As New System.Data.OleDb.OleDbConnection
        Builder.Add("Extended Properties", "Excel 8.0; IMEX=1;HDR=No;")
        cn.ConnectionString = Builder.ConnectionString
    
        Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
            cn.Open()
            cmd.CommandText = "SELECT F1, F2, F3 FROM [Sheet1$]"
            dtSheet1.Load(cmd.ExecuteReader)
            cmd.CommandText = "SELECT F1, F2, F3 FROM [Sheet2$]"
            dtSheet2.Load(cmd.ExecuteReader)
        End Using
    End Using
    Let's say there are no headers and you know column 1 contains FirstName and column 2 is LastName you can alias the columns
    Code:
    Dim dtSheet1 As New DataTable
    Dim Builder As New OleDbConnectionStringBuilder _
        With _
            { _
                .DataSource = IO.Path.Combine(Application.StartupPath, "SomeFile.xls"), _
                .Provider = "Microsoft.Jet.OLEDB.4.0" _
            }
    
    Using cn As New System.Data.OleDb.OleDbConnection
        Builder.Add("Extended Properties", "Excel 8.0; IMEX=1;HDR=No;")
        cn.ConnectionString = Builder.ConnectionString
    
        Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
            cn.Open()
            cmd.CommandText = "SELECT F1 As FirstName, F2 As LastName FROM [Sheet1$]"
            dtSheet1.Load(cmd.ExecuteReader)
        End Using
    End Using
    Not sure if the file is XLS or XLSX
    Code:
    Private Sub cmdSelectExcelFile_Click( _ 
    	ByVal sender As System.Object, _ 
    	ByVal e As System.EventArgs) Handles cmdSelectExcelFile.Click
    	
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim FileName As String = OpenFileDialog1.FileName.ToUpper
            Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = FileName}
            TextBox1.Text = OpenFileDialog1.FileName
    
            If IO.Path.GetExtension(FileName) = ".XLSX" Then
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", "Excel 12.0;HDR=No;")
            Else
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", "Excel 8.0;HDR=No;")
            End If
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                ' Beings Excel gives Fn column names when a sheet/range has no column names in
                ' the first row we provide our own names.
                Dim cmd As New OleDbCommand With _
                { _
                    .CommandText = "SELECT F1 As Column1, F2 As Column2, F3 As Column3 FROM [Sheet1$]", _
                    .Connection = cn _
                }
                cn.Open()
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader)
            End Using
        End If
    End Sub

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: Import Excel into a Dataset

    Thanks Kevin.
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: Import Excel into a Dataset

    It doesn't recognize my worksheet for some reason, and thoughts on what might cause that?

    "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator."
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Import Excel into a Dataset

    The only reason for this message is that in the workbook you are working with is that Sheet1 does not exists.

    What are the names of the worksheets in the workbook you are working with? One way to learn the names of sheets in a workbook besides opening the file is using the code below which is hard coded to a workbook which you change to point to your Excel 2007 file. If you are looking at a pre-2007 file than change the connection string using the example I gave before.

    Code:
    Using cn As New System.Data.OleDb.OleDbConnection
        Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
            Dim Builder As New OleDbConnectionStringBuilder With _
                { _
                    .DataSource = IO.Path.Combine(Application.StartupPath, "book1.xlsx"), _
                    .Provider = "Microsoft.ACE.OLEDB.12.0" _
                }
            Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")
    
            cn.ConnectionString = Builder.ConnectionString
            cn.Open()
    
            Dim SheetNames = _
            ( _
                From T In cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                             New Object() {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable _
                Where T.Field(Of String)("Table_Name").Contains("$") _
                Select T.Field(Of String)("Table_name") _
            ).ToList
    
            For Each SheetName In SheetNames
                Console.WriteLine(SheetName)
            Next
        End Using
    End Using

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: Import Excel into a Dataset

    Huh it shows no worksheets at all.

    Edit: Eh never mind I got it, wasn't using the full path. Wonder why it doesn't except though
    Last edited by SeanGrebey; Dec 12th, 2011 at 01:49 PM.
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  8. #8
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Import Excel into a Dataset

    Quote Originally Posted by SeanGrebey View Post
    Huh it shows no worksheets at all.
    Then to OleDb there are no worksheets.

    For a double check if you had OpenXML SDK installed the following gets worksheet names for Office 2007 and up versions of Excel.

    The code below is taken from my code bank post on the basics of OpenXML in regards to working with Excel http://www.vbforums.com/showthread.php?t=664975

    Code:
    Public Function GetSheets(ByVal fileName As String) As DataTable
        Using dtSheetData As New DataTable()
            dtSheetData.Columns.AddRange(New DataColumn() _
                { _
                    New DataColumn("OrdinalPosition", GetType(System.Int32)), _
                    New DataColumn("SortedPosition", GetType(System.Int32)), _
                    New DataColumn("RelationshipId", GetType(System.String)), _
                    New DataColumn("SheetName", GetType(System.String)) _
                } _
            )
    
            Dim CanOpen As Boolean = True
            Try
                Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(fileName, False)
                    Application.DoEvents()
                End Using
            Catch ex As Exception
                CanOpen = False
            End Try
    
            If Not CanOpen Then
                Return dtSheetData
            End If
            Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False)
                Dim WorkBookPart As WorkbookPart = document.WorkbookPart
                Dim Sheets As IEnumerable(Of Sheet) = WorkBookPart.Workbook.Descendants(Of Sheet)()
                For Each Sheet In Sheets
                    dtSheetData.Rows.Add(New Object() _
                        { _
                            CInt(Regex.Replace(Sheet.Id.ToString, "[^0-9]", "")), _
                            CInt(Sheet.SheetId.ToString), _
                            Sheet.Id.ToString, _
                            Sheet.Name _
                        })
                Next
            End Using
    
            Return dtSheetData
    
        End Using
    End Function

  9. #9
    New Member
    Join Date
    Jul 2014
    Posts
    1

    Re: Import Excel into a Dataset

    "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator."
    Quote Originally Posted by kevininstructor View Post
    The only reason for this message is that in the workbook you are working with is that Sheet1 does not exists.
    That happens not to be the case. I am looking right at this workbook and the sheet is right there. Not only that it worked on the developement machine. It did not give this error until it was deployed to the server. The workbook is contained within the web site. First I got the error
    The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
    The I ran the install of the Microsoft Access Database Engine 2010 Redistributable 64 bit installer which it was reported needed to be run on machine before it could see "The 'Microsoft.ACE.OLEDB.12.0' provider" Then after that I got the error message
    The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
    The path is being set using Server.MapPath(filename.xlsx) The file is under the root of the web app's directory structure. As stated before the app saw the file and the sheet when they were run in IIS Express on the developer machine, and responded perfectly.
    The only difference is that the developer machine has MS Office 2010 installed on it and the server does not. Is there something incorrect about the engine. But everything I found says it is the proper engine.

  10. #10
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Import Excel into a Dataset

    I don't have a web server to duplicate your environment so I have nothing else to suggest.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width