OLEDB Excel connections - mixed type columns
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
24-03-20 02:40 PM
I have an issue with the OLEDB Excel Connection.
My source Excel file contains columns with where the datatype can be different, like in columns B and C.
I changed the "TypeGuessRows" registry from 8 to 0, and the "ImportMixedTypes" registry from "Majority Type" to "Text".
I also added True flag to the "Treating Data As Text" in the Set connection OLEDB action.
Could you please help, how can I set Blue Prism not to define the datatype of a column based on the first few rows?
------------------------------
Erika Papp
RPA Specialist
Winformatics
Europe/Budapest
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
30-03-20 12:53 PM
My connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\pappe\Desktop\Book4.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"
My Registry settings:
The VBO how I use:
The SQL statement:
SELECT* FROM [Sheet1$]
Result:
Thank you.
------------------------------
Erika Papp
RPA Specialist
Winformatics
Europe/Budapest
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
30-03-20 01:26 PM
------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
30-03-20 02:56 PM
As I see you have Microsoft.ACE.OLEDB.16.0 and I use Microsoft.ACE.OLEDB.12.0. Maybe this difference could cause the difference in the result.
------------------------------
Erika Papp
RPA Specialist
Winformatics
Europe/Budapest
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
31-03-20 05:37 AM
------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
31-03-20 02:48 PM
As far as being able to run SQL against the data, you can query collections in Blue Prism in much the same way. There should be an action for Set Column Names From First Row in the Collection Manipulation utility, which will take care of the column names. There is also a Query Collection action which will accept SQL-like syntax. If more customization is needed, you can also query collections within a code stage using LINQ, which should be able to duplicate any SQL functionality needed.
Probably not the ideal solution, but I wanted to point out that this can still work since SQL functionality can be written within Blue Prism.
------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
22-03-21 03:56 PM
I am having the same issue where Select Query in OLEDB is giving blank value where the datatype is not number in excel column.
I have tried all the above solutions but none seems to work. Any working solution for this issue?
Thanks in advance.
------------------------------
Daulat Ram
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
22-03-21 05:58 PM
------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
23-03-21 05:24 AM
When I use HDR=YES; in connection string it gives null in Place of alphanumeric rows
Sample configuration which I am Using:
Connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source='filename.xlsx';Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1"
Sample data : Column F1 with first 2000 rows with value in format 1234 and some in format A2D7
Query in Execute OLEDB Action : Insert Into [Sheet2$] ([F1]) Select [F1] From [Sheet1$] Where [F2]="No"
Error: Operation must use an updateble query
When I use HDR=YES or NO; and IMEX 0 or 2 in connection string it gives null in place of alphanumeric rows
------------------------------
Daulat Ram
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-09-21 12:00 PM
Adding Cstr in select query will resolve this issue.
For example : Select Cstr([A]) from [Sheet1$];
Regards
Athiban
RPA Developer
------------------------------
Athiban Mathi
------------------------------

- « Previous
-
- 1
- 2
- Next »