- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-10-20 06:47 PM
Any help you can offer is appreciated.
------------------------------
Diane Sanzone
------------------------------
Answered! Go to Answer.
Helpful Answers
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-10-20 07:39 PM
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
------------------------------
Dave Morris, 3Ci at Southern Company
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-10-20 07:39 PM
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
------------------------------
Dave Morris, 3Ci at Southern Company
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-10-20 08:28 PM
------------------------------
Diane Sanzone
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-10-23 08:37 PM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
03-10-23 01:50 PM
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)
- 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
- 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"
- I looped through the Header Row collection to confirm my required values were there
- 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
- I looped through the CSV Lines collection, parsing each row, again using the same Split Text command
- After splitting each row (within my loop) I add a row to my Final Collection
- 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
- 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
------------------------------
