November 19, 2004 at 10:14 am
Hello everyone,
I am using xp_cmdshell and BCP to load a comma separated file containing 3 columns: an integer, a string, and a string. The strings are surrounded by double quotes that need to be removed. Any quotes in the middle of the string need to remain in place. I can write some code using left() and right() , or replace(), to remove the quotes after BCP is complete. DTS is not an option for multiple reasons. Is there an easy method to strip the quotes during the BCP load? Is there a better way to approach the problem?
Thanks in advance, and have a great day!
Wayne
November 19, 2004 at 4:16 pm
Um, how about having the source not put them in there? (always the best option: get good data to start)...apart from that...
There is also the option of using a format file with BCP, however, if the same kinds of quotes are embedded in text without proper escaping, that's probably not going to work.
So, no help here... but if your looking for affirmation, I vote for post processing in SQL. What I always like to do is suck the data into SQL and change it there as needed. You can even write a custom function to mod the text column and use that in the insert statement that moves it from the staging table to the permanent table.
November 22, 2004 at 7:56 am
Thanks John,
I never assume that I know everyting, and sometimes I don't even know anything. I was looking for confirmation that I wasn't missing something really obvious. If gurus such as yourself don't have the answer than I figure I am on safe ground. I'm using the post-processing after loading and the job is working fine.
Thanks for the response and have a great day!
Wayne
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply