Import Data from Excel to SQL Server

[primagic]

I have developed a windows form to import data from an excel spreadsheet into an sql table.

The following code opends a dialog box for them to choose the file:

Protected Sub btnChooseFile_Click(sender As Object, e As EventArgs) Handles btnChooseFile.Click

        Dim myStream As Stream = Nothing
        Dim OpenFileDialog As New OpenFileDialog()
        Dim strFilename As String
        strFilename = txtFilename.Text

        OpenFileDialog.Title = "Please select a file"


        OpenFileDialog.InitialDirectory = "c:\"
        OpenFileDialog.Filter = "Excel 2003 files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xlsx"
        OpenFileDialog.FilterIndex = 2
        OpenFileDialog.RestoreDirectory = True


        If OpenFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Try
                myStream = OpenFileDialog.OpenFile()
                If (myStream IsNot Nothing) Then

                    txtFilename.Text = OpenFileDialog.FileName

                       MsgBox("Import successful", MsgBoxStyle.Information, "Import Successful")

                End If
            Catch Ex As Exception
                MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
            Finally
                ' Check this again, since we need to make sure we didn't throw an exception on open. 
                If (myStream IsNot Nothing) Then
                    myStream.Close()
                End If
            End Try
        End If

   End Sub

This is my code to connect to the spreadsheet.

Private Sub ImportReportDetails1()

        Dim strFilename As String
        strFilename = txtFilename.Text

        Dim oconn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFilename & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;""")

        Try
            Dim ocmd As New OleDbCommand("select * from [Report Detail 1$]", oconn)
            oconn.Open()
            Dim odr As OleDbDataReader = ocmd.ExecuteReader()
            Dim PrimaryAcctNumber As String = ""
            Dim CustomerType As String = ""
            Dim PaymentStatus As String = ""
            Dim PaymentDate As String = ""
            Dim PaymentReason As String = ""
            Dim Source As String = ""
            Dim PaymentType As String = ""
            Dim Amount As String = ""
            Dim CSR As String = ""
            Dim PaymentID As String = ""
            Dim AppliedDate As String = ""
            Dim RejectionDate As String = ""
            Dim RejectionReason As String = ""
            Dim Counter As String = ""
            Dim CustEirMetFlag As String = ""

            Dim i As Integer = 0
            While odr.Read()

                If i < 2 Then 'Skip the first couple of rows
                    i += 1
                    Continue While

                End If
                PrimaryAcctNumber = odr.GetValue(0).ToString
                CustomerType = odr.GetValue(1).ToString
                PaymentStatus = odr.GetValue(2).ToString
                PaymentDate = odr.GetValue(3).ToString
                PaymentReason = odr.GetValue(4).ToString
                Source = odr.GetValue(5).ToString
                PaymentType = odr.GetValue(6).ToString
                Amount = odr.GetValue(7).ToString
                CSR = odr.GetValue(8).ToString
                PaymentID = odr.GetValue(9).ToString
                AppliedDate = odr.GetValue(10).ToString
                RejectionDate = odr.GetValue(11).ToString
                RejectionReason = odr.GetValue(12).ToString
                Counter = odr.GetValue(13).ToString
                CustEirMetFlag = odr.GetValue(14).ToString
                InsertDetails1(PrimaryAcctNumber, CustomerType, PaymentStatus, PaymentDate, PaymentReason, Source, PaymentType, Amount,  CSR, PaymentID, AppliedDate, RejectionDate, RejectionReason, Counter, CustEirMetFlag)
            End While
            oconn.Close()
        Catch ee As DataException
            'lblmsg.Text = ee.Message
            'lblmsg.ForeColor = System.Drawing.Color.Red
        Finally


        End Try
   End Sub

For some reason it wont let me import an xlsx spreadsheet. Only xls. Says 'external table is not in the expected format'

Here is my code to import to SQL database using a stored procedure

Public Sub InsertDetails1(PrimaryAcctNumber As String, CustomerType As String, PaymentStatus As String, PaymentDate As DateTime, PaymentReason As String, _
                              Source As String, PaymentType As String, Amount As String, CSR As String, PaymentID As String, _
                              AppliedDate As DateTime, RejectionDate As DateTime, RejectionReason As String, Counter As String, _
                              CustEirMetFlag As String)
        Dim conn As New SqlConnection("DATA SOURCE=bs1;User ID=??;Password=??;Initial Catalog=??;Persist Security Info=false;")
        Dim cmd As New SqlCommand()

        cmd.Connection = conn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "spInsertPayments"
        cmd.Parameters.Add("@PrimaryAcctNumber", SqlDbType.VarChar).Value = PrimaryAcctNumber
        cmd.Parameters.Add("@CustomerTYpe", SqlDbType.VarChar).Value = CustomerType
        cmd.Parameters.Add("@PaymentStatus", SqlDbType.VarChar).Value = PaymentStatus
        cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = PaymentDate
        cmd.Parameters.Add("@PaymentReason", SqlDbType.VarChar).Value = PaymentReason
        cmd.Parameters.Add("@Source", SqlDbType.VarChar).Value = Source
        cmd.Parameters.Add("@PaymentType", SqlDbType.VarChar).Value = PaymentType
        cmd.Parameters.Add("@Amount", SqlDbType.VarChar).Value = Amount
        cmd.Parameters.Add("@CSR", SqlDbType.VarChar).Value = CSR
        cmd.Parameters.Add("@PaymentID", SqlDbType.VarChar).Value = PaymentID
        cmd.Parameters.Add("@AppliedDate", SqlDbType.DateTime).Value = AppliedDate
        cmd.Parameters.Add("@RejectionDate", SqlDbType.DateTime).Value = RejectionDate
        cmd.Parameters.Add("@RejectionReason", SqlDbType.VarChar).Value = RejectionReason
        cmd.Parameters.Add("@Counter", SqlDbType.VarChar).Value = Counter
        cmd.Parameters.Add("@CustEirMetFlag", SqlDbType.VarChar).Value = CustEirMetFlag
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

    End Sub

My import fails on the date fields telling me 'string cannot be converted to date' or something along those lines.

There is a mixture of US and UK dates. The sql server is in UK date.

How would I ensure the dates get taken from the strings read from the spreadsheet as the correct format.

So dates in the spreadshee look like

04/15/2014 05:00 AM

I need it to be

15/04/2014 05:00

.

Some dates also have two digits for the year, so 15/04/40 would import as 15/04/2040 which is wrong should be 1940.

Help please!!.

Much appreciated

 

[jebenson]

Try adding "IMEX=1" at the end of your connection string, like so: HDR=YES;IMEX=1

As to the dates, I'm not sure what to do. The problem is, when reading them in should "12/11/14" be read as November 11, or December 12? If you have some way of knowing whether a given date is UK or US format, I would suggest cleaning up the Excel file to make all dates the format you want before running the import.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!