none
Excel Data coming back as NULL RRS feed

  • Question

  • I have to validate that the columns in an excel spread sheet exist and are of a certain value. I'm trying to do it by avoiding a scrip task. I thought I was being cleaver by selecting the top row and dumping it to a recordset destination but apparenlty I'm not as clever as I thought. I pull the data by SQL command in the Excel Source Editor.

    SELECT        TOP 1 F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14
    FROM            [Data$]

    The problem is some of the columns are coming back as NULL for no reason. I'm looking right at the column in excel and it's totally populated with a value. This ONLY happens with the first row. No rows after that exibit this issue. What could be causing this?

    Monday, February 25, 2013 6:15 PM

Answers

All replies

  • Even without TOP1 pulling back the whole data set still winds up with NULLs in the first record.
    Monday, February 25, 2013 6:24 PM
  • The problem is how the Jet driver assigns data types for the data in your columns.  For those columns that appear NULL, I'm going to make a guess - you tell me if I'm right.

    Those columns can have alphanumeric values in them - for example your sheet may have a few rows that have letters and numbers - but it also has a couple rows with just numbers in them.  Am I right?  For my guess to be right, you'll have to tell me that in the first eight rows of those columns, there are at least four numbers...


    Todd McDermid's Blog Talk to me now on

    Monday, February 25, 2013 7:22 PM
    Moderator
  • You sir are correct. I attempted to rectify with a data conversion. No dice.
    Monday, February 25, 2013 7:36 PM
  • Yes As Todd has mentioned, the excel driver samples the first 8 records and decides the column metadata type. I know some people do try to hack by updating the registry value for this entry. But i recommend you the official support article that MS wants us to implement.

    Add IMEX=1; to the connection string information

    Support Excel values Set as Null


    Regards, Dinesh

    • Marked as answer by falcon00 Monday, February 25, 2013 8:37 PM
    Monday, February 25, 2013 8:21 PM
  • See these two blog post when you have null values:
    http://blogs.lessthandot.com/index.php/DataMgmt/ssis-1/what-s-the-deal-with
    http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by falcon00 Monday, February 25, 2013 8:39 PM
    Monday, February 25, 2013 8:26 PM
    Moderator
  • IMEX=1 got it. Weird. I've tried that before and it didn't work.
    Monday, February 25, 2013 8:38 PM
  • It may or may not work for you.  IMEX=1 is NOT a "fix" for this problem.  The specific effect of this attribute is to tell the Jet driver to interpret the incoming information as strings when it sees different data types.  However, if you happen to have a spreadsheet that has all numbers in the first eight rows (at design time), then IMEX=1 won't help.  Jet will report that column as a numeric column, and at runtime, any alpha values will be imported as NULL.  So be warned...

    Todd McDermid's Blog Talk to me now on

    Tuesday, February 26, 2013 3:51 AM
    Moderator