bulding excel to sql server interface

[dotolee]

i'm building an import routine for my application - the goal is to have the user be able to select an excel spreadsheet that looks identical to my sql server database table.

so far, i've built a small prototype and the code looks like this:

    Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
        Try
            Dim DtSet As System.Data.DataSet
            Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
                                & "Data Source='" & strFileToOpen & "';Extended Properties=""Excel 8.0;HDR=YES;IMEX=0""")
            DtSet = New System.Data.DataSet
            excelConnection.Open()
            Dim excelCommand As New System.Data.OleDb.OleDbDataAdapter("INSERT INTO [ODBC; Driver={SQLServer};Server=(local);Database=territoreLIVE;Trusted_Connection=yes].[Householder]SELECT * FROM [Sheet1$];", excelConnection)
            excelCommand.Update(DtSet)
            excelConnection.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub btnReadExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadExcel.Click

        'Get user to select file.
        If Not (OpenFileDialog1.ShowDialog() = DialogResult.OK) Then
            'MsgBox("problems with the Open File Dialog. Contact your administrator.")
            Exit Sub
        End If

        '2007.07.30 Enable MultiFile Selects
        strFileToOpen = OpenFileDialog1.FileName


        Dim MyConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
                    & "Data Source='" & strFileToOpen & "';Extended Properties=""Excel 8.0;HDR=YES;IMEX=0""")

        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

        Try

            'MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from A1:L57", MyConnection)
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0).DefaultView
            MyCommand = Nothing
            DtSet = Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

I am able to select the excel file, have it appear in the datagrid. but when i try the import, i get an error message:

Update unable to find TableMapping['Table'] or Datatable 'Table'.

I'm not sure what i'm doing wrong. Also, how do i handle scenarios where the record already exists in the database... do i have to attempt to insert each record one at a time, checking if it already exists first?

 

[MattP]

You go through the work to get your Excel spreadsheet into a DataSet DtSet in btnReadExcel.Click and after binding it to your DataGridView you go and do DtSet = Nothing.

Then in btnImport.Click you dimension another DataSet with the same name DtSet and expect it to have all of your information in it. You need to pass in the DataSet you filled from the Excel sheet to be able to do anything with it.

Please for the love of sanity name your variables something meaningful. Naming your adapters xxxCommand hurts my head.

You connection string to "my sql server database" or is that MySQL? shouldn't have Excel 8.0 anywhere in it. I'd take a look @ connectionstrings.com to what you need to connect to SQL.

 

[dotolee]

thank you

I got my basic code to start off from other posts... like Import Excel Data into SQL Server with VB.NET - .NET.
this is just a prototype to see how things work... so i apologize for the confusing names...

 

[MattP]

Here's a sampling from when I was learning about connecting to Excel via Jet and ACE. Hopefully it gives you some ideas about where to turn.

Class scope for my DataSet and OleDbConnection so I can use them throughout.

	Dim ds As New DataSet()
	Dim cn As OleDbConnection

OpenFileDialog to select a file. Excel 2007 and 2003 use different providers so you need to check which type of file it is before you can determine the connection string.

In my case I was letting the user select which WorkSheet they wanted to pull data from via a ComboBox. Extra tables I found and didn't want commented. I didn't try a worksheet with graphs on it but I wouldn't be surprised if they showed up here as well.

	Private Sub btnSelectFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles btnSelectFile.Click

		Me.OpenFileDialog1.ShowDialog()

		Dim doc As New FileInfo(Me.OpenFileDialog1.FileName)

		If Path.GetExtension(Me.OpenFileDialog1.FileName) = ".xlsx" Then
			'Excel 2007
			cn = New OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties={1}Excel 12.0 Xml;HDR=YES;IMEX=0{1}", doc.FullName, ControlChars.Quote))
		ElseIf Path.GetExtension(Me.OpenFileDialog1.FileName) = ".xls" Then
			'Excel 2003 & earlier
			cn = New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties={1}Excel 8.0;HDR=YES;IMEX=0{1}", doc.FullName, ControlChars.Quote))
		Else
			MessageBox.Show("Please Select An Excel File", _
			"Invalid File", _
			MessageBoxButtons.OK, _
			MessageBoxIcon.Error)
			Exit Sub
		End If

		cn.Open()
		'Get list of worksheets in Excel File
		Dim workSheets As DataTable = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)

		If workSheets Is Nothing OrElse workSheets.Rows.Count = 0 Then
			MessageBox.Show("Could Not Determine File Worksheets", _
			"No Worksheets Found", _
			MessageBoxButtons.OK, _
			MessageBoxIcon.Error)
			Exit Sub
		Else
			Me.cboWorkSheets.Items.Clear()
			For Each row As DataRow In workSheets.Rows
				'xlsx files put an extra table in the results called _xlnm#_FilterDatabase
				'xls files put an extra table in the results called <tablename>$_
				'Some printer formatting tables can show up.  Checking if a table ends w/ $ or $' seems to fix this
				If Not row.Item("Table_Name").ToString() = "_xlnm#_FilterDatabase" AndAlso _
				Not row.Item("Table_Name").ToString().EndsWith("_") AndAlso _
				(row.Item("Table_Name").ToString().EndsWith("$") Or _
				row.Item("Table_Name").ToString().EndsWith("$'")) Then
					Me.cboWorkSheets.Items.Add(row.Item("Table_Name").ToString())
				End If
			Next
		End If

		cn.Close()

	End Sub

	Private Sub cboWorkSheets_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles cboWorkSheets.SelectedIndexChanged

		Dim cmd As OleDbCommand = _
		 New OleDbCommand(String.Format("SELECT * FROM [{0}] ", Me.cboWorkSheets.SelectedItem.ToString()), cn)

		Dim da As New OleDbDataAdapter()

		ds.Clear()

		cn.Open()
		da.SelectCommand = cmd
		'Marks all rows as new so they're included in the update command when writing the file
		da.AcceptChangesDuringFill = False
		da.Fill(ds, "File")
		cn.Close()

		Me.DataGridView1.DataSource = ds.Tables("File")

	End Sub