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

[CliveBarnard]

We always add & "" to every column to ensure everything arrives in BP as a STRING.

------------------------------
Clive Barnard
Intelligent Automation Solution Architect
TJX Europe
Europe/London
------------------------------

[Erika_Papp]

Could you please help me where and when should I add the &"" to the columns? Thank you.

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

[CliveBarnard]

OK, slightly crossed wires and a better explaination:

We us SQL to extract the data out of Excel and so use something like

Select [Column1] & "" AS [User],
[Column2] & "" AS [email]
From [Sheet1$]

That is using the GET in the OLEDB Object.

------------------------------
Clive Barnard
Intelligent Automation Solution Architect
TJX Europe
Europe/London
------------------------------

[Erika_Papp]

I tired your solution, and now all the columns are text, but some value is still missing from column B. (from the last 3 rows). Thank you.

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

[John__Carter]

Hi Erika - connectionstrings.com is a site I've found very useful at times

https://www.connectionstrings.com/ace-oledb-12-0/treating-data-as-text/

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[Erika_Papp]

Hi John, Thank you for the link. 
When I use the following expression with HDR = NO it works well, but in this case I lose the possibility to write complex SQL statements, as I do not have column names. 

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1";


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

[PSSupport]

Have you tried HDR=Yes; IMEX=1

------------------------------
PS Support
PS Support Account for all of Professional Services
Blue Prism
Europe/London
------------------------------

[Erika_Papp]

Yes I tried, but in this case I lose data from column B. (last 3 rows)

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

[GopalBhaire]

Can you share your connection string and registry screenshot. IMEX=1 should change datatype to text for mixed column data that is column B and C while treating A as number.

Here is my connection string

Provider=Microsoft.ACE.OLEDB.16.0;Data Source='C:\Users\gopalbhaire\Desktop\Temp.xlsx';Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"​

and registry values at Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

Here is the output

​

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