The given value of type String from the data source cannot be converted to type float of the specified target column

.net asp.net c# sql sqlbulkcopy

Question

I was wondering if someone could help. I am attempting to read a CSV file in C# and import it's data into a table i have created in SQL 2008.

For some reason i keep getting the following error:

"The given value of type String from the data source cannot be converted to type float of the specified target column."

Complete Stack Trace:

System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
System.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
System.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
RMBEventReportingSystemPOC.Admin.ProcessFile(String strFilename) in \Admin.aspx.cs:line 159

Please find below the code i am using. Please let me know where i have gone wrong :

StatusLabel.Text = "File Process status: File process started!";
try
{

    //var fileName = string.Format(strFilename, Directory.GetCurrentDirectory());

    SqlConnection con = new SqlConnection(@"**ConnectionString**");

    string filepath = Server.MapPath("~/uploads/") + strFilename;
    StreamReader sr = new StreamReader(filepath);
    string line = sr.ReadLine();
    string[] value = line.Split(';');
    DataTable dt = new DataTable();
    DataRow row;
    foreach (string dc in value)
    {
        if (dc == "Month" || dc == "Year" || dc == "Reply")
            dt.Columns.Add(new DataColumn(dc, typeof(int)));
        else if (dc == "CostPerHead" || dc == "TotalCost")
            dt.Columns.Add(new DataColumn(dc, typeof(float)));
        else
            dt.Columns.Add(new DataColumn(dc));
    }

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


            //fix up default values                        
            value[1] = value[1] == "" ? "0" : value[1].ToString().Trim();
            value[2] = value[2] == "" ? "0" : value[2].ToString().Trim();
            value[3] = value[3] == "" ? "0.00" : string.Format("{0:0.00}",value[3].ToString());
            value[4] = value[4] == "" ? "0.00" : string.Format("{0:0.00}",value[4].ToString());
            value[7] = value[7] == "" ? "0" : value[7].ToString().Trim();

            row.ItemArray = value;

            dt.Rows.Add(row);
        }
    }

    ////fix up default values

    for (int i = 0; i < dt.Rows.Count; i++)
    {

        dt.Rows[i][1] = int.Parse(dt.Rows[i][1].ToString().Trim());
        dt.Rows[i][2] = int.Parse(dt.Rows[i][2].ToString().Trim());
        dt.Rows[i][3] = Math.Round(float.Parse(dt.Rows[i][3].ToString() + ".00"), 2);
        dt.Rows[i][4] = Math.Round(float.Parse(dt.Rows[i][4].ToString() + ".00"), 2);

        dt.Rows[i][7] = dt.Rows[i][7].ToString() == "" ? 0 : int.Parse(dt.Rows[i]["Reply"].ToString().Trim());
    }

    SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
    bc.DestinationTableName = "Guestlist";
    bc.BatchSize = dt.Rows.Count;
    // add column mappings if necessary               
    con.Open();
    bc.WriteToServer(dt);
    bc.Close();
    con.Close();


    StatusLabel.Text = "File Process status: File process completed! " + dt.Rows.Count.ToString() + " records imported.";
    StatusLabel.CssClass = "success";
}
catch (Exception exx)
{

    StatusLabel.Text = "File Process status: File process failed!: " + exx.Message;
    StatusLabel.CssClass = "error";
}

any pointers would be greatly appreciated.

Accepted Answer

One or more of your columns are defined as float type in the database.

However, in the CSV file the column value is a string or contains a value that cannot be converted to a float type.

The columns you are converting at

dt.Rows[i][3] = Math.Round(float.Parse(dt.Rows[i][3].ToString() + ".00"), 2);
dt.Rows[i][4] = Math.Round(float.Parse(dt.Rows[i][4].ToString() + ".00"), 2);

may contain a floating point number already. By appending ".00" you are now making it invalid and hence the conversion fails.

Depending on the data, either remove the conversion or make conditional use of it.


Popular Answer

SqlBulkCopy.WriteToServer(DataTable) fails with confusing messages if the column order of the DataTable differs from the column order of the table definition in your database (when this causes a type or length incompatibility). Apparently the WriteToServer method does not map column names.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why