ToString processing

I have a csv where one of the fields is problematic when UIPath is inputting into a webform

CSV
id, email, permissions, date
23, email@email.com, [“permission1”,“permission2”], 22/08/2020

i want to the enter the ‘permissions’ field into a webform as: permission1 permission2
i.e. with the special characters removed and with a space between the permission types

This is my best yet lame effort at manipulating the string so far:
Row((Split(text,""]")(0).ToString),“["”)(1).ToString

…but it isn’t even parsing

Hi again @Andrew_Callaghan :wave:

Is this always going to be the case?

Like the CSV file will always contain the "[“permission1”,“permission2”]"?

EDIT:

I think you need to have an additional header here? The permissions columns takes only the value ["permission1" into account since there is a comma after that.

If this is the case, you can always go with replace text and replace the existing value with a empty string and trim it when you are using it.

Sometimes the datatypes get messed up when reading a CSV. Can you validate that uipath is interpreting the ‘permissions’ field in your csv as a simple string? You can check this by putting the following in a write line: dt1.columns("permissions").DataType.ToString

You should also try writing the CSV out again after you read it to ensure that it is still setup the way you intended. The way it’s written the CSV you have given us is invalid because there are 4 column headers, but the 1st row contains 5 columns. You need to ‘escape’ the comma in your permissions fields which I believe is done in vb.net with double quotes (but i’m not 100% sure)

1 Like

Thanks @monsieurrahul , Its not possible to change the headers of these CSVs - the field will however always contain the format [“permission1”,“permission2”]. When opened in excel, this is delimited into a single field.

Thanks @Dave i’ve added that as a write line - the debug doens’t error but nothing is outputted to the console.

@Andrew_Callaghan okay, in that case, can you tell me what row("Permission").ToString returns?

Nothing again. no output

What happens if you write it back to CSV and open it in notepad? Excel is not great for opening CSVs as it changes the formats and whatnot quite a bit

Step 1: Read CSV (original file)
Step 2: Write CSV (save as new file)
step 3: Open new file with notepad (or other text editor like notepad++)

Other quick things to do since it seems like it’s behaving weird:

  • in a write line activity type in dt1.columns.count.tostring - this will display how many columns are in the datatable
  • If the above columns are not the same number you expect, use a for each activity to display the name of each column (for each col in dt1.columns write line col.columnname)

Excellent. thanks for the advice - i’ll try those now

Thanks for your help. The CSV outputs the same as when it went in
id, email, permissions, date
23, email@email.com, [“permission1”,“permission2”], 22/08/2020

dt1.columns.count.tostring errors as below

RemoteException wrapping System.NullReferenceException: Object reference not set to an instance of an object.
at lambda_method(Closure , ActivityContext )
at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context)
at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor)
at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath)
at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

@Andrew_Callaghan I’m unable to read the CSV file as it says I’ve entered more data than the existing headers. Are you facing the same issue?

1 Like

That indicates that the ‘read csv’ activity didn’t work, or the scope of the variable CSVTable is incorrect - it is saying it was never initialized and is null. Could you show the ‘read csv’ activity (including properties) along with the variables pane in that ‘Body’ section? Or better yet, could you upload the .xaml and the .csv?

I’m not sure how the ‘read csv’ activity would be able to work at all since when I make one myself that copies the one you posted it tells me it’s in an invalid CSV

testing.txt (92 Bytes)

will this help? i’ve renamed it to txt because csv is blocked

TestAccountCreation.xaml (23.0 KB)

@Andrew_Callaghan - thank you for providing the .xaml and the .csv

The .csv you have written in the first post is different than the .csv you uploaded which is why we were having trouble. The comma in the “permissions” column is escaped because that entire thing is contained within double quotes

As for the .xaml, the part reading the .csv looks fine to me. That means if you are getting the null reference error immediately afterwards, then you are not reading the correct file. Check and double check that the file path you entered is correct: "C:\temp\test3.csv"

Once you get it working, I would eventually recommend using regex to pull out all the values in the “permissions” column that is found between 2 double quotes. There is likely a regex that can do this all in once, but I do it with regex + linq/lambda as follows: Regex.matches(dt1.rows(0).item("permissions").ToString,"(?<="").+?(?="")").cast(of Match)().Where(function (x) not(x.Value = ",")).Select(Function (y) y.value)

This uses regex.matches() to pull out all values found between 2 quotes in the input string. However, this means that you will be getting the commas found in between each word in addition to the words themselves (which we don’t want). So it then uses linq to filter out matches where the regex match.value is a single comma. This statement will return an ienumerable(of string) value containing all of the matches in the input string.

See it in action here: delete.xaml (6.5 KB)

EDIT: Simplified regex that will ignore the matches that are just single commas: (?<=")(?!,).+?(?=") - I’d use this instead of my regex + linq/lambda solution for simplicity

1 Like

Wow. that’s a big help. i’ve been doing some many edits, i had pasted in the wrong csv. my apologies. I have the csv working when I strip out the chars manually. I will try now with your regex. Thank you!

To get the permissions as a String[], add an assign with this:
permissions = System.Text.RegularExpressions.Regex.Replace(varDB.Rows(0)(2).ToString, "(\[|\""|\])", "").Split(","c)

1 Like

Thanks for you help guy. Having trouble making this work. its not liking the Regex:

ZingTreeAccountCreation.xaml: Compiler error(s) encountered processing expression “Regex.matches(CSVTable.rows(0).item(“Perms”).ToString,”(?<="").+?(?="")").cast(of Match)().Where(function (x) not(x.Value = “,”)).Select(Function (y) y.value)".
‘Regex’ is not declared. It may be inaccessible due to its protection level.
Type ‘Match’ is not defined.

TestAccountCreation.xaml (23.9 KB)

i think its the rest of the flow that is erroring. seems i’m back to the ‘string to int’ issue

You’ll have to import System.Text.RegularExpressions namespace. To do that, click the ‘imports’ tab which is right next to the variable and arguments tab. Then type in System.Text.RegularExpressions and click enter. That should resolve the error you described

1 Like