How to Upload CSV file to sql server table using C#?

Question

I got the error message below when I tried to upload a csv file to a SQL Server table using C# (csv file has no header). Error Message : "A column named ' ' already belongs to this DataTable"

I tried to find some solutions somewhere on the web but I'm really stuck with it. My code :

SqlConnection con = new SqlConnection(@"server=.;Initial Catalog=myDtabase;Integrated Security=SSPI;");

            string filepath = @"c:\\my_CSV_file.csv";

            StreamReader sr = new StreamReader(filepath);

            string line = sr.ReadLine();
            string[] value = line.Split(',');
            DataTable dt = new DataTable();
            DataRow row;

            foreach (string dc in value)
            {
                dt.Columns.Add(new DataColumn(dc));
            }

            while (!sr.EndOfStream)
            {
                value = sr.ReadLine().Split(',');
                if (value.Length == dt.Columns.Count)
                {
                    row = dt.NewRow();
                    row.ItemArray = value;
                    dt.Rows.Add(row);
                }
            }

            SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
            bc.DestinationTableName = "my_SQLServer_Table";
            bc.BatchSize = dt.Rows.Count;
            con.Open();
            bc.WriteToServer(dt);
            bc.Close();
            con.Close();

Have you stepped through your code using a debugger to see if you adding the same column more than once? — Ryan Fitzpatrick, Dec 08 '15 at 21:23
show us some sample data from your CSV file. The first few rows perhaps. — Mike Nakis, Dec 08 '15 at 21:33
@ayoub, take a look at one of my old post.. http://stackoverflow.com/questions/24088989/how-to-read-csv-file-splitting-by-commas-except-if-its-part-of-a-field also there is a much easier way to do bulk inserts by converting the DataTable to XML and doing the insert on the database end — MethodMan, Dec 08 '15 at 21:48
Thank you for you quick response !! MethodMan my fields are not enclosed by " " MikeNakis I'll Edit my post and I show some records. MethodMan I'll look at at your post. Thank you all ! I'll let you know if it works — ayoub, Dec 08 '15 at 22:08

score 2 · Accepted Answer · answered Dec 09 '15 at 17:46

I think this link will help you get this done.

http://forums.asp.net/t/1695615.aspx

As usual, there is more than one way to skin a cat. So, if yo don't like the solution listed above, try this script, which I know will work for you.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string server = "EXCEL-PC\\EXCELDEVELOPER";
            string database = "AdventureWorksLT2012";
            string SQLServerConnectionString = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, database);


            string CSVpath = @"C:\Users\Ryan\Documents\Visual Studio 2010\Projects\Bulk Copy from CSV to SQL Server Table\WindowsFormsApplication1\bin"; // CSV file Path
            string CSVFileConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};;Extended Properties=\"text;HDR=Yes;FMT=Delimited\";", CSVpath);

            var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
            string File_Name = string.Empty;

            foreach (var file in AllFiles)
            {
                try
                {
                    DataTable dt = new DataTable();
                    using (OleDbConnection con = new OleDbConnection(CSVFileConnectionString))
                    {
                        con.Open();
                        var csvQuery = string.Format("select * from [{0}]", file.Name);
                        using (OleDbDataAdapter da = new OleDbDataAdapter(csvQuery, con))
                        {
                            da.Fill(dt);
                        }
                    }

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLServerConnectionString))
                    {
                        bulkCopy.ColumnMappings.Add(0, "MyGroup");
                        bulkCopy.ColumnMappings.Add(1, "ID");
                        bulkCopy.ColumnMappings.Add(2, "Name");
                        bulkCopy.ColumnMappings.Add(3, "Address");
                        bulkCopy.ColumnMappings.Add(4, "Country");
                        bulkCopy.DestinationTableName = "AllEmployees";
                        bulkCopy.BatchSize = 0;
                        bulkCopy.WriteToServer(dt);
                        bulkCopy.Close();
                    }

                }
                catch(Exception ex)
                     {
                         MessageBox.Show(ex.Message, "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                     }
            }
        }
    }
}

score 0 · Answer 2 · answered May 04 '22 at 22:14

The CsvHelper NuGet library has an implementation for IDataReader which you can pass into the BulkCopy WriteToServer method. This makes for really simple code and allows you to customize the data import.

using CsvHelper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Globalization;
using System.IO;

public int ReplaceTableWithFile(string table, FileInfo csvFile)
{
    using var fileReader = new StreamReader(csvFile.OpenRead());
    using var csv = new CsvReader(fileReader, CultureInfo.InvariantCulture);
    using var csvDataReader = new CsvDataReader(csv);

    var connection = GetDbConnection();

    using var command = new SqlBulkCopy(connection);
    command.EnableStreaming = true;
    command.DestinationTableName = table;
    command.WriteToServer(csvDataReader);
    return command.RowsCopied;
}

CsvDataReader from CsvHelper NuGet package

How to Upload CSV file to sql server table using C#?

2 Answers2