May 11, 2011 at 7:01 am
Hi all
I have an issue with loading data from a text file. I normally use bcp or openrowset to load up the data. The supplier of the file has decided to remove the new line chars from the file which I was using as the delimeter since all lines are variable length. The file does however have an apostrophy ' as the delimeter, however the data itself can (and does) have apostrophies in the data, however if they are present in the file and not the delimeter then a ? is used to signify the following apostrophy isn't to be used as a delimeter.
I can't see any way of specifying when to ignore a delimeter in the upload. Currently I've resorted to loading it all as one row, change the non delimeter apostrophies to another char, then split up the data into rows based on the delimeter. Whilst this works and only takes a few seconds to load all the files it doesn't seem like a clean or totally reliable solution.
Can anyone please enlighten me as to how to ignore a delimeter when preceded by the ?.
Any assistance would be greatly appreciated.
May 11, 2011 at 7:57 am
No offense, but it's kinda dumb to use an apostophe as a delimiter for the exact reasons your specifying. It's seems like more work to go in and put a ? in front of the apostrophe. How about suggesting they use something a little more standard like a pipe? Can you supply a couple lines of the file so we can see what it looks like? A couple with apostrophes in the data and a couple without.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 5:03 pm
No offense taken, I did ask them if they could use something else, but got a resounding no, and since they're the customer I have to deal with it.
The actual files contain a thousond or two lines, I've replaced some of the data as it is company sensitive.
Example with an apostophy with the escape char:
UNA:+.? 'UNB+UNOC:3+1111111111111:14+2222222222222:14+121510:0820+21224566++++1++1'NAD+SU+ABC001::92++HARRY?'S SHOP'PIA+1+6351130:IN'PIA+1+ACCXCY-0041:SA'
Example without:
UNA:+.? 'UNB+UNOC:3+1111111111111:14+2222222222222:14+121510:0820+21224566++++1++1'NAD+SU+ABC001::92++FREDS SHOP'PIA+1+6351130:IN'PIA+1+ACCXCY-0041:SA'
There's about 30 segments (UNA, NAD or PIA in the examples are segments), some segements are optional and wont always be there and most segments are varying in length.
Hope that helps and thanks for looking.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply