SqlBulkCopy ColumnMapping error CSV to DataTable to SQL Server Table




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
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);
}

```
If I am not wrong, you are trying to read or loop through the rows from datatable. This error states that the datasource(DataTable) is not having any rows. To avoid this exception use this before you read or loop through datatable:  


```
if(dt.Rows.Count  > 0 ){
     //Do your stuff here.
}
```
  

  

  

--Amit For anyone having similar problems, I have solved my issue. It was several-fold.


* As Darren suggested and Paul backed up, the Max..Size properties in the configuration needed to be enlarged. The SvcTraceViewer utility helped in determining this, but it still does not always give the most helpful error messages.
* It also appears that when the Service Reference is updated on the client side, the configuration will sometimes not update properly (e.g. Changing config values on the server will not always properly update on the client. I had to go in and change the Max..Size properties multiple times on both the client and server sides in the course of my debugging)
* For a DataTable to be serializable, it needs to be given a name. The default constructor does not give the table a name, so:



```
return new DataTable();

```

will not be serializable, while:



```
return new DataTable("someName");

```

will name the table whatever is passed as the parameter. 


Note that a table can be given a name at any time by assigning a string to the `TableName` property of the DataTable.



```
var table = new DataTable();
table.TableName = "someName";

```


Hopefully that will help someone. Did you bind the listBox1, using DataSource property from DataTable?  

If so, you have to 1st get the item and value from listBox item and add it some other place (in your case to listBox2):



```
for(int i = 0; i < listBox1.SelectedItems.Count; i++)
{
     DataRowView view = listBox1.SelectedItem as DataRowView;
     string item = view["ColumnNameOfItem"].ToString(); //can be some name
     int value = Convert.ToInt32(view["ColumnNameOfValue"]);  //can be some id (number)
     listBox2.Items.Add(item); // it you want to add item (you can add value as well);
}
```

ColumnNameOfItem (or OfValue) are the names of fields in your dataTable (and same are in dataBase as well). Insteads of names you can use indexes (0 and 1, but without quotation marks).



Hope it helps.  

bye Dapper just wraps raw ADO.NET; raw ADO.NET doesn't offer a facility for this, therefore dapper does not. What you want is `SqlBulkCopy`. You could *also* use a table-valued-parameter, but this really feels like a `SqlBulkCopy` job.


In a pinch, you can *use* dapper here - `Execute` will unroll an `IEnumerable<T>` into a series of commands about `T` - but it will be lots of commands; and unless you explicitly enable async-pipelining, it will suffer from latency per-command (the pipelined mode avoids this, but it will still be n commands). But `SqlBulkCopy` will be *much* more efficient.


If the input data is an `IEnumerable<T>`, you might want to use `ObjectReader` from FastMember; for example:



```
IEnumerable<SomeType> data = ...
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(reader);
}

``` Your test is redundant, since you are already querying only for matching records. In this case, you will get no rows if there is no match, therefore you will never hit the else clause in the loop.  

  

Instead, it is enough to check if there are any matching records at all. The example below shows how (note that you can put a condition straight into Any() instead of specifying Where()).  

  

C#Copy Code

```
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
namespace CustomerMaster
{
    public partial class CustomerMaster : System.Web.UI.Page
    {
 
        private void AddData()
        {
            
            OperationsDataContext AddDb = new OperationsDataContext();
            CustomerMast cm = new CustomerMast();
            
                        
            cm.CustomerCode = txtCustCde.Text;
            cm.CustomerName = txtCustName.Text;
            cm.CustomerAddress = txtCustAdd.Text;
            cm.BrkrCode = txtBrkCode.Text;
            cm.BrokerName = txtBrkName.Text.
            cm.CrrCode = txtCrrCode.CarrierCode;
            cm.CarrierName =  txtCrrName.Text;
            cm.StnCode=sm.StationCode;
            cm.StationName = txtStnName.Text;
            cm.VatNo=txtVatNo.Text;
            cm.TinNo=txtTinNo.Text;
            cm.TMCO=txtTMCONo.Text;
            cm.PanNo = txtPAN.Text;
			
	    if(AddDb.CustomerMasts.Any(c=>c.CustomerCode == txtCustCde.Text))
            {
	        Response.Write("Error! CustomerCode Exits");
	    }
	    else
	    {
                AddDb.BrokerMasts.InsertOnSubmit(bm);
                AddDb.CarrierMasts.InsertOnSubmit(crm);
                AddDb.StationMasts.InsertOnSubmit(sm);
                AddDb.CustomerMasts.InsertOnSubmit(cm);

	        AddDb.SubmitChanges();
            }         
        }
 
        protected void btnAdd\_Click(object sender, EventArgs e)
        {
            AddData();
        }
    }
}
``` Lets say you want to send a table with a single column of GUIDs.


First we need to create a structure using [SqlMetaData](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmetadata(v=vs.110).aspx) which represents the schema of the table (columns).



```
var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

```

Next you create a list of records that match the schema using [SqlDataRecord](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqldatarecord(v=vs.110).aspx).



```
var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

```

Then create the [SqlParameter](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx):



```
var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds";
parameter.Value = table;

var parameters = new SqlParameter[1]
{
  parameter
};

```

Then simply call the stored procedure by using the [Database.SqlQuery](https://msdn.microsoft.com/en-us/library/gg696545(v=vs.113).aspx).



```
IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

```

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):



```
CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

```

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):



```
CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON

  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

``` Hi  

This is simple. First check whether you are able to connect Sql Server through SSMS or not.If you are able to connect through SSMS, then there may be a problem in connection string. If you are not able to connect through SSMS, then you had some problem with SQL Environment. A couple of notes to get started:



> 
> .apply(literal\_eval)
> 
> 
> 


Wouldn't this be better as `map`?



> 
> I then re-create the Dask DF:
> 
> 
> ddf = dd.from\_pandas(ddf\_out, npartitions=nCores)
> 
> 
> 


`ddf_out` was already a dask dataframe, I don't know why you had to do this.



> 
> The columns in result seem to match those in metadir.
> 
> 
> 


The value of `result.columns` is taken from the meta you provided, no computing happens until you ask for it (dask is lazy in most operations). Does the ValueError exception not provide further information?


Here is a full example



```
x = ({'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'DEBRA MEALY',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'CHAIR PERSON',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'},
 {'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'HELEN GORDON',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'VICE CHAIR',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'})
df = pd.DataFrame({'a': x})
d = dd.from_pandas(df, 1)
meta = pd.DataFrame(columns=['F9_07_PZ_COMP_DIRECT', 
       'F9_07_PZ_DIRTRSTKEY_NAME',
       'F9_07_PZ_COMP_OTHER', 'F9_07_PZ_COMP_RELATED', 'F9_07_PZ_TITLE',
       'F9_07_PZ_AVE_HOURS_WEEK', 'F9_07_PC_TRUSTEE_INDIVIDUAL'], dtype="O")
d.map_partitions(lambda df: df.a.apply(pd.Series), meta=meta).compute()

```

How did I know what `meta` to use? I applied the function to the pandas dataframe - you might use a small piece of the dataframe to do this.


Some additional notes:


* it is an anti-pattern to load the data with pandas, pass to dask workers and then gather the whole result back to a pandas (in memory) dataframe, you are very unlikely to see a speedup this way and may incur a lot of overhead. You are better off loading with something like `dd.read_csv`, and aggregating or writing with dask functions too. Only `compute()` on something that will be small or not return anything (because it involves writing the output). The official examples do not use from\_pandas.
* string and dict processing are python methods, and so hold the interpreter lock (GIL) of any python function: threads will not actually run in parallel. To get parallelism, you need to run in processes, which is most easily achieved using <https://docs.dask.org/en/latest/setup/single-distributed.html>
* the distributed scheduler also gives you access to the dashboard, which has a lot of useful information to diagnose how your system is running. You can also configure a lot about its behaviour, in case you have firewall rules you need to obey. You can provide an optional parameter to the scaffolding command to update only the table you target.



```
Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DirectoryNameOfYourModels -Tables employee -f

```

If you are using .net core cli then use.



```
dotnet ef dbcontext scaffold "Server=(localdb)\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o DirectoryNameOfYourModels -t employee -f

``` it's not clear what it is you want as an output. if it's a row vector, there's no concatenation involved, you just simply reshape your matrix:
a = [0 1 1 1;
 1 0 1 0;
 1 0 0 1]
 v = reshape(a, 1, []) %reshape a into one row. since matlab is column major, this is done column-wise