Data Scraping From A Website And Saving To SQL Server Using UiPath (RPA)

Introduction

In my previous article, we learned about Robotic Process Automation (RPA). We also saw the installation of popular RPA tool UiPath (free community edition on Windows machine) and we created a sample project in UiPath. If you are new to RPA or UiPath, please refer to this article to get the basic idea about UiPath and its working principles.

This article is in continuation of the previous article and we will learn about data scraping from a website and saving data to the SQL Server. I will fetch some user data from the C# Corner site and save it to different variables. Later, I will save these variables to the SQL Server database.

Data scraping or web scraping is the process of importing data from a website and saving it to your local system or any other device. It’s one of the most efficient ways to get data from the web, and in some cases to channel that data to another website.

Create UiPath Project in UiPath Studio

You can open UiPath studio and choose project type “Process “
 
It will open a dialog box and you can give the name and description for the project. Click “Create” button to proceed further.
 
 
We can drag a “Sequence” activity from Activities pane to “Main” work area. “Sequence” and “Activity” are the main terms in UiPath. We will add more activities to this "Sequence" activity.
 
 

We can add “Input Dialog” activity to sequence. Please search for “Input Dialog” and drag to the sequence.

 
We can give “Title” and “Label” properties to this activity. This dialog will ask for user input while running the process. Please note in UiPath all the string values must be entered inside the quotation mark only. Integer values can be entered without quotations.
 
 
We can add a new variable and map this variable with our “Input Dialog” activity.
 
 

I have created the variable with “String” variable type and scope as “Sequence”. Please note I have given my C# corner user id as the default value. The default is not mandatory.

We can map this variable to “Input Dialog” activity. You can add this variable to “Result” property of “Input Dialog” activity.
 
 
We can add a new activity for opening a browser. For that, we will use “Open Browser” activity.
 
 

You can drag this activity next to “Input Dialog” activity. Give the C# corner URL to this activity. Please note I have concatenated the URL value with User Id variable so that “Open Browser” activity will open the web page of the C# Corner user which we will capture from the Input Dialog box.

Inside the “Open Browser” activity, there is again a “Do” sequence. We will add more activities to this sequence to fetch the data from the website.
 
 

We can define the browser type property. We can add one more variable to define the Browser. This new variable will be used to close the browser tab after we fetch the data from the website. The type of this variable must be “UiPath.Core.Browser”.

Choose the custom data type from drop down.

 
You can search for “browser”. It will filter the “UiPath.Core.Browser” type. You can choose this custom data type.
 
 
We have added the “Browser” variable now.
 
 
We can map this variable with our “Open Browser” activity. Go to the properties of this activity and set “BrowserType as “Chrome” and “UiBrowser” as “varBrowser”.
 

When our process starts, the browser will open in Google Chrome with the variable. Later, we will use this browser variable to close the Browser tab.

We are going fetch “User Name”, “Rank”, “Read”, “Reputation”, “Membership Type” from C# Corner website for a given user.
We can use the “Get Text” activity for that.
 
 
Drag this activity inside the “Do” sequence in “Open Browser” activity.
 
 

You can click the “Indicate element inside browser” link button to mark the position of data which we want to fetch from the website. Here I am going to fetch “User Name” from the C# Corner website.

You must open the C# Corner website for a user to mark the data points. I opened with my username.
 
 
 
We have successfully marked the username in the website.
 
 
We can add a variable and map this variable with the above text value.
 
 
 
We can select the properties of “Get Text” activity and map the variable.
 
 
 
We can repeat the above steps for “Rank”, “Read”, “Reputation” and “Membership Type”.
 
 
 
You can see that there are 5 variables mapped with all “Get Texts” activities.
 
 
You can create a table “CsharpCorner” in existing or new SQL Server database.
  1. CREATE TABLE [dbo].[CsharpCorner](  
  2. [Id] [bigint] IDENTITY(1,1) NOT NULL,  
  3. [UserId] [varchar](25) NULL,  
  4. [UserName] [varchar](50) NULL,  
  5. [UserRank] [varchar](25) NULL,  
  6. [UserRead] [varchar](25) NULL,  
  7. [UserReputation] [varchar](25) NULL,  
  8. [MemberType] [varchar](25) NULL,  
  9. [RecordCreatedOn] [datetime] NULL,  
  10.  CONSTRAINT [PK_CsharpCorner] PRIMARY KEY CLUSTERED  
  11. (  
  12.    [Id] ASC  
  13. ))  
  14. GO  
  15. ALTER TABLE [dbo].[CsharpCorner] ADD  
  16. CONSTRAINT [DF_CsharpCorner_CreatedOn]  
  17. DEFAULT (getdate()) FOR [RecordCreatedOn]   
We can add “Execute Non Query activity to our sequence. You can search for it.
 

Please note this activity will not find the default activity library. We can search for additional packages. Please click “Search in available packages” link

It will open another window and you can search for “UiPath.Database”. You will find a “UiPath.Database.Activities” package. Please select it.
 
 
You can install the library now. Please note that this will install this library to your current project only. If you create another project you will install the package again.
 
 

After installation is complete, you can click the “Save” button. This will download the dependencies to our current project.

We can again search “Execute Non-Query” in the search box.
 
 
We can drag this activity next to the most recently-added “Get Text” activity.
 
 
Click “Configuration Connection” to connect SQL Server database. Click Connection Wizard and choose “Microsoft SQL Server” as Data source and “.NET Framework Data Provider for SQL Server” as Data provider and click “OK” button to proceed.
 
 

You can choose the connection properties like Server name and authentication type.

Please select the Database inside which we already created one table.
 
 
You can click “OK” button to proceed.
 
We can click “Edit Query” button to add the SQL command. We will add a query to insert one record into SQL table with C# Corner user details. We already fetched data to 5 variables separately.
 
 
  1. "insert into csharpcorner (UserId,UserName,UserRank,UserRead,UserReputation,MemberType) values ('" +  
  2. varUserId +"','"+ varUserName +"','"+ varUserRank + "','"+ varUserRead + "','"+ varUserReputation + "','"+ varMemberType + "')"   
You must be very careful in creating expression. Concatenate strings with variables.

The above activity will insert a new record to SQL Server table with fetched data.

We can close the browser tab now. We can use the “Close Tab” activity to close the browser tab.
 
 

You can drag this activity next to "Execute Non-Query” activity.

You can map the previously created Browser variable with this activity so that it will close the browser tab after fetching and inserting the data to SQL Server.

 
We can add a “Message Box” activity to add an intimation after completing the entire processes.
 
You can drag this activity next to the “Close Tab” activity.
 
 
We can run the project now. The process will ask the User Id.
 
 
After successful completion, it will show the message dialog.
 
 
We can repeat this process for some other users also. Open the database in SQL Server management studio and we can see that all the processed data is available in the table.
 

Summary

In this article, we have seen how to fetch data from a website and save to the SQL Server database. For that, we have used many activities like “Input Dialog”, “Open Browser”, “Get Text”, “Execute Non Query”, “Close Tab” and “Message Box”. We have also seen how to install an external library and dependencies to the current project.

We will see more exciting features of UiPath in upcoming articles.