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 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:
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:
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:
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.
No comments