Read text file and write excel

Hi Developers,

Greetings…!!

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:
|Name|Vineet
|id|123|
|city|abc|
|Name|junaid|
|id|987|
|city|lkj|
image

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:
image

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:
Variables:
grafik

Preperation of the target datatable:
grafik

Flow:

Input / Result:
grafik

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.

Parsing

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

\|Name\|(?<name>[^\|]+)\|\s*?\|id\|(?<id>[^\|]+)\|\s*?\|city\|(?<city>[^\|]+)\|

See it in action online in regex101.com

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

To CSV

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

Then:

  • 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.