Parse Data with blank columns

[JUUP]

Hello,

I am trying to parse a dataset from 1 to 9 columns. The first five columns are always populated and the last four may or may not have data in them. The first five successfully parsed using Regex to look at spaces and digits since their format is always the same, but I'm not sure how to handle the last four. The data looks something like this:

End Column Header

Yes      XYZ            fulfilled

No                  No

ABC    Yes

And I need it to look like this:

Column 6	Column 7	Column 8	Column 9
Yes	XYZ		fulfilled
No		No
	ABC	Yes

I've been looking on the board but haven't found anything that seems like it would apply. I appreciate any advice you can share.

Thanks!

[Jonathan-Sherman]

Hi @JUUP,

Is there a certain number of spaces between columns? Do you have a sample file of the actual data you could attach even if the actual values are changed for privacy just to see the structure?

Regards,

Jonathan

[JUUP]

Hi @Jonathan-Sherman ,

Thanks for your quick reply. I don't think there is a consistent number of spaces between the fields that I can determine. I attached a .csv file (Sample Data 1.csv) showing the data as it exists, and another (Sample Data 2.csv) showing the desired end result.

[fmvizcaino]

Hi @JUUP ,

Looks like you have a standardize number of characters for every columns counting the data as well.

Take a look at the example attached and test it with your own data.

Let me know if that works for you.

Best,

Fernando Vizcaino

[JUUP]

Hi @fmvizcaino ,

Thanks for your reply. I tested your solution with the data and I am seeing an issue. If the final column just contains "No" with nothing following it (Sample Data 3.csv), it was dropped from the Delivered column (RegExOut1 in the screenshot below):

[fmvizcaino · Accepted solution]

Hi @JUUP ,

I have adapted the regex to get that case as well.

Let me know if that works for you.

Best,

Fernando Vizcaino

[JUUP]

That version is working, thanks very much!