How to convert text file with pipe delimiter to excel

I have multiple text file with pipe delimiter which i want to convert to single excel file.

21|Abc|Nerul|10 oct 92|pass|
22|Bbc|Mumbai,MH|2 jan 93| Fail|
33|xyc|Pune|9 june 94|pass|
34|thd|Chennai|7 may 78|Fail|

Note- first row i.e. header row is fixed but at the end no pipe is there after Status.

So we need to add pipe after Status first i.e.


Then I want to convert all text files to excel.

Can you send one sample text file?

You can consider given example as input.

sample.txt (150 Bytes)
sample.xlsx (7.4 KB) (3.1 KB)

Trying to use Generate data table activity as well but not worked so in above attached workflow, you can find the solution

1 Like

Hi @Mansi_Mhatre ,

Give a Try on Generate Datatable Activity with Format as CSV and Column Separators as |

We can create a Datatable using the Build Datatable with the Column Names that are Already known.

We can then convert each Text file into a Datatable and then Merge it to the Datatable that was Created using Merge Datatable activity. This Should be done using a For Each to Loop over the text files so that we can convert all the Text files into a datatable.