Create SQL Database from various Excel files

Help Activities
, ,
1

Hi everyone,

yesterday I discovered the magic of SQL Database in UiPath.
I had to read out a table with over 16.500 lines which would take much too long for the robot to read out as datatable.
So I used “Connect to database” and “Run query” activities, and it worked perfectly fine.

I am now wondering if it’s possible to create a database out of many Excel files that are stored in several folders?
The structure of each table is always the same, they are just in different folders, depending on year and customer.

Is there a way somewhere in here to make it possible?

image
image752×71 4.67 KB

This is just what I found on the internet, I don’t have any knowledge about SQL or databases whatsoever.

Thanks for your input!

2

@Juli

Yes you can definitely use it like that…

You can either update or select the data from excel using excel as database

Cheers

3

Hi again,

actually, I was trying ChatGPT and it gave me this answer:

Yes, it is possible to create an SQL database out of various Excel files that are in different folders using UiPath.
To do this, you will need to use UiPath activities to read the data from the Excel files and then write it to an SQL database. Here are the general steps you can follow:

  1. Use UiPath’s “Directory.GetFiles” activity to get a list of all the Excel files in the different folders.
  2. Use UiPath’s “Excel Application Scope” activity to open each Excel file and read the data.
  3. Store the data in UiPath’s “Data Table” variable type.
  4. Use UiPath’s “Database Connect” activity to connect to your SQL database.
  5. Use UiPath’s “Database Insert” activity to insert the data from the Data Table variable into the SQL database.
  6. Repeat steps 2-5 for each Excel file.

Another approach is also described:

If you have multiple Excel files that you want to use as data sources for your UiPath workflow, you can specify each file path individually in the connection string. One way to do this is to use a loop to iterate over a list of file paths and execute your database commands for each file in turn.

Haha… I will just run a loop for each file in (sub)folder, get the EXCELFILEPATHs, add them to a variable (str_sqlConnections) with the code surrounding each path ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source="+ EXCELFILEPATH +"; Extended Properties='Excel 12.0 XML;HDR=YES;ReadOnly=False';") - and in the end I can use the str_sqlConnections (consisting of like 100 files) for the Connection String field in the Connection configurations.

Would this be a correct approach?