Remove new lines in a csv - Help

Help Activities
, , ,
1

Hello guys I have a CSV input as attached.
Problem is there are some lines where there is a new line added and should be removed.
So that first column is always ID.
Can this be done with UiPath studio activities?
Any idea please.
Thank you so much

fuel_may_2022.zip (125.3 KB)

2

Maybe some Trim activity?

3

@hurmet.noka Kindly provide more information, about what should remove. If possible share CSV file by zip it and expected output.

4

Hi @hurmet.noka ,

We see that you have provided us with an xlsx file, where as the Image shows us a Csv Text file.

If unable to send a .csv file, try to compress (zip) and send the file here.

We would like to check the Input data at hand.

5

CSV is attached.
As you can see one column entry is stretching on multiple rows. I dont want that.
Or when i read csv to datatable and write datatable to excel this will not appear?
Thank you

6

Sorry I updated the file

7

@hurmet.noka ,

Could you maybe point out to which lines (Line Number if possible) that you were referring to ?

8

@hurmet.noka
Did you tell about this? for LDTA?

image
image1194×468 31 KB

9

If yes, it’s affecting your data? or any issues?
It may be due to autofit of your column.
image

10

@hurmet.noka ,

When analysing the first portion of the data (2-3 rows ), we found Newline Entry, But I believe it is at the end of the row.

image
image666×577 73.3 KB

I believe the csv is in the proper format. Instead of Txt, try to open the csv in Excel.

Or Let us know if you have already tried performing an operation and it failed due to the reason stated.

11

But if I open it in excel is wrong because f these lines.
see below:

image
image2093×928 91.8 KB

o

12

Fuel Data.xlsx (440.3 KB)
When i read the data in excel i get this.

If you scroll down you will see irregularities.

13

I have to remove the new line character on those problematic lines and to bring them on their proper line

14

@hurmet.noka
I open the document in excel but I not face any issues. Kindly refer to the image

image
image1173×575 27.3 KB

15

image

How come, I have this wierd issue?

16

@hurmet.noka
Right click your CSV file and select open with Excel and see the results

17

Is the result above.

18

Which Excel version are you using?

19

@hurmet.noka ,

Let us Recap/Review the Implementation on How you might have received the Irregular data in Excel. Could you confirm if the below Steps were used :

  1. Read the CSV file using Read CSV Activity.
  2. Get the Output Datatable.
  3. Write the Output datatable to an xlsx file using Write Range Activity.

When Reading the CSV file , there is an error on 417th line, because of Extra data in the same line or it couldn’t find the headers for that value. (Starting from BQ Column)
image

image
image1315×233 5.4 KB

We would need further information on How many columns are there in total ?

1 Like
20

@supermanPunch , I think some error or data missing in particular line, so it consider as a single line.

image
image1345×320 35.6 KB

image
image1355×308 33.6 KB