Home Asp.net How to bulk insert in SQL server from CSV File?

How to bulk insert in SQL server from CSV File? [SQL Bulk Insert CSV]

By
Mayank Modi
-
1649
2

In my previous tutorials, I’d explained get gridview selected row hiddenfield value using jquery, how to keep gridview selected rows during paging, how to print gridview data on print button click event, and other more cracking tutorials on GridView, Asp.net here. Now here in this tutorial, I’ll explain bulk insert in SQL server from CSV File using SqlBulkCopy in asp.net using c# and vb.net with example code.

Import CSV Data in SQL Server Using .NET Bulk Insert

For this tutorial, we need to create one database table which will be needed while inserting imported data into SQL server table. So, simply execute the following script into the SQL query editor to create “SubjectDetails” table.

CREATE TABLE [dbo].[SubjectDetails]
(
    [SubjectId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [SubjectName] [nvarchar](100) NULL,
    [Marks] [int] NULL,
    [Grade] [nvarchar](50) NULL
)

Following are the two key points that is the part of this tutorial. So, I recommend you to check before go through this tutorial.

  • To read the data from CSV or excel file, I used EPPlus Library which you can get and download from the link given.
  • For bulk insert imported data into SQL server, I used SqlBulkCopy class which imported from System.Data.SqlClient class.

HTML Markup Code To Import CSV Data in SQL Server – [.aspx]

Now add the following HTML markup code in your .aspx page:

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Bulk insert in SQL server from CSV file</title>
</head>
<body>
    <form id="form1" runat="server">
        <h4>SQL server export table to CSV</h4>
        <div>
            <asp:Button ID="btnImport" runat="server" Text="Start Import" OnClick="btnImport_Click" />
            <br />
            <br />
            <asp:Label ID="lblMsg" runat="server" />
        </div>
    </form>
</body>
</html>

As you can see from the above code, I defined OnClick=”btnImport_Click” event which will trigger the button click event and executes the code-behind code that is defined under it.

Note: Don’t forget to add EPPlus.dll reference in your project. To give reference Right-click on Reference > Add New Reference > Browse to EPPlus.dll > Select OK.

SQL bulk insert CSVUsing C# – [.cs]

First, add the following required namespaces:

using System.IO;
using System.Data.SqlClient;

And now following is the code-behind code for .cs file to Bulk insert in SQL Server from CSV file:

protected void Page_Load(object sender, EventArgs e) {
}

protected void btnImport_Click(object sender, EventArgs e) {
    string SqlCon = "Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB";
    string filePath = Server.MapPath("data.xlsx");
    //string filePath = Server.MapPath("data.csv");
    DataTable importedData = ImportExcelSheet(filePath);

    if (importedData != null && importedData.Rows.Count > 0) {
        DataTable dtToInsert = MakeTypeTable();//remove blank rows from "importedData" and assign it to typeTable
        dtToInsert = importedData.Rows.Cast<DataRow>()
        .Where(row => !row.ItemArray.All(field => field is System.DBNull ||
        string.Compare((field as string).Trim(), string.Empty) == 0)).CopyToDataTable();

        //check rows before insert data into table
        if (dtToInsert.Rows.Count > 0) {
            using (SqlBulkCopy sqlBulk = new SqlBulkCopy(SqlCon)) {
                //replace tablename that you want to insert records
                sqlBulk.DestinationTableName = "SubjectDetails";
                Dictionary<string, string> mapColumns = MakeMappingColumns();
                if (mapColumns != null) {
                    foreach (var mapping in mapColumns) {
                        sqlBulk.ColumnMappings.Add(mapping.Key, mapping.Value);
                    }
                }

                bool isDone = false;
                sqlBulk.WriteToServer(dtToInsert); //KEY to insert bulk data
                isDone = true;

                if (isDone) {
                    lblMsg.Text = "SUCCESS: Data inserted successfully!";
                } else {
                    lblMsg.Text = "ERROR: Error while inserting data!";
                }
            }
        } else {
            lblMsg.Text = "ERROR: There is no rows to insert data!";
        }
    } else {
        lblMsg.Text = "ERROR: There is no rows to insert data!";
    }
}

//Start importing "CSV" or "Excel" file, returns imported DataTable
public DataTable ImportExcelSheet(string filePath) {
    DataTable dtImportData = new DataTable();
    try {
        //If csv file have header then "true" else "false"
        bool hasHeader = true;
        using (var pck = new OfficeOpenXml.ExcelPackage()) {
            using (var stream = File.OpenRead(filePath)) {
                pck.Load(stream);
            }

            //replace excel sheet name, by default "Sheet1"
            var ws = pck.Workbook.Worksheets["Sheet1"];
            foreach (var rowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) {
                string val = hasHeader ? rowCell.Text : string.Format("Column {0}", rowCell.Start.Column);
                dtImportData.Columns.Add(val);
            }

            var startRow = hasHeader ? 2 : 1;
            for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) {
                var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                var row = dtImportData.NewRow();
                foreach (var cell in wsRow) {
                    row[cell.Start.Column - 1] = cell.Value;
                }
                dtImportData.Rows.Add(row);
            }
        }
    } catch { }

    return dtImportData;
}

//Create type table according to database columns
private DataTable MakeTypeTable() {
    DataTable dtTypeTable = new DataTable();
    dtTypeTable.Columns.Add("SubjectName", typeof(String));
    dtTypeTable.Columns.Add("Marks", typeof(Int32));
    dtTypeTable.Columns.Add("Grade", typeof(String));
    return dtTypeTable;
}

//Pairs: 1st is Excel HeaderName, 2nd is Database ColumnName
private Dictionary<string, string> MakeMappingColumns() {
    Dictionary<string, string> mappingColumns = new Dictionary<string, string>();
    mappingColumns.Add("Subject Name", "SubjectName");
    mappingColumns.Add("Marks", "Marks");
    mappingColumns.Add("Grade", "Grade");
    return mappingColumns;
}

SQL bulk insert CSV Using Vb.net – [.vb]

First, add the following required namespaces:

Imports System.IO
Imports System.Data.SqlClient

And now following is the code-behind code for .vb file to read Bulk insert in SQL server from CSV file:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub

Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim SqlCon As String = "Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB"
    Dim filePath As String = Server.MapPath("data.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 = "SubjectDetails"
                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!"
    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("SubjectName", GetType(String))
    dtTypeTable.Columns.Add("Marks", GetType(Int32))
    dtTypeTable.Columns.Add("Grade", GetType(String))
    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("Subject Name", "SubjectName")
    mappingColumns.Add("Marks", "Marks")
    mappingColumns.Add("Grade", "Grade")
    Return mappingColumns
End Function
You may also like, 100+ Frequently Asked Interview Questions on Asp.net, Top 10 OOPS Concepts in .NET C# with Examples here.

Example Result

How to bulk insert in SQL server from CSV File? [SQL Bulk Insert CSV]
How to bulk insert in SQL server from CSV File? [SQL Bulk Insert CSV]

Download Sample Code

Previous articleBest 100+ Frequently Asked Interview Questions in .Net SQL
Next articleTooltip: Show Hide Hint Help Text Example in CSS3 Transition
Mayank Modi
Hi there, I am Mayank, the man behind Technical Mack. I started AspnetO with a motive to educate people on various programming languages ranging from beginners to expert level. Through this blog, I aim to provide more insightful content.

RELATED ARTICLESMORE FROM AUTHOR

2 COMMENTS

  1. Halo Mayank Modi.
    Thank you for you article, that is very useful for me and I try to implement it into my project. However I got an error “A disk error occurred during a write operation. (Exception from HRESULT: 0x8003001D (STG_E_WRITEFAULT))”. I’am really sure that I already follow your instruction. I’m working with .Net Framework 4.0 and Visual Studio 2012. Any help would be appreciated.
    Thank you very much.

    • Make sure you have a valid Excel file with the required header and worksheet name (defaults to “Sheet1”) or change with yours.

      From where you are getting this error?

LEAVE A REPLY

Please enter your comment!
Please enter your name here

one × two =