topic RE: OLEDB Excel connections - mixed type columns in Product Forum

OLEDB Excel connections - mixed type columns

Erika_Papp — Tue, 24 Mar 2020 14:40:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

CliveBarnard — Tue, 24 Mar 2020 14:52:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

Erika_Papp — Tue, 24 Mar 2020 15:02:00 GMT

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

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

RE: OLEDB Excel connections - mixed type columns

CliveBarnard — Tue, 24 Mar 2020 15:35:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

Erika_Papp — Tue, 24 Mar 2020 16:02:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

John__Carter — Wed, 25 Mar 2020 10:13:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

Erika_Papp — Thu, 26 Mar 2020 12:38:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

PSSupport — Fri, 27 Mar 2020 11:47:00 GMT

Have you tried HDR=Yes; IMEX=1

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

RE: OLEDB Excel connections - mixed type columns

Erika_Papp — Mon, 30 Mar 2020 08:49:00 GMT

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

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

RE: OLEDB Excel connections - mixed type columns

GopalBhaire — Mon, 30 Mar 2020 11:22:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

Erika_Papp — Mon, 30 Mar 2020 11:53:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

GopalBhaire — Mon, 30 Mar 2020 12:26:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

Erika_Papp — Mon, 30 Mar 2020 13:56:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

GopalBhaire — Tue, 31 Mar 2020 04:37:00 GMT

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

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

RE: OLEDB Excel connections - mixed type columns

NicholasZejdlik — Tue, 31 Mar 2020 13:48:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

DaulatRam2 — Mon, 22 Mar 2021 15:56:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

NicholasZejdlik — Mon, 22 Mar 2021 17:58:00 GMT

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

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

RE: OLEDB Excel connections - mixed type columns

DaulatRam2 — Tue, 23 Mar 2021 05:24:00 GMT

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

RE: OLEDB Excel connections - mixed type columns

Athiban_Mathi — Sat, 11 Sep 2021 11:00:00 GMT

Hello All,

Adding Cstr in select query will resolve this issue.

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

Regards
Athiban
RPA Developer

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