How to Extract the table from TEXT file

Help Studio
, ,
1

Hi Team ,

I want to extract the bill To and table as mentioned from the below text file

image
image1286×439 36.1 KB

Expected output should be in data table as mentioned below :slight_smile:
emailbody.txt (848 Bytes)

Indian Board Education ABC Board

image
image1068×132 3.96 KB

please note : The columns names are constant but the values will be change every time and also some times values will be the empty

please find the text file

Thanks

2

could you try to change the extension to .csv and let see if you can read the .csv and build the table

3

Hi @sreene26 ,

Could you also let us know the original source of the data ? Is it the text file itself or is it the data getting extracted from PDF documents ?

If PDF documents, then could you maybe check on table extraction using the below component:

4

Hi @sreene26 ,

The ‘Bill to’ field is simple. Use regex like this one to get the value:
(?<=Bill To:)(.*)

The table is a bit more difficult. If the table is always at the bottom I would split it as soon as you encounter the column names, then try to read it as a .csv like @Raymond_Hui suggested or also use regex to get the value for each row there. If you have a couple of rows each time both methods should be fine, but anything more I would recommend the .csv route.

Recognising the colum names:
^Code\s+Brand\s+Supplier\s+Bottle Size\s+Proof\s+Age\s+Bottles Per Case\s+Case Cost\s+Qty

5

Hi @sreene26

Please find attached workflow to extract table
I hope this will work let me know if its working
ExtractTable.zip (3.1 KB)

6

Hi @sreene26 ,
Do all text files follow the same format?

7

There’s a very good chance any implementation could fail due to missing values.

Here’s my solution; please note it passes with the supplied sample only! I’ve tried to build some tolerance in; lack of enough sample data is a limitation though.

image
image1233×5267 359 KB

image
image1328×210 11.5 KB

8

Can you please share Xaml file, it could be better

9

Here is the workflow file:
TextToDatatable.xaml (33.3 KB)