Problem read large excel files with connect to database

[samet941 (Samet Özcan)]

A few months ago I developed a process that read large sized excel file with connect to database activity. Until first day of the new year everything was okey. After that the process started giving this error “Connect to database: The type initializer for ‘System.Data.OleDb.OleDbConnection’ threw an exception.”

Information about the activity and version are below;

Provider Name: “System.Data.OleDb”
Connection String: “Provider=Microsoft.ACE.OLEDB.12.0; Data Source=‘LOCALPATH’; Extended Properties=‘Excel 12.0 XML;HDR=YES;ReadOnly=False’”

Robot server’s UiPath version: 23.4.7
Developer server’s UiPath version: 23.4.1

UiPath.Database.Activity package version: 1.9.0

I am getting the same error on both servers.

[loginerror (Maciej Kuźmicz)]

Hi @samet941

Any chance you have the full error stack associated with this issue?

Did really nothing change within the process and the configuration? My first thought would go to the driver, and to make sure it is a 64 bit driver if this is a Windows type Studio project.
It is harder to tell without more details though.

[samet941 (Samet Özcan)]

I tried many times downward packages from manage packages and I thought that’s happened. After that I saw that didn’t work. I changed it manually from the project file. Problem solved.

It’s probably related to other dependencies as well, but just downgrading the package this way worked for me.

UiPath.Database.Activity package downward —> 1.9.0 to 1.7.1

[vasundhara.sharma]

I’m getting the same error. How did you resolve it exactly?

Details
UiPath Database Activities version: 1.9.0
Studio version: 2022.10.7

Dim connectionString As String = String.Format("
                Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source={0};
                Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'", filePathXlsbFile)

The type initializer for 'System.Data.OleDb.OleDbConnection' threw an exception.

System.TypeInitializationException: The type initializer for 'System.Data.OleDb.OleDbConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.OleDb.OleDbConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.ProviderBase.DbConnectionPoolCountersNoCounters' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'System.Diagnostics.PerformanceCounter, Version=7.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'. The system cannot find the file specified.   at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp)
   at System.Data.ProviderBase.DbConnectionPoolCounters..ctor()
   at System.Data.ProviderBase.DbConnectionPoolCountersNoCounters..ctor()
   at System.Data.ProviderBase.DbConnectionPoolCountersNoCounters..cctor()
	--- End of inner exception stack trace ---
   at System.Data.ProviderBase.DbConnectionFactory..ctor()
   at System.Data.OleDb.OleDbConnectionFactory..ctor()
   at System.Data.OleDb.OleDbConnectionFactory..cctor()
	--- End of inner exception stack trace ---
   at System.Data.OleDb.OleDbConnection..cctor()
	--- End of inner exception stack trace ---
   at System.Activities.Statements.Throw.Execute(CodeActivityContext context)
   at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

[sudster]

@vasundhara.sharma , here are some links that may help you connecting to Excel using the ODBC driver.

https://uipath.my.salesforce-sites.com/CaseView/articles/Knowledge/How-to-use-xlslx-as-a-Database-in-UiPath-and-perform-sql-queries?lang=en_US

Using Excel as a Database in UiPath

[vasundhara.sharma]

Hi @sudster - thank you for your response. I skimmed through the link you shared and it looks like it might not work with dynamic Excel file paths. Please correct me if I’m wrong. My use-case involves downloading .xlsb files and then reading the pivot table in a worksheet (table range B6:C, there is data in other columns in the same sheet, the pivot table row count can go up to 100K also).

[samet941 (Samet Özcan)]

Hi @vasundhara.sharma, I wrote chatgpt for using xlsb format and it’s answer below down.

Unfortunately, the ACE.OLEDB.12.0 provider does not natively support .xlsb (Binary Excel) files. While it works with .xls and .xlsx formats, you need to use alternative methods to handle .xlsb files.

Summary:

If Excel is installed and converting the file format is not an option, the easiest solution is to use the Excel Application Scope activity. Directly working with .xlsb files using OLEDB is not supported.