Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
UIT
Starting Member
5 Posts |
 Posted - 2008-08-06 : 12:45:43
|
| Hello All,I have a text file which we receive monthly and its a comma-delimited, file with the double quote (") is used as the text qualifier. (Not two single quotes, one after the other)...I need to import this file into a table using SQL Server 2005, so I am using SSIS. However, there are 2 columns in this file which have embedded in its text, quotes, of all characters. Example:Column Name is ORGNAME:Data for ORGNAME (without the text delimiters):First Company Associates D/B/A "FCA Group"And with the delimiters (as it is in the file):"First Company Associates D/B/A "FCA Group""SSIS is choking on the embedded quotes. Is there ANY way around this? This seems like it should be a bug in SSIS. I could swear that I used to be able to do this with DTS and in Excel and Access too. I am going to toy around with a script that hopefully I can intercept the quote and change it to a single quote. I have no control over the creation of the file, nor can I ask for it to be changed. Thanks in advance! |
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-08-07 : 08:22:40
|
| If it's comma delimited then quotes should not matter. What is the error? Isn't a and b have the same output?a) aaa,bbbb"bbb",ccccb) "aaa","bbbb"bbb"","cccc"Have you tried to select/deselect Text Qualifier option? |
 |
|
|
UIT
Starting Member
5 Posts |
Posted - 2008-08-07 : 08:58:49
|
| No, there are slightly different, (A) is comma separated but no character delimiter. (B) is comma separated and has a quote delimiter. My input file is not like (A), its exactly like (B).If I specify NO text delimiter then the loaded data contains ALL of the quotes. The first quote, the embedded quotes (which is OK) and the ending quote. I've tried so many things but one thing is becoming clear: I must either pre- or post- process this file in order to escape those embedded quotes because there is a bug in SSIS that doesnt allow embedded quotes to be processed normally (like Excel does, Access, even DTS did). It is also in SQL Server 2008 and Microsoft will not fix it. So my question is, has anyone any suggestions of pre or post processing this file in SSIS before it gets into the table? Script component isnt working as I had thought. It cant seem to intercept the row. And ideas? Anyone? |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-08-07 : 09:27:30
|
| Okay if B is your input then you can use Derived Column to remove double quotes. Here's the sytax:REPLACE([Column 1],"\"","")Result: ","bbbb"bbb"" >> bbbbbbbIs this what you're looking for? |
|
|
|
|
|
|
|
|
Embedded Quote in SSIS Text Import ?