I get a report several times a week that has data combined into a single field, and I need to split it out. I know how to use Excel to split to columns, but that doesn't work for this report, because the data can have any number of values (there may only be 3 values there, there may be 10 values there, and I've seen 150 values there) - so I can end up with tons of columns. And if that solved the problem, I'd just do that and then transpose, but the next wrinkle is that there are generally 3 columns of data that each need to split, while keeping the data in the same order as they relate to each other. So if there are 2 values in each of my 3 columns, the first value must always stay connected to the first value in the other columns. I made an example below. In the first table, you'll see how I get the data, and the second table is what I'm trying to accomplish.
Frankly, I don't care how I need to handle this - Excel, PowerShell, SQL - whatever works. The real reports are usually around 300k rows of tab separated, but it can be as many as 2M rows. Anything I try to do doesn't really give me the confidence that post-split, the information that belongs together is correct (and it has to be - this is a report that is used in litigation and can be requested by the courts). [My examples below are very basic - my real data is usually names, absolute paths, filenames, dates.]
Two tables, first an example of how I get data, second an example of how I need it
EDITS: The data comes in a CSV that is tab delimited.
So far we've just used Excel to split it and then we feed it into SQL to store it and report it out correctly. We usually filter for all rows that have only two semicolons, then split those into two columns for each of the three sets of values; then we clear those rows and filter for three semicolons and split those into three columns; rinse and repeat - it's incredibly time consuming.
