Import data from Excel to SQL Server, cannot save as decimal data type

Question

I have a utility that imports Excel spreadsheets to SQL Server. My utility broke recently when I had to convert some fields from nvarchar to decimal.

I use c# code to call a stored procedure. My c# code:

DataTable dtChem = new DataTable();
ada.Fill(dtChem);  // Fills datatable with data from Excel
cmd.Parameters.Clear();
cmd.CommandText = "dbo.insertDataChem";
cmd.Parameters.AddWithValue("@chemResults", dtChem);
cmd.Parameters.AddWithValue("@eddName", strFileName);                                    
cmd.ExecuteNonQuery();

My stored procedure:

create PROCEDURE [dbo].[insertDataChem]
    @chemResults as dbo.udtableDataChem READONLY
    , @eddName nvarchar(100) = null
AS

BEGIN 
    INSERT INTO MY_TABLE
    SELECT cr.SampleDate, cr.*,  @eddName, CURRENT_TIMESTAMP FROM @chemResults cr
END

And I use the following user-defined table type to store my data (I've removed a lot of fields for brevity):

CREATE TYPE [dbo].[udtableDataChem] AS TABLE(

    ...

    [MDL] [decimal](30, 15) NULL,   // data type changed
    [RL] [decimal](30, 15) NULL,    // data type changed
    [DilutionFactor] [nvarchar](200) NULL,  // original data type
    [ExpectedValue] [nvarchar](200) NULL,  // original data type

    ...

)

In my data table type, above, all of my types used to be nvarchar. When I changed the data types in the destination table to decimal(30, 15), I thought I could just change the data types in the corresponding user-defined table type to decimal as well, and that would force the data to be compatible. But my c# error trapping is still telling me "Error converting data type nvarchar to numeric.".

Any help would be appreciated.

Troels Larsen · Accepted Answer · 2019-07-19 17:43:31Z

2

To convert from nvarchar (string), you need to convert it, either in C# by doing double.Parse. If you want to keep it in your query, do:

select convert(decimal(4,2), '12.32')

...where you'd replace '12.32' with your column.

Be wary of how your decimals points are stored also. You may need to replace you local decimal point with the invariant one (replace ',' with '.').

If you want to change data types on insert, you can also do the same:

INSERT INTO CEDEN_ChemResults  (SampleData, EddName, InsertedAtUtc, NumericColumnA)
SELECT cr.SampleDate, @eddName, GETUTCDATE(), CONVERT(decimal(30,15), cr.OriginalVarcharColumn) FROM @chemResults cr

By passing the column names, you don't have to rely on columns having the same names.

edited Jul 19, 2019 at 17:43

answered Jul 19, 2019 at 17:35

Troels Larsen

4,6813 gold badges41 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

buckshot Over a year ago

Troels, thank you for your reply. The problem with your suggestion is that, as you can see, I've created a table data type and use it as a single variable. At no point in my code do I actually touch the individual fields; the data is passed from Excel into sql as a single object.

Troels Larsen Over a year ago

@buckshot: You can still do the conversion in your stored procedure. I've expanded my example to help explain. Another alternative is to copy the values to a new data table in C#. That may make sense depending on amount of data and your preferences.

stuartd Over a year ago

"At no point in my code do I actually touch the individual fields; the data is passed from Excel into sql as a single object." - so presumably Excel is filling the table with string values, rather than decimals? Have you checked the column type?

buckshot Over a year ago

Troels, I thank you again. I think I'll go with your second suggestion and use c# to format the data table. A little more work, but I know how do do it.

buckshot Over a year ago

Stuartd, yes, I can manually change the field types in Excel, but this is a high-throughput operation. That would not be a practical solution. Thanks for your reply, though.

|

Collectives™ on Stack Overflow

Import data from Excel to SQL Server, cannot save as decimal data type

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related