Convert CSV to Excel using ¶ as a delimiter

Help Studio
, ,
1

Hi All Experts,

I have a csv file and I want to change it to an Excel file using ¶ as a delimiter.
But there is no option available in studio to directly change it. can someone help me ?

2

@Pradyumn_Agrawal1

Use read text file and then you can use generate datatable activity and give the column separator as pipes and rows separator as new line

Then use write range to write data to excel

Hope this helps

Cheers

3

Hi @Pradyumn_Agrawal1 ,

Could you maybe try performing the below Steps :

  1. Read the CSV file using Read Text File activity, get the Output as string, say strDT.

  2. Next, you could use Generate Datatable from Text activity to convert the text data into Datatable by applying the column delimiter as you have provided.

Let us know if this doesn’t work, we can switch to a Linq Expression for identifying the delimiters

4

Hi,
Thanks for your reply.
I used convert text to datatable and tried to paste the sample data in the wizard.

image
image1293×892 45.2 KB

but there is no option to use a custom delimiter

5

@Pradyumn_Agrawal1 ,

Try providing the delimiter you have mentioned as the Column Separator and check

6

it doesnt work,

The bot is stuck and not providing any result

7

Hi,

Can you share your text as a file, if possible?

In my environment, a sample works as the following

image
image886×633 23 KB

Regards,

8

Here is the sample data

APPLICATION.APPLICATIONDATE¶APPLICATION.PROPOSALNO¶APPLICATION.STATUSNAME¶CUSTOMER.COMPANYNO¶CUSTOMER.CUSTOMERNAME¶TRANSACTIONDATA.GROUPEXPOSUREINCLREQUESTEDAMOUNT¶INTRODUCER.INTRODUCERNAME¶INTRODUCER.COMPANYNO¶DECISION.RECIPIENTTEXT
2022-09-26 00:00:00.0¶123456¶Manual Decision¶7890435¶Pradyumn AGrawal¶25000¶XZY Group¶1256789¶Dilip Gupta

9

@Pradyumn_Agrawal1 ,

With the Configuration below, we do get the data separate but not able to make the first row as the headers. Do check if you can bypass it for now and write the datatable to Excel without headers and then read it using the Add Headers checked using Read Range activity.

image
image886×636 23.7 KB

10

I think, this solves my problem, I am able to convert the data and store it in excel format with the headers

Thanks

1 Like
11

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