Parsing one field into multiple columns, based on label

[drudis]

I have multiple dates/events in one cell of Excel.  Need to parse the entry into multiple columns, for further processing:

~ date/time - Inprogress

~ date/time - OnHold

~ date/time - Shipped...

[MarqueeCrew · Accepted solution]

@drudis,

You might want to parse this to rows instead of columns, but here is the column solution.  I assume that there is a maximum of 6 status pairs per Timestamp + Status field.  You can increase my limit from 12 columns (2*6) to a bigger number.  To be dynamic, you might consider the row option.

I used a Text to Columns tool with the following configurations:

You can have multiple delimiters defined in the tool.  I have a comma and a new line {, \n} defined as delimiters.  I set the maximum # of columns added to 12 and the workflow will error when more data is found.

Cheers,

Mark

[drudis]

Thanks, that helped me parse the data into columns.

Part-II: is there a way to get the data to consistent columns (so all the NEW are in column-A, and InProgress column-B, and Resolved column-D...)?

[drudis]

Solved it with RegEx

[jgo]

Hi @drudis,

As an FYI, or something to experiment with, if the data is parsed as @MarqueeCrew suggested, you can use the "Cross Tab" tool to transform/pivot the data to have it look like your Excel example.

[Raghu_s]

Hello! 

I'm working on a similar problem but am stuck while using the RegEx. The data am trying to split is below, 

This is got as a result of pulling file information as string in a single field in order to pick the latest file from SFTP using Download tool. So using RegEx am not sure how to split words based on white spaces so for time being I pulled File and date info in separate columns.

String: -rw-rw-r--    1 progressive progressive   648818 Mar  6  2017 dailyreporting_03032017.csv.zip

Notation: (.{13}(?=dailyreporting))(?i)(dailyreporting_.{8}.csv.zip)

But unfortunately for some reason the FTP spits out time information as *Mon dd hh:mm* and also *Mon dd YYYY* formats which is making difficult to use any string to date converters or use assumptions using Find & replace tool to convert the actual date. Ex: for Jan YYYY data is missing.

I'm looking for a solid way to parse this date and time. I use FileZilla as client for my FTP. Their forum says listing date/time has always been an issue and I don't see any clue to fix this. Very much looking forward for some advice. 

[MarqueeCrew]

@Raghu_s,

Here is a formula that you can use/modify:

REGEX_Replace([DownloadData], ".*\s(\u\l{2})\s+(\d+)\s+(\d+:\d+)\s.*",
 '$1|$2|$3',1)

It will search the download data field and find:

• Capital Letter followed by 2 lower case letters
• skip spaces
• 1 or more digits
• skip spaces
• 1 or more digits followed by a colon followed by 1 or more digits

It then outputs Mmm|99|99:99

You can change this to fit your needs.

Cheers,

Mark