locked
How To avoid NULL in OLEDB Table when import the Excel Data ? RRS feed

  • Question

  • InSSIS-a data flow,Excel Source (.xlsx) --> OLEDB Destination([dbo].INPUT)

    Excel had empty values in some cells

    when importing data from Excel to OLEDB

    then OLEDB([dbo].INPUT) had NULL values   (where Excel had empty value)

    Destination Table : ([dbo].INPUT)) creating column structure in like

       i.e.,  Create table INPUT (ID int,

                               Name  nvarchar(255) NULL,....)

    So How To avoid NULL in OLEDB Table when import the Excel Data ?

    It means I want empty value where NULL  in [dbo].INPUT table.

    Please do help.

    Thursday, June 26, 2014 8:07 AM

Answers

  • Excel does not have the notion of NULL being not exactly a database.

    Arthur My Blog

    • Proposed as answer by ArthurZ Thursday, July 3, 2014 3:43 PM
    • Marked as answer by Elvis Long Tuesday, July 15, 2014 1:55 AM
    Thursday, June 26, 2014 2:31 PM

All replies

  • Use a derived column with an SSIS Expression like

    ISNULL([Column1])?" " : [Column1]


    Arthur My Blog

    Thursday, June 26, 2014 1:52 PM
  • I had 40 columns

    So  Its too , Hence any other Solution ?

    Thursday, June 26, 2014 2:07 PM
  • Then it is just a few more keystrokes, you can automate to a degree with an editor macro

    e.g. you copy paste your columns in a column and then apply the above expression to all (by scripting a macro).

    Then you copy and paste each into the Derived Column remembering to set the add as column or perhaps replace column.


    Arthur My Blog

    Thursday, June 26, 2014 2:17 PM
  • Its Ok, Again from Export to xlsx from OLEDB  had no NULL values .

    but why DBO table is Facing problem of NULL ?

    Thursday, June 26, 2014 2:22 PM
  • Excel does not have the notion of NULL being not exactly a database.

    Arthur My Blog

    • Proposed as answer by ArthurZ Thursday, July 3, 2014 3:43 PM
    • Marked as answer by Elvis Long Tuesday, July 15, 2014 1:55 AM
    Thursday, June 26, 2014 2:31 PM