Excel to SQL Table with VB.net

Question

I've read all the articles and posts from other users on this subject and I'm still stuck.

Essentially what I have is a VB.net program with a local SQL backend. I created a table called "consolDump" that I wish to import an Excel sheet into. I feel like I'm very close just from the help I've gotten from the other people with a similar problem. Just to clarify, I do not have the ability to add software to the machine I'm using (it's heavily restricted by IT), and do not have access to the SQL server import utility.

Here's the code I have. Any help would be appreciated.

Imports System.Data.SqlClient
Public Class formImport

    Private Sub buttonConsolImport_Click(sender As Object, e As EventArgs) Handles buttonConsolImport.Click

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim fBrowse As New OpenFileDialog
        Dim fname As String

        Try
            With fBrowse
                .Filter = "Excel files(*.xls)|*.xls|All files (*.*)|*.*"
                .FilterIndex = 1
                .Title = "Import data from Excel file"

            End With
            If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then

                fname = fBrowse.FileName
                MyConnection = New System.Data.OleDb.OleDbConnection _
                    ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fname & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""")
                MyCommand = New System.Data.OleDb.OleDbDataAdapter("A1, B1, C1, D1, E1, F1, G1, H1, I1, J1, k1, L1 from [consol_data$]", MyConnection)
                MyCommand.TableMappings.Add("Table", "consolDump")
                DtSet = New System.Data.DataSet
                MyCommand.Fill(DtSet)

                For Each Drr As DataRow In DtSet.Tables(0).Rows
                    Execute_Local("INSERT INTO consolDump(PO_Number, Consol_ID, Status, Contractor, UTAS_Owner, Description, Start_Date, End_Date, Total_Spend, ) VALUES ('" & Drr(0).ToString & "','" & Drr(1).ToString & "','" & Drr(2).ToString & "'),'" & Drr(3).ToString & "','" & Drr(4).ToString & "','" & Drr(5).ToString & "','" & Drr(6).ToString & "','" & Drr(7).ToString & "','" & Drr(8).ToString & "','" & Drr(9).ToString & "','" & Drr(10).ToString & "','" & Drr(11).ToString & "'")
                Next
                MsgBox("Success")
                MyConnection.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub

Ok so, to add to what I've asked, I'm trying to import a csv file from the excel sheet with this code, which works fine.

    'Converts consol data to csv file===========================
    Dim excelApplication As New Excel.Application
    Dim excelWrkBook As Excel.Workbook

    excelApplication.Visible = False
    excelApplication.DisplayAlerts = False
    excelWrkBook = excelApplication.Workbooks.Open("R:\PECOE-WLOX\QuEST\Torres\sqlProjects\consol_data.xls")
    excelWrkBook.SaveAs(Filename:="R:\PECOE-WLOX\QuEST\Torres\sqlProjects\consol_data.csv", FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)

    excelWrkBook.Close()
    excelApplication.DisplayAlerts = True
    excelApplication.Quit()
    MessageBox.Show("Converted to CSV")
    '============================================================

Now I'm trying to import the csv file into a new data table but I'm getting a strange error that says "system.data.sql is a namespace and cannot be used as an expression". Now this is probably my inexperience as I pulled the code from another site and several said they have it working. I've modified it to fit my data. Any help would be appreciated. The error appears as a syntax error on "Dim cmd As New SqlClient.SqlCommand(Sql, connection)". It's highlighting the Sql in the paranthesis as a namespace.

    Dim consolDump1 As New DataTable()

    consolDump1.Columns.Add("PO_Number")
    consolDump1.Columns.Add("Consol_ID")
    consolDump1.Columns.Add("Status")
    consolDump1.Columns.Add("Contractor")
    consolDump1.Columns.Add("UTAS_Owner")
    consolDump1.Columns.Add("Description")
    consolDump1.Columns.Add("Start_Date")
    consolDump1.Columns.Add("End_Date")
    consolDump1.Columns.Add("Total_Spend")
    consolDump1.Columns.Add("Job_Title")
    consolDump1.Columns.Add("Location")
    consolDump1.Columns.Add("Type")

    Dim parser As New FileIO.TextFieldParser("R:\PECOE-WLOX\QuEST\Torres\sqlProjects\consol_data.csv")

    parser.Delimiters = New String() {","} ' fields are separated by comma
    parser.HasFieldsEnclosedInQuotes = True ' each of the values is enclosed with double quotes
    parser.TrimWhiteSpace = True

    parser.ReadLine()

    Do Until parser.EndOfData = True
        consolDump1.Rows.Add(parser.ReadFields())
    Loop

    Dim strSql As String = "INSERT INTO consolDump(PO_Number,Consol_ID,Status,Contractor,UTAS_Owner,Description,Start_Date,End_Date,Total_Spend,Job_Title,Location,Type) VALUES (@PO_Number,@Consol_ID,@Status,@Contractor,@UTAS_Owner,@Description,@Start_Date,@End_Date,@Total_Spend,@Job_Title,@Location,@Type)"
    Dim SqlconnectionString As String = "Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\consolData.mdf;Integrated Security=True;Connect Timeout=30"

    Using connection As New SqlClient.SqlConnection(SqlconnectionString)
        Dim cmd As New SqlClient.SqlCommand(Sql, connection)

        ' create command objects and add parameters
        With cmd.Parameters
            .Add("@PO_Number", SqlDbType.VarChar, 15, "PO_Number")
            .Add("@Consol_ID", SqlDbType.BigInt, "Consol_ID")
            .Add("@Status", SqlDbType.Text, "Status")
            .Add("@Contractor", SqlDbType.Text, "Contractor")
            .Add("@UTAS_Owner", SqlDbType.Text, "UTAS_Owner")
            .Add("@Description", SqlDbType.Text, "Description")
            .Add("@Start_Date", SqlDbType.Date, "Start_Date")
            .Add("@End_Date", SqlDbType.Date, "End_Date")
            .Add("@Total_Spend", SqlDbType.Money, "Total_Spend")
            .Add("@Job_Title", SqlDbType.Text, "Job_Title")
            .Add("@Location", SqlDbType.Text, "Location")
            .Add("@Type", SqlDbType.Text, "Type")
        End With

        Dim adapter As New SqlClient.SqlDataAdapter()
        adapter.InsertCommand = cmd

        '--Update the original SQL table from the datatable
        Dim iRowsInserted As Int32 = adapter.Update(consolDump1)

    End Using

End Sub

Please clarify what you problem is exactly. Also include all (if any) errors or other messages you get. — K_B
– K_B, Commented Apr 17, 2014 at 14:44
I appologize. I got side tracked when writing my original post. I need to import an excel sheet and have that data dumped in to the data table called consolDump. — CTTKDKing
– CTTKDKing, Commented Apr 17, 2014 at 14:56
Right now I'm getting a syntax error on "execute_local" in the For loop. — CTTKDKing
– CTTKDKing, Commented Apr 17, 2014 at 16:37

Joel Coehoorn · Accepted Answer · 2014-04-21 15:15:59Z

1

There is an extra comma at the end of the column list in your sql query:

End_Date, Total_Spend, )

You need this:

End_Date, Total_Spend )

Also, the column list has 9 items, but your values list has 12. Are you maybe missing a few columns?

Finally for this section, it doesn't matter much with code for personal use, but what you're doing with string concatenation to put the excel data into your query is considered bad practice. If you do that in product code, you're creating a huge security hole. Instead, learn about parameterized queries.

In the later sample, you define your sql string this way:

Dim strSql As String

But try to include in your command this way:

Dim cmd As New SqlClient.SqlCommand(Sql, ...

You should do this:

Dim cmd As New SqlClient.SqlCommand(strSql, ...

Also, based on the other code, if this is an access database, you should still be using the OleDbCommand and OleDbConnection objects, in the System.Data.OleDb namespace.

What I would do is somewhere in between your first sample and your second:

Imports System.Data.SqlClient

Private Sub buttonConsolImport_Click(sender As Object, e As EventArgs) Handles buttonConsolImport.Click

    Dim fBrowse As New OpenFileDialog
    With fBrowse
        .Filter = "Excel files(*.xls)|*.xls|All files (*.*)|*.*"
        .FilterIndex = 1
        .Title = "Import data from Excel file"
    End With

    If fBrowse.ShowDialog() <> Windows.Forms.DialogResult.OK Then Exit Sub
    Dim fname As String = fBrowse.FileName

    Dim sql As String = "INSERT INTO consolDump(PO_Number,Consol_ID,[Status],Contractor,UTAS_Owner,Description,Start_Date,End_Date,Total_Spend,Job_Title,Location,Type) VALUES (@PO_Number,@Consol_ID,@Status,@Contractor,@UTAS_Owner,@Description,@Start_Date,@End_Date,@Total_Spend,@Job_Title,@Location,@Type)"

    Try 
        Dim parser As New FileIO.TextFieldParser(fname)
        parser.Delimiters = New String() {","} ' fields are separated by comma
        parser.HasFieldsEnclosedInQuotes = True ' each of the values is enclosed with double quotes
        parser.TrimWhiteSpace = True

        parser.ReadLine() 'skip column headers

        Using cn As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\consolData.mdf;Integrated Security=True"), 
              cmd As New SqlCommand(sql, cn)

            With cmd.Parameters
                .Add("@PO_Number", SqlDbType.VarChar, 15)
                .Add("@Consol_ID", SqlDbType.BigInt)
                .Add("@Status", SqlDbType.Text) 'I doubt that "Text" is the right type for all of these
                .Add("@Contractor", SqlDbType.Text) 'If it is, you may want to examine your table schema
                .Add("@UTAS_Owner", SqlDbType.Text)
                .Add("@Description", SqlDbType.Text)
                .Add("@Start_Date", SqlDbType.Date)
                .Add("@End_Date", SqlDbType.Date)
                .Add("@Total_Spend", SqlDbType.Money)
                .Add("@Job_Title", SqlDbType.Text)
                .Add("@Location", SqlDbType.Text)
                .Add("@Type", SqlDbType.Text)
            End With

            cn.Open()
            Do Until parser.EndOfData = True
                Dim fields() As String = parser.ReadFields()
                For i As Integer = 0 To 11
                    cmd.Parameters(i).Value = fields(i)
                Next
                cmd.ExecuteNonQuery()
            Loop
        End Using
        MsgBox("Success")

    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try

End Sub

edited Apr 21, 2014 at 15:15

answered Apr 21, 2014 at 14:41

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

CTTKDKing Over a year ago

Thank you. The first block of code with the miscalculated columns and whatnot is no longer in the project. Everything after the csv conversion is there now. That said, the change over in the cmd to "Dim cmd As New SqlClient.SqlCommand(strSql, ..." cleared the syntax error so it now runs. when importing it throws an "unhandeled exception of 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll". It doesn't like the "Status" field. It thinks I'm trying to convert to integer.

CTTKDKing Over a year ago

Oh and Thank you. I will spend some time looking into parameterized queries for my own knowlege. For this project the csv import should be sufficient. It's part of a program that'll be offline and internally used on a heavily guarded network and inaccessible from the outside so security isn't an issue.

Joel Coehoorn Over a year ago

@CTTKDKing I've added some code. It went through a few revisions, so if you saw it earlier, it's worth looking through again.

CTTKDKing Over a year ago

Added a screenshot of an error I get with your code. Otherwise it runs nicely with no errors so thank you very much for the help.

Joel Coehoorn Over a year ago

@CTTKDKing You'll have to debug this. I typed my answer right into the reply window without testings, as I don't have your testing materials. Looking at the error, it seems like it's basic debugging issue. You should be able to step through the code in Visual Studio to see what's wrong.

|

Collectives™ on Stack Overflow

Excel to SQL Table with VB.net

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related