Convert text file to csv

Learn Academic Alliance
1

Hello ,

Please can any one help i am trying to build a workflow that would convert this text file to a csv. it always in this format

2

Hi,

Hope the following helps you.

Sample20200617-1.zip (10.2 KB)

Regards,

1 Like
3

@Yoichi thank youuuuuuuu… i truly appreciate. That worked so fine and fast. Thanks a lot.

1 Like
4

@Yoichi thank you so much… this is the new error i get now when i try to read the csv Read Input CSV: The CSV file format for uiPath\AccessMobileGLRecon\WorkDirectory\Processing\acm03JUN20.csv is invalid. Line 17 contains more values than the header line.

5

Hi,

probably it’s caused by the following.

There are some causes to raise this error.
For example, Is there any comma in your original text?

or can you share your text data?

Regards,

6

Hello Yochi, please take a look at this. i think this is the issue after generating csv from text file it creates a blank column at the end which is normally not supposed to exists

7

Hi,

I think mainly there are 2 possibilities of the problem as the following.

  1. There is comma in the text and it’s recognized as separator of CSV. As a result, number of column in the row is larger than other.

  2. There are 3 or more spaces not as separator but as data . This workflow replaces 3 or more spaces to comma. As a result, number of column in the row is larger than other.

Can you check your text data from the above points?

Regards,

1 Like
8

Thanks a lot @Yoichi … you are very correct , Number (2) is the problem. there is a data that has 3 spaces and it separates it into different column , how do i go about it now

9

I think we would need a regex to replace spaces between characters that are less than 4 spaces to exactly 1 space

10

Hi,

It’s depends on maximum number of spaces as data .
If it’s less than 5, the following expression will work. Can you modify expression in assign activity which I comment “Replace 3 or more spaces to comma”?

System.Text.RegularExpressions.Regex.Replace(text," {5,}",",")

note: currently 3

Can you try?

Regards,

1 Like
11

Hello @Yoichi it worked thank you. but its still has the same issue because there is another data separated by 13 spaces . 13 spaces this time, i am about to give up and call the file unstructured. is there is still anyway out. the file is above the max file size to be uploaded here … here is a link to check it out https://drive.google.com/file/d/12pRUbe7ePOKaHrxA1/view?usp=sharing
the link contains your sequence and the complete text file
Thank you so much

12

Hi,

Can you try the following?

Sample20200617-1v2.zip (12.6 KB)

This add extra columns but works good.

Regards,

1 Like
13

@Yochi hey thanks a lot, i think the for each is running forever as the rows are many

14

Hi

Can you try not debug but run?

Regards,

1 Like
15

Hello @Yoichi , You are the best. I am very delighted . I appreciate all your assistance . it works really fine. without you i would have never gotten it . Thanks a lot . Everything works fine. thanks to you.

1 Like