Bulk Insert with bad data

Question

Post reply

Bulk Insert with bad data

Darryl Wilson

Mr or Mrs. 500

Points: 526
More actions
September 2, 2011 at 4:57 am

#247369

I have a bulk insert statement for a csv, but this particular clients' data is making it difficult to import.
Its in the following format:
GREEN,0016861779528,"Dembo, Ron C","""Roulette"" or the Rules of Risk",0.00,,,"XX(205733.2)",2001,20100517,0,,
MAIN,0016861779528,"Dembo, Ron C","""Roulette"" or the Rules of Risk",0.00,,,"XX(205733.1)",2001,20020311,0,,
The two issues are the multiple Quotes and the Comma between Dembo and Ron C. Also not ALL will have the 3rd field, it could be blank.
Can I also strip the Quotes before it goes into the database?
Thank you
Darryl Wilson
darrylw99@hotmail.com

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

You can remove the quotation characters by utilizing the REPLACE function. For example:

DECLARE @IN VARCHAR(200)

SET @IN = 'MAIN,0016861779528,"Dembo, Ron C","""Roulette"" or the Rules of Risk",0.00,,,"XX(205733.1)",2001,20020311,0,,'

SELECT REPLACE(@IN,'"','')

Result:

MAIN,0016861779528,Dembo, Ron C,Roulette or the Rules of Risk,0.00,,,XX(205733.1),2001,20020311,0,,

A question. If what I assume is a name exists will their always be a comma between the first and last names, in effect making that fields 3 and 4?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Darryl Wilson (9/2/2011)
I have a bulk insert statement for a csv, but this particular clients' data is making it difficult to import.
Its in the following format:
GREEN,0016861779528,"Dembo, Ron C","""Roulette"" or the Rules of Risk",0.00,,,"XX(205733.2)",2001,20100517,0,,
MAIN,0016861779528,"Dembo, Ron C","""Roulette"" or the Rules of Risk",0.00,,,"XX(205733.1)",2001,20020311,0,,
The two issues are the multiple Quotes and the Comma between Dembo and Ron C. Also not ALL will have the 3rd field, it could be blank.
Can I also strip the Quotes before it goes into the database?
Thank you

If you'll post the CREATE TABLE statement for the data above, I'll show you how to do this.

--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)