Issues with splitting text to columns, stopping short before reaching end of string

[cslagle005]

Hi all,

Novice here, and I've got what seems like it must be a simple fix but this one has me quite frustrated and defeated. I have a raw data file that spits out a bunch of information all in one single column. My end goal is to separate all the data into separate columns. A bit more info below:

• The data is separated by a pipe delimiter until it reaches the 20th set of information, at which point there is a double pipe
• There are 36 different pieces of information, for example:  Report Date | Company | Currency would be 3 "pieces of information" as I'm referring to it

My workflow is splitting the data into 36 different columns based on the pipe delimiter. 

Here is the problem: I'm getting null values around 20 or so sets of information into the string through the rest of the data set. I thought of trying regex(replace) to replace the "||" with a single "|" and then splitting to columns thinking maybe that had something to do with it, but that didn't work and the same problem occurs. I can't figure out why I'm getting nulls and I need to have all of the pieces of information to be split to columns so that I can then run them through a Select tool to keep what I need. It seems like it's just stopping the split to columns before reaching the end and I can't figure out why.

Any advice would be extremely appreciated. Thank you so much in advance!

[Qiu · Accepted solution]

@cslagle005 
I believe you should have received the message shown as below that your data is truncated.

We need to increase the Field Length to big number so all your 36 columns data ban be brought in.

And I do suggest that we should bring the header info as data as well then use a Dynamic Rename tool to bring it up as header.

[cslagle005]

Thank you, Qiu! The Field Length makes sense as being the start of the issue and I think the way you set up the rest of the workflow makes sense as well.

I appreciate it so much, thank you 

[Qiu]

Glad to help😀