topic Trying to read excel from OLEDB in Product Forum

Trying to read excel from OLEDB

JohanSörman — Wed, 23 Feb 2022 11:12:32 GMT

Hello,

First of all, I never used OLEDB before.

I'm trying to read a excel file into a db and then read it as collection.

My connection string looks like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&[Excel-file path]&";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

And my SQL querry in "Get Collection" action:

"SELECT * FROM [SHEET1$]"

is the syntax wrong or something else that could be it?

Error message:

Internal : Could not execute code stage because exception thrown by code stage: Invalid argument.

Also saw this earlier but not anymore:

ERROR: Internal : Could not execute code stage because exception thrown by code stage: Could not find installable ISAM.

------------------------------
Johan Sörman
RPA Developer
Telia Company
Sweden
------------------------------

Re: Trying to read excel from OLEDB

PvD_SE — Wed, 23 Feb 2022 11:33:52 GMT

Hi Johan,

Indeed can OLEDB be a dream to work with or a pain in the behind to get started with. Once you get it working, it is both fast and able to handle larger volumes of data. Well worth the effort!

The order of all things OLEDB:

Connect to the XL with action 'Set connection' and your connection string. You can find all about this here
Mine usually look similar to this:
Replace("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XYZ;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"";", "XYZ", [xl path])
Wait a short while for things to fall into place. Usually 5 seconds does the trick for me
Open OLEDB with action 'Open'
Build your SQL statement, something like this: "select * from " & [xl sheet name] & " where " & [filter]
With:
'xl sheet name': "[" & [xl sheet name] & "$]"
'filter': "(YEAR = '2022' and ProductCode = '123')"
Run your SQL statement with action 'Get Collection'
Wait a short while for things to happen. Again, 5 seconds works for me
Close the connection with action 'Close'

'Known' issues:

Duplicate column names in your XL are not appreciated by OLEDB
OLEDB does not like illegal characters in XL column names (a dot in a column name is considered illegal, there are probably more)

Try this and see if it helps. Otherwise, there's more here.

As always, happy coding!
---------------------------
Paul
Sweden

Re: Trying to read excel from OLEDB

naveed_raza — Wed, 23 Feb 2022 17:31:41 GMT

In Connection String XML keyword should not be there .

I am posting my code here which i am using every day.

I written the Global Function code and i added 2 parameters to it, Full File Path and Sheet Name

Public Function ExcelSheetRead(ByVal filepath as String,sheetname as string) As DataTable
Dim dt As DataTable = New DataTable()
Dim Con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & filepath & ";Extended Properties=Excel 12.0;")
Con.Open()

Try
                                                
	Dim oCon As OleDbCommand = New OleDbCommand("Select * from [" & sheetname & "$]",Con)
	Dim adp As OleDbDataAdapter = New OleDbDataAdapter(OCon)
	adp.Fill(dt)
                                                
Catch e_ As Exception

End Try

	Con.Close()
	Return dt
	
End Function