Issue while reading large CSV file

i dont know how could we parse a comma separated file if one column has a comma inside it… is it really separated by comma or maybe is by TAB or SPACE?

Comma is coming while writing address.
Hence, the issue is coming

so it is in a row and not in the column name?

Yes it is a cell value of “Address 1” column

do they have quotes surrounding the value?

Hello, Thank you for your inquiry

Here you can find info and here about dividing large files into chunks also as you continue growing in your knowledge take a look at this (optional) and if you are in a hurry I’ve attached a chunk data table xml file for you to take a look (UI Path very slow with huge Excel file - #2 by badita)ChunkDataTable.zip (155.4 KB)

@bcorrea gracias buddy for your help
Regards,


Don’t forget to like us :heartpulse:
Happy Automation! :sunglasses:

1 Like

Yes they do

ok, so every row of data is like this?
“safsfds”,2,“wrqwr”,435345"…
OR
“sdfsdfsd”,“2”,“wer, wer”,“12/12/2020”,“11212.00”

Number does not have double quotes.
Others have

Sample: 182924319427839,996,“R”,20181019," ",“24304795”,“8776”,“WCS5768PDP22”

ok, i will update the code and get back to you in a minute.
Done. change line 6 to:
Dim Values As String() = nextLine.Split(“,(?=([^”“]“”[^“”]”“)[^“”]$)”.ToCharArray)

Thank you, I will quickly get back to you.

It is throwing same error.

19.4.4+Branch.support/v2019.4.Sha.6d308c7f5a32b559373887ae421ce7eaec8f8d24

Source: Invoke code

Message: Exception has been thrown by the target of an invocation.

Exception Type: System.Reflection.TargetInvocationException

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ----> System.ArgumentException: Input array is longer than the number of columns in this table.
at System.Data.DataTable.NewRecordFromArray(Object value)
at System.Data.DataRowCollection.Add(Object values)
at UiPathCodeRunner_9378fb1f31db4a5dac3f979ec42f8a23.Run(DataTable& inDT)
— End of inner ExceptionDetail stack trace —
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
at UiPath.Activities.System.Utilities.InvokeCode.CompilerRunner.Run(Object args)
at UiPath.Activities.System.Utilities.InvokeCode.NetCodeInvoker.Run(String userCode, List1 inArgs, IEnumerable1 imps, Object args)
at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Change that line again to this:

Dim Values As String() = System.Text.RegularExpressions.Regex.Split(nextLine, ",(?=([^""]*""[^""]*"")*[^""]*$)")

Just not sure if the values will end up containg those quotes…

I have attached sample file for you.
In the end just count the number of rows.
It is giving zero to me.Sample1.zip (2.2 KB)
But complete data is failing

well, in this sample is supposed to find no rows… none of them have 40 as value…

Yes you are right.
I changed the file and put 40 on four places.
But it is showing 3 as a count.

It is something right? :rofl: maybe some value is in wrong column…

The problem is when the cell value has “comma”, it is not taking it.
:rofl: :joy:

I have attached sample for you.
It should show 7 but is showing 3.Sample1.zip (2.2 KB)

Yes, some type of quote escaping bug is going on in uipath, because my expression should be good to ignore the comma when is inside the quotes, but in here its broken :frowning:

GOT IT, change only the expression to this:
",(?=(?:[^\""]*\""[^\""]*\"")*[^\""]*$)"

1 Like