Text Qualifier inside a Text qualified field

Question

Text Qualifier inside a Text qualified field

SQLAssAS

SSCertifiable

Points: 7246
More actions
March 12, 2015 at 4:20 am

#316219

Hi,
In SQL 2005 if i was trying to insert some data with a text qualifier inside a text qualified field, it would work, for example:
"Name","ID ","Location","","Comany",""House Name" Road",
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
There has to be an easier way to do this??

Viewing 6 posts - 1 through 6 (of 6 total)

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

webrunner SSC-Dedicated Points: 32039 More actions · Answer 1

Hello,

Do you mean you are getting an error when trying to include the exact string ' "House Name" '? (Added single quotes to emphasize that the exact string is "House Name" including the double quotes.)

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 2

If you're actually expecting data with " in it can you change the file format from say comma delimited with " qualifiers to something else like just | delimited?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

SQLAssAS (3/12/2015)
Hi,
In SQL 2005 if i was trying to insert some data with a text qualifier inside a text qualified field, it would work, for example:
"Name","ID ","Location","","Comany",""House Name" Road",
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
There has to be an easier way to do this??

You mention the word "import". Where does the original data "live". In a file or in a table? Also, what tool (SSIS, BCP, Bulk Insert, or ????) are you trying to use to import the data?

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

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

ZZartin (3/13/2015)
If you're actually expecting data with " in it can you change the file format from say comma delimited with " qualifiers to something else like just | delimited?

If everything was pipe i would be a lot happier trust me. But we don't have much of a choice. To change to pipe we would have to do it our selves with the same approach of importing, updates particular sequences of " to | , exporting back out and importing back in :/

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 5

SQLAssAS (3/19/2015)
ZZartin (3/13/2015)
If you're actually expecting data with " in it can you change the file format from say comma delimited with " qualifiers to something else like just | delimited?
If everything was pipe i would be a lot happier trust me. But we don't have much of a choice. To change to pipe we would have to do it our selves with the same approach of importing, updates particular sequences of " to | , exporting back out and importing back in :/

Hi Jeff, its a CSV file and we are using SSIS to import.