babno
babno

Reputation: 309

ssis ragged right with whitespace delimiter

Working with legacy software which generates files. This one is a ragged right file that is also fixed width, except since the last line doesn't have the delimiter it's 2 characters short and the last record is skipped if I use fixed width, even though I ignore that column.

The problem with the ragged right solution is that the delimiter is 'F'. I have names in my records which occasionally contain the letter F, which means I can't define the delimiter as just F.

Luckily, the first column for every single record is '01D' and in testing in notepad, if I separate out each record by 'F 01D' that's unique and works. Unfortunately visual studios doesn't recognize that, I believe because of the space. I don't see why it wouldn't recognize spaces as valid characters in a delimiter, but I can't get it to work. I've tried escaping it with 'F\ 01D', I've tried using hex with 'F0x2001D'

Another possible solution but has similar issues is that if I can define the header delimiter as '2õ (plus 16 spaces)' that'll make the F delimiter appear on the left instead of the right and the last record will have enough characters to not be skipped. But, if I simply copy paste from the file into the header delimiter, it claims it can't find the delimiter in the file (same solutions tried above also don't work)

Some odd behavior I noticed. If I set the ragged right row delimiter to 'F ' it will identify one place in the file where that occurs. A comment field which states "USE ALL OF THE AVAILABLE SUPPLY" around 29,000 characters into the file. It's the first occurrence of 'F ' which is preceded by a non space character, though it is far from the only one and all latter occurrences aren't recognized.

Upvotes: 0

Views: 46

Answers (0)

Related Questions