Error converting data type nvarchar to numeric when Import Excel to Sql Server Database

Question

I am importing excel file into Sql Server database using a Console application script.

    private static void ImportToSql(string ssqlconnectionstring, string query)
    {
        string query = "MERGE Inventory AS target USING (select LocalSKU,ItemName, QOH,ISNULL(Price,0.00),Discontinued,Integer2,Integer3 from @source)  as source ON (source.LocalSKU = target.LocalSKU) WHEN MATCHED THEN UPDATE SET ItemName=source.ItemName,Price=source.Price,Discontinued=source.Discontinued,Integer2=source.Integer2,Integer3=source.QOH;";
        string excelfilepath = Environment.CurrentDirectory + @"\Sheet1.csv";
        // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
        // string myexceldataquery = "select LocalSKU,ItemName,QOH,Price,Discontinued,Barcode,Integer2,Integer3,SalePrice,SaleOn,Price2 from [sheet1$]";
        string myexceldataquery = "select LocalSKU,ItemName, QOH,Price,Discontinued,Integer2,Integer3 from [sheet1$]";

        try
        {

            // string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";Extended Properties=Excel 12.0;";

            string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + excelfilepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2; ImportMixedTypes=Text;\"";
            //string ssqlconnectionstring = "Data Source=User-PC\\Dell;Trusted_Connection=True;DATABASE=TestStore;CONNECTION RESET=FALSE";

            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);

            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);

            oledbconn.Open();

            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlCommand sqlcmd = new SqlCommand(@query, sqlconn);

            SqlParameter param;
            param = sqlcmd.Parameters.AddWithValue("@source", dr);
            param.SqlDbType = SqlDbType.Structured;

            param.TypeName = "dbo.DTTOW";
            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();

            oledbconn.Close();

            Console.WriteLine(".xlsx file imported succssessfully into database. /r /n ");

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

This code works when I select only two columns from excel sheet , LocalSKU and QOH

When I select few more columns , I get this error. "Error converting data type nvarchar to numeric."

I am using User defined datatypes :

CREATE TYPE [dbo].[DTTOW] AS TABLE(
  [LocalSKU] [varchar](200) NOT NULL,
  [ItemName] [varchar](200) NULL,
  [QOH] [int] NULL,
  [Price] [decimal](19, 4) NULL,
  [Discontinued] [bit] NULL,
  [Integer2] [int] NULL,
  [Integer3] [int] NULL

)

Do you have any, non-numeric values in excel Price column? Anything with spaces, letters, null, or empty string? — Mike Hixson
– Mike Hixson, Commented Jun 16, 2014 at 20:16
yes.. got your point.. can you draw me how to handle those..? — user3590485
– user3590485, Commented Jun 16, 2014 at 20:18

Wanted by FBI.... Sexually LOL · Accepted Answer · 2014-06-16 20:25:11Z

1

From the looks of the SQL DDL for the structure there is messed up price somewhere in the excel file. Can you run a macro or script to see if all the cells in the price column are actually convertible? You can ise =ISNUMBER() function. Also easy and dirty way - try to edit =SUM() at the bottom of the Price column. I wonder if some "smarty pants" stuck in a '-' or '$' character into one of the Price column's cells.:) I'm afraid that the default type for any Excel cell is nvarachar in the OLE-DB perception.

Personally, I would have used Excel inter-op to suck the information in, rather than fight implicit conversion errors like yours. It is a little bit slower, but surely does the job without you wondering what could have went wrong inside of a "black box" that is OLE-DB for Excel.

edited Jun 16, 2014 at 20:25

answered Jun 16, 2014 at 20:18

Wanted by FBI.... Sexually LOL

644 bronze badges

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

Comments

Mike Hixson · Accepted Answer · 2014-06-16 20:42:56Z

1

You can do some clean up of the numeric types in your query that selects from excel. For the Price field you could change the query to the following to guarantee the column is always numeric, by replacing non-numeric values with 0.

select iif(isnumeric(Price), Price, 0) as Price from [sheet1$]

answered Jun 16, 2014 at 20:42

Mike Hixson

5,2171 gold badge21 silver badges24 bronze badges

Collectives™ on Stack Overflow

Error converting data type nvarchar to numeric when Import Excel to Sql Server Database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related