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