Dynamic file name in connection string

Help Activities
, ,
1

Hi,
I want to connect Excel as DB by changing the file name of excel every time. Every time when I try to make some changes in new excel file, the changes are made in the old excel file. I mean DB connection establishing the file used while creating the DB connection is considered and modified.
So the connection string was previously like this,

Dsn=April2023_ExcelDB;dbq=C:\USERS\JEYA1\DOCUMENTS\UiPath\ROBOTICENTERPRISEFRAMEWORK_FINYAHOO\DATA\OUTPUT\jeya110f@gmail.com_FinYahoo_18_April_2023.xlsx;defaultdir=C:\USERS\JEYA1\DOCUMENTS\UiPath\ROBOTICENTERPRISEFRAMEWORK_FINYAHOO\DATA\OUTPUT;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

When I wanted to change the filename dynamically, I changed the connection string by passing the string variable (which holds the filename) as follows::

“Dsn=April2023_ExcelDB;Data Source=‘”+filename+“’;defaultdir=C:\USERS\JEYA1\DOCUMENTS\UiPath\ROBOTICENTERPRISEFRAMEWORK_FINYAHOO\DATA\OUTPUT;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5”

image
image951×46 2.38 KB

2

@Jeyalakshmi_KS

This looks correct are you facing any issue?

Also its better to include variable for defaultdir

Cheers

3

Hello @Jeyalakshmi_KS
Your connection is Looking Good
If You need to loop the files in the specific Folder, Use For Each file in the Folder activity and Assign, FilePath= Currentfile.Fullname Or if you facing any issues, share the error with screenshots.
Dsn=April2023_ExcelDB;dbq=“+FilePath+”;defaultdir=“+DefaultdirPath+”;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5

4

Hi @Jeyalakshmi_KS ,

Maybe let us know what is the issue that you are facing when performing this and also take a look at the below blog :

5

I am not facing any errors. But when I want to update the new file that I am passing thru input variable, it’s updating only the old file which was there during Excel-DB connection establishment.
Like if there are two files such as A and B, the changes made thru query are done only on file A but not on new file B.

6

No, I do not want to loop the files. I am passing the file in a variable, I want the ‘Update Query’ to be processed in the file which I am passing dynamically.
Instead it is processing in the file which is selected in Connection wizard during establishing DB connection.

7

@Jeyalakshmi_KS

Can you print the connection string once in log message and check the file path

Cheers

8

Thanks, will look into this n try the same. The issue it’s updating only the old file which was there during Excel-DB connection establishment instead of the file passed in the variable.
Like if there are two files such as A and B, the changes made thru query are done only on file A but not on new file B.

9

image
image1920×265 11 KB

Should I pass the whole thing in a string and print? Because when I tried printing the variable’filename’, it prints the new file name correctly every time. Issue is DSN part only I guess but not sure how to resolve it

10

@Jeyalakshmi_KS

Yes please print whole of the atring and also can you change the default directory path also according to new file

And may i know what is the drive id for?

Cheers

11

Driver ID comes as default when I configured connection.I dint give it manually.
Have attached the xaml file here. Can u please have a look at it? Thanks for your time.

Flowchart_test.xaml (52.2 KB)