Post reply

BCP Excel with Null value into table

  • June 2, 2006 at 6:40 am

    #70417

    I have an excel spreadsheet, one of the column (COLUMNA) can be blank (null) or can have values.  The first 8 rows that column was blank.  Then after that it had value in the 9th rows.  I bcped the excel spreadsheet to a table.   After the bcp finished, I found out that the column in the table that corresponding to the COLUMNA in the spreadsheet was null in the whole table.  Can anyone know why? 

     

  • June 5, 2006 at 6:32 am

    #641636

    This is a rather obscure problem that pops up here frequently.  Check my comment under the discussion of the QOD on March 15, 2006. < http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=120&messageid=265749 >.

    As I mentioned in the discussion, this is documented on this MS KB article: "PRB: Excel Values Returned as NULL Using DAO OpenRecordset" at http://support.microsoft.com/kb/194124/EN-US/.  This has been an issue since at least Excel 97 and VB4 (and may be longer than that). But it can be worked around by treating all columns as text.  See these links for the details how to do this.

    Hope this helps



    Mark

  • June 6, 2006 at 2:31 am

    #641882

    DTS plays a guessing game with excel. It looks at the first 8 rows of data and then decides itaself what data type the column is and whether it expects nulls everywhere.

    This will fix your problem.

    http://www.sqldts.com/default.aspx?254

    ------------------------------
    The Users are always right - when I'm not wrong!

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

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