OLEDB Excel connections - mixed type columns

[Erika_Papp]

Dear All,

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.

When I use the Get Collection action for OLEDB in the result collection I lose data (a,b,c in the last 3 rows), because the datatype for column B is number instead of text.
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?

Thank you.

------------------------------
Erika Papp
RPA Specialist
Winformatics
Europe/Budapest
------------------------------

[Erika_Papp]

Hello,

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

[GopalBhaire]

Can you check registry if there is one more key TypeGuessRows, if there is no other key maybe change TypeGuessRows to 100 it will just check first 100 rows for mixed data type.

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

[Erika_Papp]

I have a single instance of TypeGuessRows. I modified it to 100 but still the same result.
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
------------------------------

[GopalBhaire]

Did you try that, I'm using Access Database engine 2016.

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

[NicholasZejdlik]

This is the best solution that I was able to find in a similar circumstance - HDR=No and IMEX=1. The problem is that it is looking at the first few rows of the file and guessing the datatype based off of that. That is why column B keeps coming as numeric and dropping the non-numeric data later on in the column. By using HDR=No, it ensures that the column name is included in the data, which will keep it from guessing a numeric type for column B since the column name contains alpha characters. So long as the column names do not consist of only numeric characters, this will ensure that all columns are read as text.

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

[DaulatRam2]

Hi,
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
------------------------------

[NicholasZejdlik]

Could you post a sample of data that you're having a problem with? Also, what's your connection string?

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[DaulatRam2]

I want to Insert into Sheet 2 based on filter condition in sheet1
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
------------------------------

[Athiban_Mathi]

Hello All,

Adding Cstr in select query will resolve this issue.

For example : Select Cstr([A]) from [Sheet1$];

Regards
Athiban 
RPA Developer

------------------------------
Athiban Mathi
------------------------------