Read text file and write excel

Hi Developers,


I trying to find a solution, of this process like read text from txt file and write it on excel. and
work.txt (74 Bytes)
where i getting output like:
same as in text file, I want a output like column wise like:
name | Id | city entered the value as below on the excel.

Dear developers,

for the clear understanding my output is :slight_smile:

and I want the output to be like:

Name id city
vineet 123 abc
junaid 987 ikj
![image 193x81](upload://5rBraHTBpIsmH9O5vJXvUn1tzCz.png)

when following table is achieved:

then it can be sliced on every 3rd row (rowindex Modulo 3 = 0) and the sliced rows can be transposed to the target structure: Name, ID, City

1 Like

@ppr , how to do this?

how to achieve it?

A quick optimistic Non-LINQ approach could look like this:

Preperation of the target datatable:


Input / Result:

Find starter help here:
SliceEveryNrow_TransposeSegment.xaml (12.0 KB)

1 Like

Hi @Majunu09

Did you forgot a | at the end of first line?

I can see two approach at first glance:

  • Build a DataTable, directly parse the text file and add values to a new row.
  • Transform the text to a csv that can be read as a DataTable.


if you’re confortable with Regex handling, you can parse your text with the following pattern.


See it in action online in

You can add row to a DataTable (or directly write into an excel file but I don’t think its optimal)


Replace headers and separators with string replace method or you can still use Regex :slight_smile: like the following:

You’ll find an example in attachment. Here is a short explanation. Please update the path variables before running it

Raw = "Your text that you can read with Read Text File activity"
Pattern = \|Name\|(?<name>[^\|]+)\|\s*?\|id\|(?<id>[^\|]+)\|\s*?\|city\|(?<city>[^\|]+)\|
CsvText = System.Text.RegularExpressions.Regex.Replace(Raw, Pattern, "$1;$2;$3")

CsvHeader = "Name;ID;City"
CsvTextWithHeader = CsvHeader & vbCrLf & CsvPath


  • Write Text File with CsvText (or CsvTextWithHeader)
  • Read CSV with produced text file
  • Read Range (workbook flavor) to write produced DataTable into an .xlsx file

Regex_Replace_Fields.xaml (9.3 KB)
Regex_Replace_Fields.txt (74 Bytes)

1 Like

:sweat_smile: Thank you I use Generate data table from text activity instead using regex :upside_down_face:. Its remove all special character before writing on excel.

just updated my post with example and explanation for second approach.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.