Get CSV as Collection rounds 16 digit number - similar to Excel

[diane.sanzone]

I am trying to read a CSV file into a collection The file contains a 16 digit number. If I open the file in notepad, the value is correct. When I read it into BP using the File Utility and "get CSV text as collection", it rounds the number to the nearest 10. If I open the file in Excel, it does the same thing.  I am purposefully NOT opening the file in excel prior to loading, but BP still reads it into the collection with the rounding.  I know I can do a "read all lines from file" call instead and then parse the value.  I'm looking for an answer as to why BP is not reading the text from the file correctly.

Any help you can offer is appreciated.

------------------------------
Diane Sanzone
------------------------------

[david.l.morris · Accepted solution]

This isn't the fault of Blue Prism. It's OLEDB that is interpreting that field as a number and it has a precision limitation of 15 digits.

I've come across two possible solutions, one of which involves editing the registry settings.

1. Not involving the registry: https://stackoverflow.com/questions/3595131/i-need-a-workaround-for-excel-guessing-data-types-problem
2. Involving the registry: https://social.msdn.microsoft.com/Forums/en-US/83bef763-5ba1-4704-a10d-d29ac1d7af78/how-to-tell-excel-driver-not-to-guess-data-type-of-some-columns?forum=adodotnetdataproviders

------------------------------
Dave Morris
Cano Ai
Atlanta, GA
------------------------------

[diane.sanzone]

Thanks, Dave. This is exactly what I needed/was looking for. Appreciate the quick response.​

------------------------------
Diane Sanzone
------------------------------

[AneeshaKolli3]

Hello Diane,

Can I know what changes you have made in order to read CSV values right?

Appreciate your response.

------------------------------
Aneesha Kolli
RPA Developer
Education
America/New_York
------------------------------

[diane.sanzone]

Hi Aneesha,

This was quite some time ago so I can't explain why I did what I did, but here's what I did. (happy path only)

1. Read the CSV file using Utility - File Management - Read Lines From File action. I used inputs of my file path, start line 0 and end line 999999999999999 to read the entire file.  My output was a collection named CSV Lines
2. I parsed the first row in the collection using Utility - Strings - Split Text, with inputs of the first row, split Char of "," (a comma inside quotes) and my Collection Field Name of "Column1". I saved that output in a collection called "Header Row"
3. I looped through the Header Row collection to confirm my required values were there
4. I then deleted Row 1 from my CSV Lines collection (the header row) because I have a pre-defined collection (called Final Collection) that I use to store the data I need
5. I looped through the CSV Lines collection, parsing each row, again using the same Split Text command
6. After splitting each row (within my loop) I add a row to my Final Collection
7. I then loop the results of the Split text command and set the value to a field in my final collection, tracking each position with a counter I set in my next step
8. I increment my counter and loop again

So long story short - I did not use either of Dave's fixes. If I remember correctly, they didn't fix it for me and I didn't have time to keep troubleshooting the application.  I ended up just using BP canned functions to read the data differently and parse it directly.  It's not the prettiest or the fastest, but it is stable and functional. This bot runs daily in production and I have yet to see it fail or incorrectly parse the data (but I'm sure it will tomorrow :))

Here's a screenshot of my double loop if it helps explain the description above.  I marked the loops with colors to help it match back easier. Hope this helps!

------------------------------
Diane Sanzone
------------------------------