How to convert text file with pipe delimiter to excel

Help Activities
, , , ,
1

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

ID|Name|Address|DOB|Status
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.

ID|Name|Address|DOB|Status|

Then I want to convert all text files to excel.

2

Can you send one sample text file?

3

You can consider given example as input.

4

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

5

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

1 Like
6

Hi @Mansi_Mhatre ,

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

image
image889×473 19.1 KB

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.