Import only good rows

Question

Import only good rows

newbieuser

SSCrazy Eights

Points: 9423
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

CyclingRabbit Say Hey Kid Points: 693 More actions · Answer 3

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.