Read Value From CSV

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:

1. splitting the data using the combination of quote and comma as the delimiter
2. stripping out all of the remaining quotes so your data is now an array of their basic values

...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

Thanks,

Gaurav Singh

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

Thanks,

Gaurav Singh

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:

• to get the first two values you can simply use fn!split() with a comma as the delimiter and just reference the first two instances in the generated array and ignore the rest
• to get the last value you can find the positions of the quotes, extract the characters between those positions and cast to a numeric value

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.