×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Importing Excel to SQL Server using EPPlus

Importing Excel to SQL Server using EPPlus

Importing Excel to SQL Server using EPPlus

(OP)
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

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

CODE

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

Any ideas why its not reading any rows?

RE: Importing Excel to SQL Server using EPPlus

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.

RE: Importing Excel to SQL Server using EPPlus

(OP)
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)

RE: Importing Excel to SQL Server using EPPlus

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close