Generating DataTable from Text File where the delimiters or separators are space, tab, newline, colon, or long "-"?

Help Activities
, ,
1

Good Day.

I am trying to generate datatable from a text file where the delimiters or separators are space, tab, newline, colon, or long “-” and write it into an excel file? What is the possible solution for this one?

Kindly see attached file for the sample input and target output file. Thank you.

Sample Input.txt (33.1 KB)
Target Output.xlsx (26.1 KB)

Best regards,
Robert

2

Hi @RobertRussell_Monsalud

→ You can use the Read CSV activity to read the text file and store the data in a datatable.
→ Open the Properties of Read CSV activity and Select the option based on your requirement in the Delimeter.
→ After Read CSV use the Write Range workbook activity to write the datatable to excel.

Hope it helps!!

3

Hi @mkankatala

Can you show me how because I’ve tried the same solution but it did not work?

4

@RobertRussell_Monsalud,

It looks like SAP Data output as text file. I would advise to get it from SAP in the form of xls. Text with Tab option outputs the report in xls format.

Decoding this text file to a table format will not be reliable.

Thanks,
Ashok :slight_smile:

5

Hi @ashokkarale

May I know if using Regex will be useful?

6

@RobertRussell_Monsalud,

If this output is the only option you have then regex will be an option here. Just take care of places like this highlighted below. It will be tricky.

image
image1468×729 47.3 KB

Thanks,
Ashok :slight_smile:

7

Hi @ashokkarale

May I know if this sample input is already correct?

Sample Input.txt (33.1 KB)

Thank you.

8

@RobertRussell_Monsalud,

Yes that’s good. One approach I would suggest for this would be,

  1. Read text file to a string variable
  2. Split the string by NewLine character. This will give us array of string.
  3. Iterate the array using For Each activity.
  4. Add else if activity inside the loop to check which line being iterated.
  5. Define the conditions to split the currenttext variable based on your template.
    for example:
    first line have : as separator. Split the currenttex variable by : and it will give us string array. Just pass his array to a Add data row activity. this will add a row to datatable variable
  6. Once we have the data in Datatable, use Append range activity to write the data we extracted from that particular line to excel file.
  7. Continue till you get ** End of Listing**

I would have helped with the code but, don’t have this much time. Hope you will get this.
Feel free to reach if stuck somewhere.

Good luck.

Thanks,
Ashok :slight_smile:

9

In general we combine different approaches

  • Regex.Replace for setting column delimeters
  • Regex.Match(es) for extracting Blocks / Parsings
  • Filterings (e.g. LINQ Where) to remove unneeded lines (e.g. ---- lines)

Once we have preprocessed the data, we parse it into datatable

  • Generate DataTable activity
  • Custom Parsing
10

If we will work with array of string, will it have a long execution time?

11

Hi @ppr

Can you show me how? Thank you

12
13

@RobertRussell_Monsalud,

Won’t be very long. Use workbook activities to write the data to excel.

Thanks,
Ashok :slight_smile:

14

Hi @ppr @ashokkarale

I tried to use regex but I got this kind of error. How can I proceed with this?

image
image1410×988 63.3 KB

Thank you.

Best regards,
Robert

15

@RobertRussell_Monsalud,

It’s throwing error because of this. We can not have same name column in Datatable. We will have to rename one.

image
image1475×450 13.3 KB

Add logic before generate datatable to rename one string with some additional character like space, ', or anything which will not change the meaning of the column for business.

Thanks,
Ashok :slight_smile: