Upload CSV file to SQL server

Question

What is the best way to upload a large csv data file into SQL server using C# ? The file contains about 30,000 rows and 25 columns.

Answer 1

1st off, You don't need programming stuff. You can directly upload CSV files into SQL Database with SQL management tools. However, if you really need do it through programming, Just read below.

Personally, I think this approach is the most efficient and easiest way to do through programming.

In general, you can achieve it in two steps

1st step is to read the CSV file and hold the records as a DataTable.
2nd step is store the retrieved DataTable into SQL Database Table as a Bulk Entry

This is a function that returns CSV File Data as a DataTable. Call and Keep it in the memory and you can do whatever you want with it.

This function is going to return CSV Read file into DataTable.

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 null;
        }
        return csvData;
    }
  }

SQLBulkCopy - Use this function to insert the Retrieved DataTable into Sql Table

static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
{
    using(SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=yourDB;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);
         }
     }

Source

Answer 2

Here is another way of uploading CSV to database via programming. Cinchoo ETL, an open source library can do the job of uploading CSV file to database using DataReader, which is streaming approach with little to none memory overhead.

Here is sample how to do it

string connectionstring = @"#YOUR DB ConnectionString#";
using (SqlBulkCopy bcp = new SqlBulkCopy(connectionstring))
{
    using (var p = new ChoCSVReader("#YOUR CSV FILE#").WithFirstLineHeader())
    {
        bcp.DestinationTableName = "#TABLENAME#";
        bcp.EnableStreaming = true;
        bcp.BatchSize = 10000;
        bcp.BulkCopyTimeout = 0;
        bcp.NotifyAfter = 100;
        bcp.SqlRowsCopied += delegate (object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine(e.RowsCopied.ToString("#,##0") + " rows copied.");
        };
        bcp.WriteToServer(p.AsDataReader());
    }
}

Answer 3

The best way I found to import large CSV files into SQL Server is by using SqlBulkCopy along with IDataReader implementation. The good thing about it is that you're not reading the entire file into memory (which is the case using DataTable approach) and you can control the size of the batch which gets sent to SQL Server. The bad thing about it is that you have to implement IDataReader which is one of the longest MS interfaces I've seen.

I wrote a nuget package that does the trick for you. It uses the awesome CsvHelper package so there's very little config required. The simplest scenario would look like this:

//Instantiate the reader, providing the list of columns which matches 1 to 1 the data table structure.
var dataReader = new CsvDataReader(filePath,
    new List<TypeCode>(5)
    {
        TypeCode.String,
        TypeCode.Decimal,
        TypeCode.String,
        TypeCode.Boolean,
        TypeCode.DateTime
    });

bulkCopyUtility.BulkCopy("TableName", dataReader);

There are also additional configuration options for more complex scenarios (flexible column mapping, additional static column values which are not present in the csv file, value transformation). If you're interested, the project is on Github and available as a nuget package.

For reference, here's how to use SqlBulkCopy with IDataReader:

public void BulkCopy(string tableName, IDataReader dataReader, Action<SqlBulkCopy>  configureSqlBulkCopy)
{
    using (SqlConnection dbConnection = new SqlConnection(connectionString))
    {
        dbConnection.Open();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnection))
        {
            bulkCopy.BatchSize = 3000; //Data will be sent to SQL Server in batches of this size
            bulkCopy.EnableStreaming = true;
            bulkCopy.DestinationTableName = tableName;

            //This will ensure mapping based on names rather than column position
            foreach (DataColumn column in dataReader.GetSchemaTable().Columns)
            {
                bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
            }

            //If additional, custom configuration is required, invoke the action
            configureSqlBulkCopy?.Invoke(bulkCopy);

            try
            {
                // Write from the source to the destination.
                bulkCopy.WriteToServer(dataReader);
            }
            finally
            {
                dataReader.Close();
            }
        }
    }
}

Answer 4

Use System.Data.SqlClient.SqlBulkCopy class to insert data into Sql tables. To use that class you also need to convert CVS data to DataTable, see here one of the ways.

Answer 5

This sounds like a perfect job for SSIS. It's a free part of SQL Server, can loop through all the csv files in a folder, is very fast, and has excellent error handling and logging.

Answer 6

This technique uses the SQLBulkCopy() facility, but does not read the entire file into memory.

The trick is that it implements a IDataReader class to read the .csv file.

https://www.codeproject.com/Tips/1029831/Fast-and-Simple-IDataReader-Implementation-to-Read

Answer 7

You can also use Bulk Insert

Public Shared Function bulkQuery()

        Dim query As StringBuilder = New StringBuilder

        query.Append("USE Import_DB BULK INSERT dbo.[Insert_Table] FROM")
        query.Append(" 'C:\Insert_Table.csv' ")
        query.Append("With (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')")

        Return query.ToString

    End Function

Be carfully here though since tablename and csv name has to be identical as well as the column count in the csv has to be the same as in the pre-defined table.

Answer 8

    private void GetDataTabletFromCSVFile(string fileName)
    {
        DataTable dt = new DataTable();
        //dt.TableName = fileName;

        try
        {
            using (TextFieldParser csvReader = new TextFieldParser(fileName))
            {
                csvReader.SetDelimiters(new string[] { "," });
                csvReader.HasFieldsEnclosedInQuotes = true;
                string[] colFields = csvReader.ReadFields();
                //foreach (string column in colFields)
                //{
                //    DataColumn datecolumn = new DataColumn(column);
                //    datecolumn.AllowDBNull = true;
                //    dt.Columns.Add(datecolumn);
                //}
                dt.Columns.AddRange(new DataColumn[8] {
                    new DataColumn("Symbol", typeof(string)),
                new DataColumn("ISIN", typeof(string)),
                new DataColumn("Company", typeof(string)),
                new DataColumn("FirstListingDate", typeof(string)),
                new DataColumn("FaceValue", typeof(string)),
                new DataColumn("PaidUpValue", typeof(string)),
                new DataColumn("MarketLot",typeof(string)),
                new DataColumn("industry",typeof(string))
                });
                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;
                        }
                    }
                    dt.Rows.Add(fieldData);
                }
                var builder = new ConfigurationBuilder()
                    .SetBasePath(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location))
                    .AddJsonFile("appsettings.json");

                var configuration = builder.Build();
                string DBconnection = configuration.GetSection("ConnectionString").Value;
                using (SqlConnection dbConnection = new SqlConnection(DBconnection))
                {
                    dbConnection.Open();
                    using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                    {
                        s.DestinationTableName = "Static.dbo.Securitiesinfo";
                        foreach (var column in dt.Columns)
                            s.ColumnMappings.Add(column.ToString(), column.ToString());
                        s.WriteToServer(dt);
                    }
                }

            }
        }
        catch (Exception ex)
        {
            var x = ex;
        }

    }