Problem Importing NULLs in varchar field

  • 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!

     

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

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • 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 month, 3 weeks ago by  Phil Parkin.
    • This reply was modified 1 month, 3 weeks ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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(?)

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • gotacha... appreciate the help

  • 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