How to convert the "|" separated input text data into excel file

Help Studio
,
1

Hi Team,

In the stuck in the module where I have the input text file as a STRING variable as shown below:-

formating
formating1552×191 20.4 KB

As you may see, the data is populated in the String format which is separated with respect to “|”.

Please note that there are 16 columns in the Data.

I want to convert this input text file data into the Excel file using the “|” as a coma separator.

Kindly help me with the same.

Thanks and Regards,
@hacky

2

@hacky I guess you can use Generate Datatable Activity with comma as separator

1 Like
3

Hi @hacky,

I’ll suggest you to use Generate Data Table activity… with column separator | & line separator as Newline.
follow this link to know more about
https://docs.uipath.com/activities/docs/generate-data-table

image
just check csv parsing checkbox
Column Separators —> |

2 Likes
4

@samir, @supermanPunch

This looks good, so if I have the data such that in some rows it is moving down to the next line, how can it handle this situation?

5

@hacky,
There is a newLine separator as well, as shown in below shot

2 Likes
6

@samir

Thanks!!
This helps.

Also, as you see the data in my input text file is completely header less, I am getting the auto generated columns such as (Column 1, Column 2, etc).

If I want to set the column names, is there any provision in this activity?

Or I need to use the traditional method:

where the new ColumnName can be set with datatableVar.Columns(“oldColumnName”).ColumnName
doing this within an assign activity
??

1 Like
7

@hacky
There is option like, First Row as Column Header… but I don’t see any option in Generate Datatable activity itself, which sets new column headers, so you have to use your way on this. :slightly_smiling_face:

1 Like
8

@hacky Yes It might need to use that Expression :sweat_smile: but not necessarily need to use it many times to rename all Columns. You can Create an Array Of Strings, having your new Column Names, then Use a For Loop to Loop through the New Column Names, set an Index in For Each. use that Index inside the For Each with the Assign Statement below :

datatableVar.Columns(index).ColumnName = columnNameArray(index)

4 Likes
9

@supermanPunch, @samir

As I checked, there were few rows of the data, where the data is populated such that the text is going to the next line.

Also, if you check manually, in word document, there is an option called, WORD WRAP, which brings all the text in one line if its meant to be.

When I am using the setting as suggested by @samir, I am still getting some rows to be considered as a separate rows when text goes to next line.

Please suggest.

10

@hacky

If you provide input text file, it will be easy for everyone to get this.