|
|
Thursday, December 2nd, 2004
|
9:37a - import/export wizard problem
Okay, this has me reeeeeeeeeeeeeally confused.
I'm loading a table using the import/export wizard (because I'm focusing on stored procedure code and haven't written the one-step DTS package to do it yet). I'm bringing the data in from a text file (p:\provider\gc1129.txt, if it matters), and so I have to specify all the text file stuff on the next page.
The actual row delimiter is ~{Cr}{Lf}, which is weird enough (and which the provider insists is the industry standard, despite my having quoted the standards documents at him showing the contrary), but I've been giving it just {Cr}{Lf} and stripping off the tildes afterwards. I've been testing the bcp portion of the parser, and I realized I was getting some very odd data on each row; I wasn't noticing because pulling up the table in Enterprise Manager only shows you the first line of data. But I was getting rows containing two or three {Cr}{Lf}s, despite my specifying it as the row delimiter. To make absolutely sure they weren't using some weird Cr or Lf character (or only one of them on some rows), I changed the row delimiter to just the tilde and reran the import wizard. I'm still coming up with rows that look like
3*BGN*00*0001*data*data*ES***4~{Cr}{Lf}
N1*P5*data*ZZ*data~{Cr}{Lf}
N1*IN*data
I've tried importing with both the ANSI and OEM character sets and get the same result, no matter what row delimiter I use. (I can't use Unicode, because everything goes wonky in the preview, and it can't find any row delimiters at all, no matter what I use for them).
Just for grins, I tried using ~{Cr}{Lf} as the delimiter in both ANSI and OEM just now. "The specified delimiter could not be found in the first 8kb of data." Hmmmmmm. Interesting, and I'm running a long and torturous routine over some fo the data now to get the ASCII value for every character on the first line that has this problem. But if the {Cr} and {Lf} characters are screwed up in some way, that still doesn't explain the system not splitting rows when I specify only the tilde as the row delimiter.
In other words, short answer: the import wizard does not recognize all tildes in the file as row delimiters when I specify ~ as row delimiter.
Okay, so the ASCII thing finished running, and I saw exactly what I expected to see: each of the delimiters it doesn't recognize, no matter how I put them in, are 126-13-10. 126 is ~, 13 is Cr, 10 is Lf. So it's simply not recognizing the row delimiters on all rows, no matter how I specify them.
Anyone seen this sort of thing before? Know what I can do about it? Thanks.
(1 comment |comment on this)
|
|
|
|
