Convert data from txt to excel

Help Activities
,
1

I want to convert data from a TXT file to Excel.

The logic is:

  • After the line containing “Account Bank”
  • Read the next line and write the values into Excel columns one by one.
  • If there is more than one value/group, write the next data into the next row in Excel.
  • The table ends when a blank/empty line is found.
    For example, convert the TXT file into Excel like this:

Detail.txt (554 Bytes)
Book1.xlsx (8.8 KB)

2

Ok!
What have you tried so far that you need help with?

3

Actually, these values come from reading an email and extracting the table data as text.
I’m currently trying to find the best approach for parsing the text and writing it into Excel.

Do you have any recommended method or best practice for handling this kind of data?

4

I’d suggest a Regex based approach for your text file, is that something you are familiar with?

5

Use Read Text File activity to read the TXT content and split(Environment.NewLine) to convert text into lines.
Find the line containing “Account Bank” and start loop from next line using For Each:
If line is empty → stop the loop.
Else split the line values using spaces/tab.
Write data into Excel using Write Cell / Append Range.

6

Hi @Stef_99

Can you try this?

image
image1008×1141 61.3 KB

Output:

image
image1918×706 42.9 KB

Sequence16.xaml (11.0 KB)

Regards,

7

@Stef_99

As per your logic i created a workflow and output written in Sheet2 in same excel. Attached below check and let me know if it works.
Testig.zip (225.9 KB)

If you find this helpful please mark it as solution
Happy Automation

8

Hi @Stef_99,

A good approach is:

  1. Read the TXT/email body content into a string
  2. Split the text by line breaks
  3. Find the line containing "Account Bank"
  4. Start reading the next lines until an empty line is found
  5. Split each line using spaces/tabs/regex
  6. Store data into a DataTable
  7. Write the DataTable to Excel

Example logic:

lines = textContent.Split({Environment.NewLine}, StringSplitOptions.None)startRead = FalseFor Each line In lines    If line.Contains("Account Bank") Then        startRead = True        Continue For    End If    If startRead Then        If String.IsNullOrWhiteSpace(line) Then Exit For        values = System.Text.RegularExpressions.Regex.Split(line.Trim,"\s+")        'Add values into DataTable row    End IfNext

Using Regex.Split("\s+") helps when the spacing between values is inconsistent.

Finally, use Write Range to export the DataTable into Excel.

9

Hi @Stef_99

First, use Read Text File to get all content, then use Split by NewLine to convert it into an array of lines. Loop through each line and once you find the line containing “Account Bank”, set a flag to start reading. From the next line onwards, keep reading and split the values (using spaces or multiple spaces), then write them into Excel column by column. Keep adding rows for each group of data, and stop.

10

@lrtetala @yedukondaluaregala @tirtheshpurohit97

I have a question.
If data more than 1 record as attached.
Book1.xlsx (8.8 KB)
Detail.txt (772 Bytes)

How to apply your code ?

11

@Stef_99

Then you need to identify the where the record is ending in the notepad based on that key or index you can split the data then you can write into in excel

12

@yedukondaluaregala
If each data record contains 18 columns in Excel, can the data from the text file starting from line 19 be mapped like this:

  • data 1–18 → Excel row 1
  • data 19–36 → Excel row 2
  • and continue in groups of 18 for each new row?

In other words, every set of 18 values should become a new Excel row.

13

@Stef_99

Yes, you can do that as well, created a workflow check below
Testig (2).zip (259.9 KB)