Import CSV File into Database in C#

This article describes how to insert csv file into database in C#? Summary of the article:

• Table Creation
• Adding a Connection String
• Include Required Namespaces
• Insert into database from CSV File

Consider that, we have a csv file and it has four columns. We will insert all the data into SQL database from the csv file.

Table Creation
Create a Database in your SQL Server named TestDB. Create a  “myTable” in the database. The SQL scripts for “myTable” table is:

CREATE TABLE [dbo].[myTable](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Column1] [nvarchar](50) NULL,
 [Column2] [nvarchar](50) NULL,
 [Column3] [nvarchar](50) NULL,
 CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Adding a Connection String
we need to add or create a connection string in Web.config file.Add the following element to the <connectionStrings> element in Web.config file:

 <add name="ConStr" connectionString="Data Source=localhost;Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=sa123"
        providerName="System.Data.SqlClient"/>

Include Required Namespaces
We need to include some namespaces.Please add the following namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.IO;

Insert into database from CSV File
Some time we need to import or insert all the CSV files located in a directory into database. The following C# code will insert all the files in a directory into database:

const string CSV_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
string CSVpath = "C:\\CSVFiles";  // CSV file Path
var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
string File_Name = string.Empty;
string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;

for (int i = 0; i < AllFiles.Length; i++)
{
    try
    {
        File_Name = AllFiles[i].Name;
        DataTable dt = new DataTable();
        using (OleDbConnection con = new OleDbConnection(string.Format(CSV_CONNECTIONSTRING, CSVpath)))
        {
            using (OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + File_Name + "]", con))
            {
                da.Fill(dt);
            }
        }
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConStr))
        {
            bulkCopy.ColumnMappings.Add(0, "Column1");
            bulkCopy.ColumnMappings.Add(1, "Column2");
            bulkCopy.ColumnMappings.Add(2, "Column3");
            bulkCopy.DestinationTableName = "myTable";
            bulkCopy.BatchSize = dt.Rows.Count;
            bulkCopy.WriteToServer(dt);
            bulkCopy.Close();
        }                   
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

In this way we can import .csv files into database.