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.
(
[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:
<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.
SQL bulk insert CSVUsing C# – [.cs]
First, add the following required namespaces:
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 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.Data.SqlClient;
And now following is the code-behind code for .vb file to read Bulk insert in SQL server from CSV file:
End SubProtected 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
Example Result
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?