Hi Experts,
I have a requirement where I need to read the number from a CSV file where delimiter is "," and value is also containing comma in it. also the value is in double quotes Kindly suggest how I can read it.
sample value "123,456,789", this value is stored as text in the file but I need to read it as number in Appian.
Thanks,
Gaurav Singh
Discussion posts and replies are publicly visible
Hi Gaurav - are you saying ALL of the values in your CSV file will be contained in double quotes? e.g.:
"Apple","123,456,789","Banana","987,7654,321"
One option would be to format the data by:
...and then, assuming you can always be sure that an item will be a number based upon its position in the array, cast it from text to integer (or decimal)
Hi Stewart,
Values are like: Name,Month,Value,test1,05,"123,452,46.05",test2,06,"123,321,46.05"
Where Name,Month,Value are the column headers. which contains data in 2 rows: test1,05,"123,452,46.05" and test2,06,"123,321,46.05" in respective columns
Ok got it. My first instinct is that it would be easier if the file that is being generated can be altered to chnage this:
test1,05,"123,452,46.05" test2,06,"123,321,46.05"
to this:
test1,05,12345246.05 test2,06,12332146.05
...but assuming this is not an option...if the first two columns are always the same length, you could extract the string containing the numeric value using fn!mid(), starting at the first quote and ending at the end of the string (you'd need to use fn!len() to determine the length of the string in case the numeric value varied in length). Then you can remove the quotes using fn!stripwith(), and then cast to a numeric value using fn!todecimal()
If the first two columns can also vary in length then we'd have to re-think the design.
Unluckily data length is varying
Would reach out on the same post if I get some workaround.
Thanks for your suggestions.
Ok, so you may have to extract your data using two different techniques:
Hi Stewart, I got the same problem but I have to elaborate a huge amount of string for my csv. Is there any smarter way to split data from the csv so that I don't have to add risks of performance in my rule?
Thank you.
Any possibility you can convert it into an Excel file first, then use tools like "readExcelSheetPaging()" from Excel Tools? I believe you would need far less manual parsing that way (as it would presumably handle the column separation for you on the back end), leaving you only the relatively simple task of sanitizing numbers to remove commas where applicable in column values.