How to connect Excel with Database

Learn Academy Feedback
,
1

Hi,
Is SQL installation must in the system for an excel to connect with database?

If not, how to find the server name in the connection properties.
and what to give the provider name in the property panel?
Capture2

Capture1
Capture1627Ă—831 54.6 KB

1 Like
2

Don’t use Wizard and put connection string in property directly

3

thats the thing, i exactly dont know what to put in those options , i tried some from forum but nothing seems to work out…can you tell me what to put in those options?

TIA @Riyaz

4

If you want to store the value of Excel file into SQL Server database, then first read the value of Excel using Read Range activity then save its output, that is DataTable, in Database using insert activity.

1 Like
5

I tried jus like u said, but again with insert activity, i am supposed to enter an established database connection which is primary doubt…

Capture3
Capture3731Ă—525 32.8 KB

Capture4
Capture41315Ă—493 58.5 KB

How to do it?

TIA @reyaz

6

use connection string as : “Data Source=;Initial Catalog=Databasename;User ID=sa;Password=”
use provider name as : “System.Data.SqlClient”
In TableName use table name of database
In Datatable use output of Read Range activity
Read Range should be inside Excel Application Scope activity.

Note : don’t configure connection if SQL Server is not installed on your machine or your any machine of your LAN. Put connection string value and provider name in property panel.

If you have any problem share your connection string with some alternation in value

1 Like
7

Hi, i made the connection , now for the table name, how do i find the table name? do i have to create a table name using excel application scope ?
can you share any sample workflow, i tried all the uipath forum artifacts, nothing seems to work out for me…

TIA @reyaz

8

Table Name should be in your DataBase and all its column should match with DataTable(output of Read Range activity) that you want to store

1 Like
9

Hi @Santhosh_Hyd,

The .xaml Excel DB Connection.xaml (11.9 KB) can be added to your UiPath library and then it will make easy for you to connect excel to database whenever needed as I created something generic which relies in dynamic variable (excel file name, excel sheet name). It also gives you two SQL statement (select, update) to facilitate interacting with the file.

Additionally, you can refer to this link to better understand the syntax needed for querying and updating excel files with SQL.

2 Likes
10

Hi @brunoazev Thank you for posting a solution to this problem. I’ve run your .xaml and the following error occured:

Message: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
Exception Type: System.Data.Odbc.OdbcException

Can you help me please with this? :slight_smile:

11

Sorry @Tode, I have never faced that issue before. You have some different links on the internet about the issue that may give you some hints.

12

I was able to read the data from excel to datatable using dsn connection. How to update the sheet in excel with the data table? Can we achieve this using this dsn?

1 Like
13

Absolutely. You just need to use execute non-query instead of execute query and pass your update command.

14
  1. Can you provide some update query examples? Also Insert Into examples?
  2. Can we Update specific excel sheet rows? Like From A5:X5 to A10:X10 exlcuding top four rows?
15

Here is a detailed article on that :slight_smile:

16

Hi I am trying use excel as a database. I am facing error like provider is not registered in local host like that. Can anyone help me.