Extracting Information from a Text File

[berty]

Hi

I'm fairly new to Alteryx and slowly developing my skillset, i'm having some trouble extracting key data from a text file, if i need to parse the full data underlined below how can i achieve this - to assign each customer with a record id

Here is the actual text (sorry i wasn't able to upload the file itself)

CUSTOMER......:CALL 2 SEE
CUSTOMER:0124874                                 Yodamo Ltd.      Account number:    3e56gh
                                    Company Registration No.
 CALL 2 SEE                                                        Ref number......:   0674345
                                               20154/00765
PO BOX 1000               ID Registration No. 4014563DF1        Invoice number.....: xxdetgdwe
-                            ...                                           Invoice date....: Jan 1, 2020
                                                                 Your  registration
COLUMBIA                                         Registered Office                        161511
                                                                        
CUSTOMER......:JETSKIESE
CUSTOMER:0124875                                 Yodamo Ltd.      Account number:    3e5448gh
                                    Company Registration No.
 JETSKIESE                  ...                                     Ref number......:   0745342
                                               1545412/0007
PO BOX 1000               ID Registration No. 4014G345        Invoice number......: xx4567Ge
-                                                                       Invoice date....: Jan 1, 2020
                                                                 Your  registration
COLUMBIA                                         Registered Office                        954287

[patrick_mcauliffe]

This looks like a good use case for the Regex tools.

Does it all come in as one line or are there line breaks?
If it all comes in as a single line you'd want to use the Text To Columns tool. 

Assign a marker that you know indicates a separation in the data (like \n or ":" ) then click the radio button at the bottom of the tool "split to rows".

From there, parse out the ID you need with the plain regex tool.

It looks like your customer ID is always a string of digits after the characters "CUSTOMER:", so probably something like this:

[DavidP · Accepted solution]

Hi @berty 

Following on from our discussion in your other post, this is how I would approach this problem:

[berty]

thank you both, that was a great help!

i think i know where I've gone wrong, the 'customer' is captured on the expression as "startswith", but actually in the raw data i have the report title on the 1st row, for the 2nd row i have some "-------" as report formatting. How do i get  Alteryx to ignore the 1st 2 rows for "startswith" expression?

note - my data does show in the report as(line 3) "CUSTOMER......:" and also on line 4 it has "customer:" - this seems to increment my record number?

[DavidP]

How about skipping the 1st 2 rows with a Sample tool before starting the logic with the multi-row tool.

[berty]

I've used the sample tool to ignore the first few lines … thanks all