CSV File Format is Invalid

Hello,

I’ve been able to isolate my issue based on other responses in the forum. I need help working around an issue with a CSV file.

Working CSV

Column 1, Column 2, Column 3
“number=123, ou=456, o=data”,P123445,Brad,
“number=987, ou=456, o=data”,P123456,Tim,
“number=654, ou=456, o=data”,P123446,Larry,

Non-Working CSV

Column 1, Column 2, Column 3
“number=123, ou=456, o=data”,“P123445”,“Brad”,
“number=987, ou=456, o=data”,“P123456”,“Tim”,
“number=654, ou=456, o=data”,“P123446”,“Larry”,

The extra quotations around Column 2/3 are causing an issue with read CSV. I can’t replace all quotes because Column 1 requires them. I could do a read txt file and convert to a DT, however, that is not my preferred solution. I’m using Excel activity v2.6.2

1 Like

Could you provide a sample CSV we can look at please

Hi @bradsterling,
I’m not sure but can you try to remove spaces in columns declaration? I marked them as underscore in my example below.

Column 1,_Column 2,_Column 3
2 Likes

Hi @bradsterling

what is the issue your facing?
are u getting any extra double quotation marks for column 2 and 3? If so you can replace
quotation marks with nothing for only column 2 and 3 by looping the datatable.

use for each row to loop the datatable and for every row replace column 2 and 3 values.

Let me know for any queries.

1 Like

@Ellboy Please find the sample CSV below.

demo.zip (217 Bytes)

@Pablito I have isolated one issue to the header row.

The spaces are read fine, however, there is not a comma after the last header name. When I manually enter a comma in Notepad++, the file is read fine.

I’m receiving this file and do not think I have much power to change the way it is created.

I’ve tried using read text file, but the file is too large for that activity. Read CSV is the only activity that seems to work.

@Karun I think the issue is that Column 3 needs to have a comma after. When I manually add one via Notepad++, the .CSV is read fine. Any suggestions on how to overcome that?

Column 1, Column 2, Column 3

@bradsterling if the file is read with a comma added to the header, I believe what’s happening is because of how the CSV is formatted. Removing the last comma in each data rows also worked. You may have to validate the data for you attempt to read it.

1 Like

@karavinds Thanks. Yeah, my problem is how to validate the data.

I can either add or delete the relevant comma on any line, but this is a company-wide report and and I have no power to change it. I don’t know where to go from here since the Read Text doesn’t work either.

The PS script attached should help you do that.ReadCSV.zip (2.0 KB)

You may have edit the file paths to what you have both in UiPath workflow and the PowerShell script.

1 Like

@karavinds Thank you. I really appreciate the hard work! I can not run PS on my work computer because I don’t have Admin rights. I’m going to try and modify the code to vb.net.

You can do it quite simply within UiPath. i’ve attached something that might work for you, then it can be part of your automation :slight_smile:Main.xaml (4.8 KB)

1 Like

@Ellboy Thanks - I’ve tried using the ‘Read Text’ activity, but I always get the following error:

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Exception: Could not retrieve the result of the job execution. This might be because a message was too large to process.

Is the file quite large? That might be the issue, you can increase the message size UiPath can handle in the settings:
https://studio.uipath.com/docs/wcf-message-size-configuration

1 Like

@Ellboy
The file is only 106,293 KB but UiPath always throws that error.

Thanks! I didn’t realize I could change it. I’ll check into it. In the meantime, my current working solution is below for all others with a similar situation.

Try
Dim lines() As String = io.file.ReadAllLines("demo.CSV")

For x As Integer = 0 To 1
    If lines(x).contains("variable") Then
        lines(x) = lines(x).replace("variable", "variable,")
    End If
Next

io.file.writealllines("demo.CSV", lines)

Catch ex As Exception
	out_exception = ex
End Try
1 Like

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