Post reply

Can't read the formula =NA() from Excel into SSIS

  • December 22, 2017 at 8:33 am

    #342171

    Hi,

    I got an Excel sheet where I am trying to read a column where nearly all cells have the formula =NA(), representing NULL. The rest of the cells are decimal. That mean the value in the cell is/become #N/A, again representing NULL

    If I set the data type in the recieving table to decimal(27,20) SSIS says it can't convert #NA to decimail, because SSIS thinks it is a text, and not NULL. If I set the data type to nvarchar(255), SSIS read #NA as a text, an not as NULL.

    What to do?

  • December 22, 2017 at 9:00 am

    #1973272

    Well the best option is to fix the excel sheet so those fields don't show that and either show 0 or just blank if that's what you want.

    Otherwise you could read the data in as text and parse it in SSIS.

  • December 22, 2017 at 9:50 am

    #1973281

    Your problem is that =NA() doesn't represent NULL. Even according to the documentation, it returns an error; which is represented by the text (important word there 😉 ) #N/A. To import a NULL, simply leave the cell blank.

    Also, note that SSIS isn't doing any reading, it's the ACE drivers that are doing so (or JET if xls). But they still aren't the problem, the problem is your spreadsheet.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply