I have a text file that I am pulling through the transformation wizard using the delimiter '|' and embedded labels. There is one specific column in the data that is so long that it ends up wrapping down into a separate new row in the transformation process and shifting the remaining columns over. The issue isn't with the delimiter. The rows where that specific cell isn't that large pull through correctly. Does anyone have any ideas how to resolve this issue?
Perform a purge load before doing your cross table to load all the data into the correct fields. (Just a regular load and find out the proximate cause) You may need to remove carriage returns etc as mentioned by Gysbert
What if the line breaks are occurring due to length of line items in the file? The data is loaded out of a .txt file, and I think the issue might be due to exceeding the maximum characters per line in the file type. The only delimiter we have set in the script load is '|' so I'm confused why carriage returns would make any difference. It seems as if the load should ignore carriage returns and only break at the pipe delimiter.
Are you saying to do a normal load and then use the built in cross table function? We're not trying to transpose any data, we just want to correct the wrapping of a single string field.
Would it be possible to do a search for '\n' in the source file and delete all instances? Other than that, I can't figure out why the script would delimit on new line. The only delimiter we have set is '|' so I would assume it would ignore new lines/carriage returns.