Excel as Database - how to access without opening excel

Help
1

Hi there,
I am able to connect Database with my excel file, but it works only when it is opened…i want to perform queries in the backend…any parameters i should mention in connection string?

2

Its Strange.

Can you share your connection string (Which Provide do you use)

1 Like
3

Driver={Microsoft Excel Driver (*.xls)};dbq=C:\Users\f.shahabudeen.ali\Documents\myDB.xlsx;fil=excel 8.0;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;

this is my connection string…

4

Just a suggestion @fayaz ,

Try checking the visible = false property in excel application scope so that the excel process won’t display and the operations will be performed backend :slight_smile:

5

Thanks for the reply bro, here i am trying to access excel via “Connect to DB” activity…i am facing issue that it works only when excel is opened physically…but when i close it and run…throwing an error

6

@fayaz

Its really working? this Connection string only work for xxx.xls File only .it won’t work xlsx file

Use below string for xlsx file format excel file

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xlsx; Extended Properties=“Excel 8.0;HDR=YES;IMEX=1”

before you have to install MsAccess Engine

7

Yes, its working for me with xlsx…the above con string does not work for me throwing an error bro…

8

@fayaz

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xlsx; Extended Properties=“Excel 8.0;HDR=YES;IMEX=0”

Yes the above won’t work until you install MSAcess DataBase Engine

Just Install MSAccess Engnie and use the connection string.

For the above string Provider Name should be System.data.oledb

it will sure satisfy all you needs
Downloded link (https://www.microsoft.com/en-in/download/details.aspx?id=13255)
Note(Can you share me the Error Screenshot)

1 Like
9

sure, i will try and tell you…thanks for your time bro :slight_smile:

10

here when you give in connection string box as " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xlsx; Extended Properties=“Excel 8.0;HDR=YES;IMEX=0” ", it wont work as you already add " in Extended Properties…UiPath doesnt allow it

11

@fayaz

have you tried?

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xlsx; Extended Properties=“Excel 8.0;HDR=YES;IMEX=0”;

may I know Why Uipath Doesn’t Allow ?

it is working good for me

could you show which error occurs of you(Share the Error Screenshot)

2 Likes
12

It worked finally without opening excel file bro…i used your connection string but for Extended Porperties with single quote

“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\f.shahabudeen.ali\Downloads\Sample-Sales-Data.xlsx; Extended Properties=‘Excel 8.0;HDR=YES;IMEX=0’;”

3 Likes
13

@fayaz

Happy to hear that…:smiley:

Cheers

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.