For each line with issues

Help
1

Hey team,

Got a small one that i hope you Masters of the 'bots can help me sort please.

Am iterating through each line of data, and for the most part it is good. I know the first word before the " " is my product code and 2nd is supplier code, then description, qty, price per, total, etc.

I am using a few String Splits to find the variables: (i.e: ProductCode = Output.ToString.Split({" "),2,StringSplitOptions.RemoveEmptyEntries)(0).Trim This would get me everything before the first " “. And i have all the other bits mapped out to each " " on the line, so i can extract: ProductCode” “SupplierCode” “Description” “qty” "price) eg.

The problem lies with the DESCRIPTION part, as it’s not a fixed length (and sometimes contains numbers) and also because it may be rather long, it can continues onto the next line meaning the “For each line” part is useless for everything on the next line or so.

SO…How do i go about either getting rid of the description so as to bring the rest of the “line” all back to 1 line, or how do i provide some intelligence to the workflow so it knows to concatenate the multiple lines forming 1 line. FYI: i don’t need the description to be retrieved as a variable, i just need it gone, all the other data is needed.

Here is the Output (eg) that goes through the Each line part. Notice the problem on line 4 that extends to lines 5 & 6) due to the column that had an extended Product description (First two lines show example and what variables they are. Actual lines start below XXXXX)

EG:
ODY-FR12CDJWE FR12CDJWE 918 CHINA PRICE US DOLLAR 87.9 10 EACH 87.99 1238.95
ProductCode-------| |–SuppCode-| |-----------------description---------| |$$$| | QTY | |$$$| | total |

XXXXXX
ODY-FR12CDJWE FR12CDJWE 918 CHINA PRICE US DOLLAR 87.9 10 EACH 87.99 1238.95
ODY-FRER2 FRER2 918 CHINA PRICE US DOLLAR 43.3 10 EACH 43.38 610.81
ODY-FRER6 FRER6 918 CHINA PRICE US DOLLAR 56.3 10 EACH 56.39 794.00
ODY-FZ12CDJWXDBL FZ12CDJWXDBL ODYSSEY BLACK LABEL XD EXTRA 10 EACH 129.74 1826.81
DEEP COFFIN FOR LARGE PLAYERS
& 12" MIXER (FZ12CDJWXDBL)
ODY-FZ12MIX FZ12MIX 918 CHINA PRICE US DOLLAR 47.1 10 EACH 47.15 663.90
ODY-FZF5437T FZF5437T 918 CHINA PRICE US DOLLAR 94.3 10 EACH 94.39 1329.06

Hope that makes sense. Thanks

2

I am just woundering why you have not used regular expressions?

Just import system.text.regularexpression
And use
Regex.matches(yourstring,regular expression)
This will return match collection

May be we can work together to find out exact regular expression for you.

Following should work, if not let me know
Supplier code (?<=\s)(.*?)(?=\s)
Qty \d+ EACH
Total \d+[.]/d+$

Let me know if you are facing any difficulty.

2 Likes
3

@MikeBlades

Any progress?:blush: