Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: Handling null values using ADO 2.7 to import excel

    Hey guys, I'm running into a roadblock with an ADO issue.

    'm having issues with null values in excel when constructing an ADO 2.7 recordset from another spreadsheet. Basically if the first few values in a given column are null, the ENTIRE column returns null.

    Initiall I assumed this was due to ado's attempt to discern the datatype from the first 8 rows of data. To attempt to circumvent the issue, I set IMEX=1 and HDR=Yes, but to no avail. For record, all data in the column is numeric.

    I also tried formatting the entire column as text to see if it would eliminate any mixed data issues, no dice there either. As a last ditch effort I attempted to convert null to the string "blank" during the open method of the recordset. It looks like the datatype is "guessed" before the query is actually run though, as this had no effect other then returning "blank" for every record instead of null.

    I'm thinking the issue is specific to the way ado/excel handles null. I haven't found any resource addressing what ado will do if the entire first 8 entries in a given column are null.

    Hopefully someone can shed some light on this for me?

    TIA folks!

    -Teddy
    Last edited by Teddy; 01-04-05 at 11:57. Reason: Forgot to describe actual problem
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Cheat. Construct the query so that the first few values in that column aren't NULL.

    -PatP

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's my current plan. Until I can figure this out, I'm writing a procedure that first opens the target source and preps it for import. That's awfully slow though. I suppose I could evaluate just the first 8 for null and throw a numeric 0 in there. That would be enough to force the datatype I think...


    *update:

    I ended up going a little of both here... I created a small "PrepSource()" sub that opens the source and checks the first few records for null and inserts 0 if they are. Then in the open method for the recordset, I use an iif() statement to set the remaining nulls to zero. I think for now that's the best compromise of consitancy and speed I'm going to get.
    Last edited by Teddy; 01-04-05 at 12:50.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Pat,

    didn't you point this out as a known M$ problem....

    Ya gotta love M$...bullet proof
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Pat,

    didn't you point this out as a known M$ problem....

    Ya gotta love M$...bullet proof
    As always heh.

    There's a good deal of documentation on this sort of error with mixed datatypes. Unfortunately there was a lack of information regarding expected behavior if the ENTIRE sample is null. Workarounds ahoy!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •