Importing Excel to SQL Server using EPPlus

[G12Consult]

I am trying to import the contents of an excel spreadsheet into my database however it keeps displaying my custom error message. When I do a trace and debug, it doesn't seem to be returning any rows hence the reason why it is not progressing with import.

It is finding the file ok it seems.

my code

 Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim SqlCon As String = "Data Source=LAPTOP-JPOC72BA\SQLSERVER2014;Initial Catalog=lhc_ds;User ID=ASPNET;Password=gp8j4y34N6yhdZ8C"
        Dim filePath As String = Server.MapPath("~\Junior_DB.xlsx")
        'Dim filePath As String = Server.MapPath("data.csv")
        Dim importedData As DataTable = ImportExcelSheet(filePath)
        If importedData IsNot Nothing AndAlso importedData.Rows.Count > 0 Then
            Dim dtToInsert As DataTable = MakeTypeTable()

            'remove blank rows from "importedData" and assign it to typeTable
            dtToInsert = importedData.Rows.Cast(Of DataRow)().Where(Function(row) (Not row.ItemArray.All(Function(field) TypeOf field Is System.DBNull OrElse
            String.Compare((TryCast(field, String)).Trim(), String.Empty) = 0))).CopyToDataTable()

            'check rows before insert data into table
            If dtToInsert.Rows.Count > 0 Then
                Using sqlBulk As New SqlBulkCopy(SqlCon)
                    'replace tablename that you want to insert records
                    sqlBulk.DestinationTableName = "Master$"
                    Dim mapColumns As Dictionary(Of String, String) = MakeMappingColumns()
                    If mapColumns IsNot Nothing Then
                        For Each mapping In mapColumns
                            sqlBulk.ColumnMappings.Add(mapping.Key, mapping.Value)
                        Next mapping
                    End If

                    Dim isDone As Boolean = False
                    sqlBulk.WriteToServer(dtToInsert) 'KEY to insert bulk data
                    isDone = True
                    If isDone Then
                        lblMsg.Text = "SUCCESS: Data inserted successfully!"
                    Else
                        lblMsg.Text = "ERROR: Error while inserting data!"
                    End If
                End Using
            Else
                lblMsg.Text = "ERROR: There is no rows to insert data!"
            End If
        Else
            lblMsg.Text = "ERROR: There is no rows to insert data!2"
        End If
    End Sub

    'Start importing "CSV" or "Excel" file, returns imported DataTable
    Public Function ImportExcelSheet(ByVal filePath As String) As DataTable
        Dim dtImportData As New DataTable()
        Try
            'If csv file have header then "true" else "false"
            Dim hasHeader As Boolean = True
            Using pck = New OfficeOpenXml.ExcelPackage()
                Using stream = File.OpenRead(filePath)
                    pck.Load(stream)
                End Using

                'replace excel sheet name, by default "Sheet1"
                Dim ws = pck.Workbook.Worksheets("Sheet1")
                For Each rowCell In ws.Cells(1, 1, 1, ws.Dimension.End.Column)
                    Dim val As String = If(hasHeader, rowCell.Text, String.Format("Column {0}", rowCell.Start.Column))
                    dtImportData.Columns.Add(val)
                Next rowCell

                Dim startRow = If(hasHeader, 2, 1)
                For rowNum = startRow To ws.Dimension.End.Row
                    Dim wsRow = ws.Cells(rowNum, 1, rowNum, ws.Dimension.End.Column)
                    Dim row = dtImportData.NewRow()
                    For Each cell In wsRow
                        row(cell.Start.Column - 1) = cell.Value
                    Next cell
                    dtImportData.Rows.Add(row)
                Next rowNum
            End Using
        Catch
        End Try

        Return dtImportData
    End Function

    'Create type table according to database columns
    Private Function MakeTypeTable() As DataTable
        Dim dtTypeTable As New DataTable()
        dtTypeTable.Columns.Add("Firstname", GetType(String))
        dtTypeTable.Columns.Add("Surname", GetType(String))
        dtTypeTable.Columns.Add("DOB", GetType(Date))
        Return dtTypeTable
    End Function

    'Pairs: 1st is Excel HeaderName, 2nd is Database ColumnName
    Private Function MakeMappingColumns() As Dictionary(Of String, String)
        Dim mappingColumns As New Dictionary(Of String, String)()
        mappingColumns.Add("Firstname", "Firstname")
        mappingColumns.Add("Surname", "Surname")
        mappingColumns.Add("DOB", "DOB")
        Return mappingColumns
    End Function

when I debug the following line is displaying 0 so will error message

lblMsg.Text = "ERROR: There is no rows to insert data!2"

Any ideas why its not reading any rows?

 

[jbenson001]

You will have to debug further into ImportExcelSheet(). Check that your file path is correct. Make sure after you read the stream that you have data in your pck object.

Honestly this seems like a lot of work to import excel data. Personally, I would import the data into a SQL table using SSIS. You can write an SSIS package, or use SQL Server Management Studio. In SSMS, you can right click on the DB name and click "Tasks" --> "Import Data" This will open up the SQL Server Import and Export Wizard. Just follow the wizard steps and you can easily pull the data into a "staging" table, this way you have the raw data. From there you can use .NET to clean up the data any way you want and then insert it into a new table with the formats and columns, etc. that you want.

 

[G12Consult]

Thanks.

I was using this web form to upload as there will be upload of new registrations on a regular basis by people who won't have access to SSMS.

I agree ssis or the import wizard would be easier but I need to get a method to get the data to a staging table from the web frontend.

The web application and sql database will potentially be housed on a share hosting. (AWS)

 

[jbenson001]

No end users need SSMS, only you. I don't know how your process works but if people are giving you spreadsheets on a regular basis, I would have them upload to a server somewhere that you control. You can write your SSIS package to run at any interval you want to check for files and then process. There really would be no need for any manual intervention. But again, I am not sure of your business flow.