Post reply

Import only good rows

  • April 21, 2010 at 8:33 am

    #221563

    Hi friends,

    I'm importing few tables using SSMS import wizard from Excel. Import fails for all the rows currently because of some bad rows in Excel. I'm trying to find out if there is a setting in the import wizard to filter all the bad rows and just import the good rows into the table?

    Thanks much

  • April 21, 2010 at 4:17 pm

    #1155746

    My recommendation is to never import directly from any source even if you're the one that built the source... it's an invitation for disaster. Import the data into a "wider" staging table, prevalidate all the data and mark the bad rows (or the good rows). Import only those rows that have the correct mark.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 21, 2010 at 8:56 pm

    #1155823

    Since the import wizard is actually a SSIs package in wizard form, if you opt to not execute, but save instead, you can set up the package to only import the "good rows" (i.e. ignore errors). Look at the error tab of the excel INPUT object to set those thing to "ignore errors".

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • April 25, 2010 at 11:18 pm

    #1157449

    I second Jeff, especially if you are importing data in a table that already contains validated records. You are just asking for trouble. I always import the data into a new table, sanitize/resanitize the data, recheck the datatypes, etc and then import it.

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

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