SqlBulkCopy ColumnMapping error CSV to DataTable to SQL Server Table

c# csv-import datatable sqlbulkcopy sql-server

Question

I am trying to bulk insert a CSV file into a SQL Server database.

The process is .CSV file to DataTable to SqlBulkCopy to SQL Server.

When I run this I get this error:

The given ColumnMapping does not match up with any column in the source or destination

When I directly import the CSV into SQL Server via Management Studio it works! So I think the problem is my data table conversion?

Here is my code to go from .CSV to DataTable:

public DataTable CsvFileToDataTable(string filePath)
{
    var csvData = new DataTable("Table1");
    using (var csvReader = new TextFieldParser(filePath))
    {
        csvReader.SetDelimiters(new string[] { "," });
        csvReader.HasFieldsEnclosedInQuotes = true;
        var readFields = csvReader.ReadFields();

        foreach (var dataColumn in readFields.Select(column => new DataColumn(column) {AllowDBNull = true }))
        {
            csvData.Columns.Add(dataColumn);
        }
        while (!csvReader.EndOfData)
        {
            var data = csvReader.ReadFields();
            for (var i = 0; i < data.Length; i++)
            {
                if (data[i] == "")
                {
                    data[i] = null;
                }
            }
            csvData.Rows.Add(data);
        }
    }

    return csvData;
}

And here is the code for the bulk copy insert:

public void InsertData(DataTable table)
{
    using (var transactionScope = new TransactionScope())
    {
        using (var sqlConnection = new SqlConnection(this.ConnectionString))
        {
            sqlConnection.Open();

            using (var sqlBulkCopy = new SqlBulkCopy(sqlConnection))
            {
                sqlBulkCopy.DestinationTableName = table.TableName;

                foreach (var column in table.Columns)
                {
                    sqlBulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());
                }

                sqlBulkCopy.WriteToServer(table);
            }

            transactionScope.Complete();
        }
    }
}

Does anyone have any suggestions?

Thanks

1
1
1/6/2015 9:48:46 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

Turns out If I tweked each method 

foreach (var dataColumn in readFields.Select(column => new DataColumn(columntypeof(string)) { AllowDBNull = true, DefaultValue = string.Empty }))
{
    csvData.Columns.Add(dataColumn);
}

and in the second Method I changed it to use an index rather than column name

for (var count = 0; count < table.Columns.Count; count++)
{
    sqlBulkCopy.ColumnMappings.Add(count, count);
}
2
1/6/2015 10:24:58 PM

Popular Answer

I was able to use these code samples to create a working solution that reads a csv, checks if there is data, and if there is clean out the data and import all the csv data to the table:

private static void ProcessFile(string FilePath, string TableName)
{
    var dt = GetDataTable(FilePath, TableName);
    if (dt == null)
    {
        return;
    }
    if (dt.Rows.Count == 0)
    {
        AuditLog.AddInfo("No rows imported after reading file " + FilePath);
        return;
    }
    ClearData(TableName);
    InsertData(dt);
}
private static DataTable GetDataTable(string FilePath, string TableName)
{
    var dt = new DataTable(TableName);
    using (var csvReader = new TextFieldParser(FilePath))
    {
        csvReader.SetDelimiters(new string[] { "," });
        csvReader.HasFieldsEnclosedInQuotes = true;
        var readFields = csvReader.ReadFields();
        if (readFields == null)
        {
            AuditLog.AddInfo("Could not read header fields for file " + FilePath);
            return null;
        }
        foreach (var dataColumn in readFields.Select(column => new DataColumn(column, typeof(string)) { AllowDBNull = true, DefaultValue = string.Empty }))
        {
            dt.Columns.Add(dataColumn);
        }
        while (!csvReader.EndOfData)
        {
            var data = csvReader.ReadFields();
            if (data == null)
            {
                AuditLog.AddInfo(string.Format("Could not read fields on line {0} for file {1}", csvReader.LineNumber, FilePath));
                continue;
            }
            var dr = dt.NewRow();
            for (var i = 0; i < data.Length; i++)
            {
                if (!string.IsNullOrEmpty(data[i]))
                {
                    dr[i] = data[i];
                }
            }
            dt.Rows.Add(dr);
        }
    }
    return dt;
}
private static void ClearData(string TableName)
{
    SqlHelper.ExecuteNonQuery(ConfigurationUtil.ConnectionString, CommandType.Text, "TRUNCATE TABLE " + TableName);
}
private static void InsertData(DataTable table)
{
    using (var sqlBulkCopy = new SqlBulkCopy(ConfigurationUtil.ConnectionString))
    {
        sqlBulkCopy.DestinationTableName = table.TableName;
        for (var count = 0; count < table.Columns.Count; count++)
        {
            sqlBulkCopy.ColumnMappings.Add(count, count);
        }
        sqlBulkCopy.WriteToServer(table);
    }
}
Bolo

Related Questions

How to put a DataTable into a SQL Server Table with an Existing Key

Look up IDENTITY INSERT. You turn it on. Update the table, then turn it back off....SET IDENTITY_INSERT table ON ...Please note that you can only have it on for one table at a time. If you turn it on for a different table, it turns off on the last table....If you insert a value that is higher than the largest existing value, it will reseed itself t...
database datatable sqlbulkcopy sql-server vb.net
asked by Marcus Adams

How to Update a SQL Server Table Using Data from a Different Source (DataTable)

Use the below code:...C# Side code for reading data from DataTable and preparing the XML data:...DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder(); sb.Append("<R>"); for (int i = 0; i < dt.Rows.Count; i++) { sb.Append("<C><ID>" + dt.Rows[0].ToString() + "</ID>"); sb.Append("<N>" + dt.Rows[1].ToString() + "</N>"); s...
asp.net c# datatable sqlbulkcopy sql-server-2005
asked by Thit Lwin Oo

Automatically define the DataTable in C# from the schema of SQL server tables?

If you are looking for a quick way to load flat files into SQL, you could use BULK INSERT. Just specify a delimiter and as long as the columns in your files are laid out in the same order as the database table, everything is automatically mapped out. You do need some extra permission to execute bulk inserts, but from my experience, it's the quick...
c# datatable sqlbulkcopy sql-server
asked by CoderMarkus

Mapping columns in a DataTable to a SQL table with SqlBulkCopy

You probably need some thing like ... public void BatchBulkCopy(DataTable dataTable, string DestinationTbl, int batchSize) { // Get the DataTable DataTable dtInsertRows = dataTable; using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = DestinationTbl; ...
c# datatable sql sqlbulkcopy
asked by StackTrace

Insert xml data into SQL server via SqlBulkCopy with powershell (casting error)

I often use string data types for DataColumns while keeping my SQL Server table an XML datatype. This works fine for me....Instead of converting your xml file to xml just use (cat C:\test\4625_forForums.xml -raw) And define your [System.Xml.XmlNode] as [System.String]
.net powershell sqlbulkcopy sql-server xml
asked by Chad Miller

Getting an exception writing a boolean to sql using a datatable and SqlBulkCopy

I've fixed it, turns out it was a mapping problem. For some reason, all 20 other tables mapped completely fine, but this table didn't map correctly until I added the following:...bulk.ColumnMappings.Add("Ex", "Ex");
c# datatable sqlbulkcopy sql-server
asked by NibblyPig

SqlBulkCopy ColumnMapping Error

I would add some validation to this to check what columns your source and destination tables have in common....This essentially queries the system views (I have assumed SQL Server but this will be easily adaptable for other DBMS), to get the column names in the destination table (excluding identity columns), iterates over these and if there is a ma...
c# datatable sql sqlbulkcopy
asked by GarethD

Unable to compare SQL and datatable and copy new entries to sql table

Please put the appropriate spaces between the SQL statements of your query...cmdString = "INSERT INTO Daten ([Zyklus ID]) " & _ "SELECT [Zyklus ID] " & _ "FROM Test " & _ "LEFT JOIN Daten ON (Test.[Zyklus ID] = Daten.[Zyklus ID]) " & _ "WHERE (Daten.[Zyklus ID] IS NULL);"
datatable sql sqlbulkcopy sql-server vb.net
asked by Steve

SqlBulkCopy into table that Default column values fails when source DataTable row has DBNull.Value

For part 1, "field that is NOT NULL with a DEFAULT", you should not be sending the field in the first place. It should not be mapped. There is no need to change that field to accept NULLs just for this. ...For part 2, "field that is NULL with a DEFAULT", that ...will... work to get the default value when passing in DbNull.Value, as long as you don'...
c# default-constraint null sqlbulkcopy sql-server
asked by Solomon Rutzky

SqlBulkCopy Wcf Rest Moving Data between 2 sql server tables

Based on the info you gave, your solution would be something like the sample code I inserted. Pointers for the SQLBulkCopy: ...BCopyTutorial1..., ...BCopyTutorial2... SQLBulkCopy with transaction scope examples: ...TrasactionBulkCopy...My version is a simplified version of these, based on your question....Interface:...[ServiceContract] public inter...
c# rest sqlbulkcopy wcf
asked by DDan

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow