none
SSIS reads nvarchar values as Null when Excel column includes decimal and string values

    Question

  • I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.
    I have problem with importing CustomerSales column.
    CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.
    So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.

    However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

    CREATE TABLE [dbo].[Import_CustomerSales](
     [CustomerId] [nvarchar](50) NULL,
     [CustomeName] [nvarchar](50) NULL,
     [CustomerSales] [nvarchar](50) NULL
    ) ON [PRIMARY]


    Kenny_I

    Monday, December 09, 2013 2:54 PM

Answers

  • You can either change the datatype in the Excel source (advanced editor) or

    You can replace the nulls with text using an expression.

    It happens because a column may be defined as only one datatype, not a variable, and it got the numeric (based on built-in row sampling of the Excel driver).


    Arthur My Blog

    • Marked as answer by Kenny_I Tuesday, December 10, 2013 2:57 PM
    Monday, December 09, 2013 3:32 PM
    Moderator

All replies

  • You can either change the datatype in the Excel source (advanced editor) or

    You can replace the nulls with text using an expression.

    It happens because a column may be defined as only one datatype, not a variable, and it got the numeric (based on built-in row sampling of the Excel driver).


    Arthur My Blog

    • Marked as answer by Kenny_I Tuesday, December 10, 2013 2:57 PM
    Monday, December 09, 2013 3:32 PM
    Moderator
  • Hi Kenny_I,

    From the “Missing values” item under the “Usage Considerations” section of the document Excel Source, we can see that:

    The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type.

    So, regarding this issue, this behavior is by design. To address this issue, we add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

    For .xlsx file, the connection string of the Excel Connection Manager is like:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Test.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1";

    For .xls file, the connection string of the Excel Connection Manager is like:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Test.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

    After that, you may encounter a data truncation issue due to the length of the source column and the destination column. To avoid this issue, you can open the Advanced properties page of the Excel connection manager, and modify the data Length of the CustomerSales output column to be 50 or a smaller integer value. You can also use a Data Conversion transform to change the length of the CustomerSales column outputted by the Excel source control.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Tuesday, December 10, 2013 4:20 PM
    Moderator
  • this not explains why the numbers are turned into nulls, 

    if the default datatype adopted is nvarchar then ALL kind of content should be read as text!   (-__-) 

    Thursday, January 16, 2014 3:34 PM