Description
This article contain C# code example to import CSV file data into DataTable and insert bulk records into SQL server database using SQL Bulk Copy.
Summary
1. Import CSV file into DataTable C#.
2. Insert Bulk records into SQL Server using SQL Bulk Copy.
Import CSV file into DataTable C#
We can read data from CSV file in many of custom ways. In this article, I am going to write the C# code to read data from CSV file into .NET DataTable by using TextFieldParser. don’t try to search this class in C# library because which is not available in C#. TextFieldParser is the Visual basic class. So we need to add reference dll Microsoft.VisualBasic.
- Open Visual Studio
- Go to File ->New ->Project.
- Then go to Visual C# ->Windows and select Console Application
- Rename the project name as ReadCSVFile.
- Right-click the Reference, click Add Reference,select Microsoft.VisualBasic, and click OK button
You can use the following code example to read data from CSV file in C# and pass the DataTable object to the function InsertDataIntoSQLServerUsingSQLBulkCopy which is available in below section (Insert Bulk records into SQL Server using SQL Bulk Copy).
using System; using System.Data; using Microsoft.VisualBasic.FileIO; namespace ReadDataFromCSVFile { static class Program { static void Main() { string csv_file_path=@"C:\UsersAdministratorDesktoptest.csv"; DataTable csvData = GetDataTabletFromCSVFile(csv_file_path); Console.WriteLine("Rows count:" + csvData.Rows.Count); Console.ReadLine(); } private static DataTable GetDataTabletFromCSVFile(string csv_file_path) { DataTable csvData = new DataTable(); try { using(TextFieldParser csvReader = new TextFieldParser(csv_file_path)) { csvReader.SetDelimiters(new string[] { "," }); csvReader.HasFieldsEnclosedInQuotes = true; string[] colFields = csvReader.ReadFields(); foreach (string column in colFields) { DataColumn datecolumn = new DataColumn(column); datecolumn.AllowDBNull = true; csvData.Columns.Add(datecolumn); } while (!csvReader.EndOfData) { string[] fieldData = csvReader.ReadFields(); //Making empty value as null for (int i = 0; i < fieldData.Length; i++) { if (fieldData[i] == "") { fieldData[i] = null; } } csvData.Rows.Add(fieldData); } } } catch (Exception ex) { } return csvData; } } }
Insert Bulk Records into SQL Server using SQL Bulk Copy
You can import CSV File into C# DataTable using the function ReadDataFromCSVFile which is available in above section (Import CSV file into DataTable C#).
You can pass this databale object to the following function to insert into SQL Server using SQL Bulk Copy.
// Copy the DataTable to SQL Server using SqlBulkCopy function static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData) { using(SqlConnection dbConnection = new SqlConnection("Data Source=.SQLEXPRESS; Initial Catalog=MorganDB; Integrated Security=SSPI;")) { dbConnection.Open(); using (SqlBulkCopy s = new SqlBulkCopy(dbConnection)) { s.DestinationTableName = "Your table name"; foreach (var column in csvFileData.Columns) s.ColumnMappings.Add(column.ToString(), column.ToString()); s.WriteToServer(csvFileData); } } }
Related Articles
– How to read data from csv file in c#
– Bulk Insert into SQL Server using SqlBulkCopy in C#
– Convert DateTime to Ticks and Ticks to DateTime in C#
– Convert Object To Byte Array and Byte Array to Object in C#
– Add or Remove programs using C# in Control Panel
– Show balloon tooltip c#
Thanks,
Morgan
Software Developer
Hi
Just a few corrections. In C# you dont say function when you declare a method and the inputs csvData and csvFileData do not match. Were you high when you wrote this article?
Stewart
Thanks Stewart, I corrected now
hi friend,
i am working on import functionality from CSV file.
i have below fields in file
SR.No,Name,Age,Gender,Mobile,Address
here are the data for it.
1212, — SR.No
SampleName, — Name
25, — Age
Male, — Gender
9898525241,9562352521 –Mobile
AddressFiled — Address
Mobile field contains "," delimiter so second mobile number goes to Address Field.
can you please tell me how to restrict this type of value truncation..
Thanks in advance.
Hi jayesh, in your case, your mobile field value should be enclosed with char " like "9898525241,9562352521" . then the setting csvReader.HasFieldsEnclosedInQuotes = true; will handle your need
Hi
i want to ask from you that there is any method to assign this csv file data to their data types when importing csv file to sql server?
no it is not possible …becoz CSV file is just a text file..so you can't get data types from CSV file….but you can have column and datatype mapping in other source (ex: xml) then you can map it when import csv file
hi
The code above is working where my data from datatable is added into sql DB but only in the 1st column of database table.
I need datatable to match with 1st row from Csvfile to the columns of database table then add according data to that column.
Hi my name is Kunal I am trying to import a csv file of 4.8 gb but after importing certain records it is giving stat value error of name column which is in hindi plz help me I am in a gr8 mess
Hello Morgan,
Your code rock! Thank you.
Is there a way you can help me also on how to import mulitple CSV file?
Thank you!
I have used the same code , code is running properly but data is not getting posted in the destination table which has been created before in the table . please help me out
Hi, i tried the following code and for some reason it's not working (no error messages either), could you please help me?:
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.VisualBasic.FileIO;
namespace ReadDataFromCSVFile
{
static class Program
{
private static void Main()
{
string csv_file_path = @"C:UsersxxxDesktoptest.csv";
DataTable csvFileData = GetDataTabletFromCSVFile(csv_file_path);
Console.WriteLine("Rows count:" + csvFileData.Rows.Count);
Console.ReadLine();
InsertDataIntoSQLServerUsingSQLBulkCopy(csvFileData);
}
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvFileData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { ";" });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn datecolumn = new DataColumn(column);
datecolumn.AllowDBNull = true;
csvFileData.Columns.Add(datecolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
//Making empty value as null
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvFileData.Rows.Add(fieldData);
}
}
}
catch (Exception ex)
{
}
return csvFileData;
}
private static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
{
using (SqlConnection dbConnection = new SqlConnection(@"Data Source=xxxSQLEXPRESS; Initial Catalog=ImportTest; Integrated Security=SSPI;"))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = "Data";
foreach (var column in csvFileData.Columns)
s.ColumnMappings.Add(column.ToString(), column.ToString());
s.WriteToServer(csvFileData);
}
}
}
}
}