SQL Server Performance

Import .xls file

Discussion in 'SQL Server DTS-Related Questions' started by Reddy, Jul 10, 2006.

  1. Reddy New Member

    Hi

    I am importing an excel file daily which is an automated process but last day I got error saying that destination column does not allow nulls. BUt when I looked in detail everything looks fine for me.

    The only diffrence I observe from previous loads is that this file is bigger than tha files before processed. PLs help me to load it anyway.


    Thanks!
    "He laughs best who laughs last"

  2. Luis Martin Moderator

    I don't think bigger file is a problem..
    Check excel again to find some null value.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Reddy New Member

    I am sure I dont have any duplicates or nulls, weired thing is that when I split the file into 2 and ran my job it was gud. so I concluded that thae error was due to the large number of rows. If the number of rows exceed more than 35, this job fails. Pls help me to find the resons why it happens so.

    Thanks!
    "He laughs best who laughs last"

  4. Adriaan New Member

    Look at the SOURCE data ...

    At least one of the rows has a blank on at least one of the columns, or an incorrect format for the intended data. This is Excel, so we're not talking about proper columns in a table. The information in a cell can be inconsistent with the general type of the data. You could have a date like 31-Feb without a year, or a number like 23e8 - and they would come over as NULL.

    The column where this data will be inserted does not allow NULLs, that's the error message.

    Don't bother with the number of rows, unless you have an import routine with a row counter that is declared as a data type that is too small - like TINYINT which is limited at 255.
  5. Reddy New Member

    If there is any error how does the file loads successfully when I split the same file into 2 files and load them seprately?

    Thanks!
    "He laughs best who laughs last"

  6. Adriaan New Member

    After you split, you have one batch of 35 rows, and another batch of ... rows?
  7. Reddy New Member

    i split the file into 20 and 20 rows

    Thanks!
    "He laughs best who laughs last"

  8. Adriaan New Member

    You mention an automated process that processes the Excel sheet. What kind of process - DTS, BCP, INSERT based on OPENQUERY, ... ?
  9. Reddy New Member

    DTS

    Thanks!
    "He laughs best who laughs last"

  10. Adriaan New Member

    Okay, then can you check the exact bits where the data is inserted into the target table. Is there value being inserted that is not coming from the Excel sheet?

    Also, check for INSERT triggers on the target table. Perhaps there is a trigger that was not designed for batch inserts over 35 rows.
  11. Reddy New Member

    I dont have any triggers.
    upon googling i found that such type of imports are not supported by large number of rows, it can process only limited rows, not sure if tht is true?

    Thanks!
    "He laughs best who laughs last"

  12. Adriaan New Member

    IIRC, Excel sheets are limited to 65K rows or thereabouts. Where did you find that limitation?

    Excel has a wizard that you can use to help setup a data import, and it will make a guess of the appropriate data types based on the first 35 rows in the source. There is a registry setting that you can change to increase the number of rows used for determining the data types.

    But that's a whole other issue ...

Share This Page