How to bulk copy data from CSV file to SQL?

Question

I tried like this:

    public Datatable GetDatatable(){
        string[] csv=@"1,ABC,Arun,12/12/2017\n
        2,BCD,Sam,10/12/2017\n
        3,XYZ,Ammy,11/12/2017\n
        4,PQR,Varun,9/12/2017\n";
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));

        foreach (var line in csv)
         {
              string[] l=line.split(',');
              table.Rows.Add(l[0]);
              table.Rows.Add(l[1]);
              table.Rows.Add(l[2]);
              table.Rows.Add(l[3]);
         }
         return table;
    }

csv like

1,ABC,Arun,12/12/2017
2,BCD,Sam,10/12/2017
3,XYZ,Ammy,11/12/2017
4,PQR,Varun,9/12/2017

It is done by using foreach. I want same result without using for loop or foreach because CSV contain above 3 million lines. Please give some ideas or suggestions.

That code doesn't even compile. Are you able to provide a [mcve]? In any case a loop is required somewhere. — Steve, Aug 19 '17 at 12:59
What is the reasoning behind not wanting to use a foreach loop? Some kind of looping will be necessary in any case.. — Alen Genzić, Aug 19 '17 at 13:00
You cannot do it. Anyway you or some libraries you will use need to loop line by line csv file — Fabio, Aug 19 '17 at 13:00
@AlenGenzić to improve the performance because it takes more than 2mins to execute. — Prasanna Kumar J, Aug 19 '17 at 13:01
https://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable — Evan Trimboli, Aug 19 '17 at 13:02
If the CSV file is large, it will take a long time and you can't avoid using some kind of loop. — Alen Genzić, Aug 19 '17 at 13:02
@PrasannaKumarJ start splitting the string just one time and not 4 times at each loop. — Steve, Aug 19 '17 at 13:06
@Steve okay i changed as per your suggestion but problem still exists.Any other options you have? — Prasanna Kumar J, Aug 19 '17 at 13:08
Sql Bulk Copy is available See code below. Working Sample tested — Ramankingdom, Aug 19 '17 at 14:00
Check this answer Here is it [How to read a CSV file into a .NET Datatable [closed]](https://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable). You can use ETL tool like [SSIS](https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services) for data integration and data transformations solutions. — James Earnan Villaverde, Aug 19 '17 at 13:10

Isma · Accepted Answer · 2017-08-19T13:12:46.787

1

You could use OLEDB to read the CSV file using SQL (kinda) queries:

Check this answer for an example: Most efficient way to process a large csv in .NET

You could also use an existing library such as FileHelpers: http://www.filehelpers.net/ it allows to parse large CSV files easily and very fast.

edited Aug 19 '17 at 13:12

answered Aug 19 '17 at 13:03

Isma

14,604
5
37
51

its not a answer for SQL Bulk Copy – Ramankingdom Aug 19 '17 at 14:30

Steve · Answer 2 · 2017-08-19T13:17:08.133

In a way or another a loop will be always executed to read the file, split the line and insert it in the DataTable.
There are many free libraries to work with CSV file and if you execute a simple search you will find them really easily.

However your code could be improved (and fixed) with splitting the line just one time and not 4 times in that loop and adding the row with just one single line (Actually your code adds 4 rows for each line and this seems pretty wrong)

foreach (var line in csv)
{
    string[] parts = line.Split(',');
    table.Rows.Add(parts);
}
return table;

If this will be enough for your requirements it is up to you to evaluate. With so many rows to read I would do an extensive comparison of the features and performances of different approaches. (Using libraries or self made code)

Ramankingdom · Answer 3 · 2017-08-19T14:49:28.620

This is working no need to go for third party Use SQLBulk Copy Instead

 public static  void BulInsert()
    {
        var destination = @"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = SampleDatabase; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = False; "; // your comnnection string

        var filename = @"d:\db.csv";//your source file
        var connString = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=No;FMT=Delimited""", Path.GetDirectoryName(filename));
        string query = string.Format("Select * from [{0}]", Path.GetFileName(filename));
        using (var conn = new OleDbConnection(connString))
        {
            conn.Open();

            OleDbCommand command = new OleDbCommand(query, conn);
            var reader = command.ExecuteReader();
            using (SqlConnection destConnection = new SqlConnection(destination))
            {
                try
                {
                    destConnection.Open();
                    using (SqlBulkCopy bulkCopy =
                      new SqlBulkCopy(destConnection))
                    {

                        // what ever mapping with ordinal
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(0, "Id"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(1, "name"));
                        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(2, "visit"));
                        bulkCopy.DestinationTableName =
                            "dbo.Patients";

                        try
                        {
                            bulkCopy.WriteToServer(reader);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {

                            reader.Close();
                        }
                    }
                }
                catch (Exception)
                {

                }

            }
        }

    }

score 1 · Answer 4 · answered Aug 19 '17 at 15:47

You can use Cinchoo ETL library, if you want to convert CSV to Datatable or bulk import CSV to sqlserver easily.

To convert CSV to datatable, the code below shows how to do it

using (var p = new ChoCSVReader("emp.csv").WithFirstLineHeader())
{
    DataTable dt = p.AsDataTable();
}

If you want to bulk import CSV to sqlserver, you can refer this codeproject article on how to do it.

Cinchoo ETL - Bulk Insert CSV File into SQLServer

Hope this helps.

Disclaimer: I'm author of this library.

How to bulk copy data from CSV file to SQL?

4 Answers4