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
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)
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.
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)
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
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
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.
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
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)
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.
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
Yoichi
Anil_G
argin.lerit
adiijaiin
raja.arslankhan
arjunshenoy
AutomationX_by_Kiran
AkshaySandhu
SRoyi
lovepong66