Excel to SQL Upload

Question

Aim: Import Excel to SQL

Potential Issue:

Wrong file type - this is handled in the 'upload' button by not allowing anything but *.xlsx files. The wrong type of Excel file (i.e. not 2003 onwards) would be good to be able to handle this as well.

Notes:

The below code does work. What I am always looking for it improvements. I have not put in any character checking to the below code but it would be there as required.

Dim oleda As New OleDbDataAdapter()
        Dim ds As New DataSet()
        Dim cmd As New OleDbCommand()

        Dim strServerConnection As [String] = ConfigurationManager.ConnectionStrings("SQLLocal").ConnectionString

        Dim vFileName As String = FileUpload1.PostedFile.FileName
        Dim uploadFolder As String = "C:\sites\Examples\CSVUpload\File\" & vFileName

        FileUpload1.SaveAs(Server.MapPath(Convert.ToString("~/UploadedExcel/") & vFileName))

        Dim excelConnectionString As String = (Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=") & uploadFolder) + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"

        Dim excelConnection As New OleDbConnection(excelConnectionString)

        cmd.CommandText = "Select [name],[address],[phone] from [Sheet1$]"
        cmd.CommandType = CommandType.Text
        cmd.Connection = excelConnection
        excelConnection.Open()

        oleda = New OleDbDataAdapter(cmd)
        oleda.Fill(ds, "dataExcel")


        If ds.Tables("dataExcel").Rows.Count > 0 Then

            For i As Integer = 0 To ds.Tables("dataExcel").Rows.Count - 1
                ds.Tables("dataExcel").Rows(i)("name") = ds.Tables("dataExcel").Rows(i)("Name")
                ds.Tables("dataExcel").Rows(i)("address") = ds.Tables("dataExcel").Rows(i)("Address").ToString().ToLower().Trim()
'Check characters here with App_code and variable
                ds.Tables("dataExcel").Rows(i)("phone") = ds.Tables("dataExcel").Rows(i)("Phone").ToString().ToLower().Trim()
            Next
        End If

        Dim sqlBulk As New SqlBulkCopy(strServerConnection)
        sqlBulk.BatchSize = 150
        sqlBulk.BulkCopyTimeout = 600

        'Give your Destination table name
        sqlBulk.DestinationTableName = "dbo.PersonRecord"

        'map the columns....
        sqlBulk.ColumnMappings.Clear()
        sqlBulk.ColumnMappings.Add("Name", "Name")
        sqlBulk.ColumnMappings.Add("Address", "Address")
        sqlBulk.ColumnMappings.Add("Phone", "Phone")

        'write to db
        sqlBulk.WriteToServer(ds.Tables("dataExcel"))

    End Sub

Answer 1

I'd suggest modularizing it, breaking it up into a few smaller functions that each do a small piece of the task. You might have one routine to open the file, one routine to establish the Excel connection, one to "canonicalize" the data (where you're fixing the Excel data), and one to write the data into the SQL database.

If you break it into smaller functions, each function can then take on appropriate responsibilities, particularly around error handling. Your OpenUserSpreadsheet() function could do checking on the file name, making sure the user's file is of the right type, version, etc. Your OpenExcelConnection() would make sure that the dataset is correct, your NormalizeUserData() function would convert the case of the data and do the string trimming, and so on.

Once you have the code in smaller modules, you'll find its easier to think about the appropriate tasks related to each. You might look at your new WriteToSQL() and say "oh, I wonder what if the column name is wrong? What if the table name is not found? What if I can't log on?" A smaller module dedicated to SQL makes you focus only on SQL problems. You can then do this across all your code making small, specific improvements without the distraction of "what do I need to do with all the things in all of this code?"