Post reply

Problem Importing NULLs in varchar field

  • December 8, 2022 at 3:04 pm

    #4123437

    hello,

    This is something I'm really struggling with and haven't found much when I search (maybe I'm not wording too well, or I get too wordy and results are all over the place)  I have an excel file that contains an alpha-numeric field.  The destination in my database is a varchar(25)  Problem is, when I import, any non-numeric field returns a NULL value even though it is a varchar field in the table.  I looked at the excel file, and even the top 10 rows or so are alpha and numeric.  I will say that the only thing that ends up working for me is if I go into the excel file and manually sort this field descending (putting the alpha cells first).  That works, but very impractical if I'll need to do this regularly (for the time being, I have made it NOT NULL so I don't accidentally import without doing the sort)

    I understand this is kind of a newbie problem, but I'd love to get my head around it

    thank you kindly!

     

  • December 8, 2022 at 3:51 pm

    #4123449

    You could also explicitly define ("format") the column as "Text" data type in Excel.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 8, 2022 at 4:17 pm

    #4123460

    That is a common problem when importing from Excel. The way I handle it is

    1. Add IMEX=1 to the Excel connection string
    2. Include the header row in the import, as though it is data

      1. You need to throw this fake data-row away during the import, but (in conjunction with (1)), it forces Excel to read all columns as strings.

    • This reply was modified 1 year, 7 months ago by  Phil Parkin.
    • This reply was modified 1 year, 7 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 8, 2022 at 4:46 pm

    #4123478

    thanks guys.  this is great. really appreciate the assistance!

    Interesting, at the surface, one would think this wouldn't be a difficult job for sql server to handle(?)

  • December 8, 2022 at 5:07 pm

    #4123488

    John524 wrote:

    thanks guys.  this is great. really appreciate the assistance!

    Interesting, at the surface, one would think this wouldn't be a difficult job for sql server to handle(?)

    You're apportioning blame in the wrong place 🙂 It's Excel (or, more accurately, the execrable ACE driver) that serves up the dross that SQL Server has to stomach.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 8, 2022 at 5:11 pm

    #4123489

    gotacha... appreciate the help

  • December 8, 2022 at 9:34 pm

    This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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