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!
