Generate data table out of text

[kaurM]

I have data copied from clipboard in the below format and needed to be pasted in excel.
Here, the column delimiter is space and there are spaces in column values as well. How would you generate data table out of this data? there are three columns here.

ANTA - ZZZ ANTA - ZZZ ABC Real Board Holdings Inc
FTYU - PPP FTYU - PPP XYZ Investment Inc
AOOQ - VBN AOOQ - VBN Demo Private Holdings Inc

(First row as an example-> Column 1 (ANTA - ZZZ)
Column 2 (ANTA - ZZZ)
Column 3 (ABC Real Board Holdings Inc)

[postwick (Paul Ostwick)]

Normally you’d use the Generate Data Table activity. You could designate - as the delimiter, but in this case that won’t give the desired result. I copied and pasted your sample data into notepad and it appears to be what is called “fixed length format” - meaning each column on each row has the same number of characters. So you can use the Generate Data Table activity and tell it column 1 is 11 characters, column 2 is 11 characters, and then column 3 whatever the maximum value length would be.

That’s assuming your real data always follows this fixed length for each column.

If your data isn’t actually consistent column lengths like that, you’ll have to split it on VbCrLf to get each row into an array, then loop through the array, use RegEx to get the values you want for each column, then Add Data Row to add it to a datatable.

[lrtetala (Lakshman Reddy)]

Hi @kaurM

Try this

Sequence24.xaml (17.1 KB)

Output:

Data.xlsx (8.4 KB)

Regards,

[kaurM]

Hey, Thank you so much. For some reason, I am unable to open the xaml file you shared -
’
Also, there is an issue installing “System.Text.ReularExpressions” package.
Do you mind pasting here the screenshot of what you have in 'Regular expressions" activity.

[lrtetala (Lakshman Reddy)]

@kaurM

Pattern: "([A-Z]+\s+\-\s+[A-Z]+).*"

Regards,

[postwick (Paul Ostwick)]

System.Text.RegularExpressions is not a package. Don’t copy/paste the expression, type it in from scratch so the imports are done correctly.

Also, Find Matching Patterns is in the UiPath.System.Activites package. What version of that do you have?

[kaurM]

@lrtetala

([A-Z]+\s+\-\s+[A-Z]+).*

Hey,
This is working perfectly fine. Thank you so much.
Can you help me write regex pattern if have more columns besides 3 I posted originally -

ANTA - ZZZ ANTA - ZZZ ABC Real Board Holdings Inc 10/13/2018 20:21 7/14/2018 9:57 ACTIVE PHI DEMO GROUP PPR1 68118843 YYY OOP JJJ

[lrtetala (Lakshman Reddy)]

@kaurM

How about the following?

Pattern: "([A-Z]+\s+\-\s+[A-Z]+).*"

Sequence24.xaml (25.5 KB)

Output:

Data1.xlsx (6.3 KB)

Regards,

[kaurM]

Thank you very much