How to Read Excel File in Uipath

• Excel Automation Using UiPath

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android, and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms.

Basic Functioning of Microsoft Excel :

Microsoft Excel has the basic features of all spreadsheets, using a grid of cells bundled in numbered rows and letter-named columns to organize data manipulations like arithmetics operations.

The Microsoft excel can display information as line graphs, histograms and charts, and with a very limited three-dimensional graphical display. It allows sectioning of data to view its dependencies on various factors for different perspectives (using pivot tables and the scenario managing director).

Basics of Automation in UiPath Studio

The post-obit case demonstrates how to create an excel file and write something in information technology.

Create a process called Excel_Automation_Process_Example

One time the Process opened in the UiPath Studio, Install the Excel-related Package into your Process. Search for Excel package in the Official under Manage Packages.

Click on the UiPath.Excel.Activities. If it is already installed, then bank check for the new version to update and and then click on the Save push.

Now, you lot tin can see a lot of activities nowadays in the Excel activities pane.

Drag and drop the sequence into the Process. All the excel related activities are work under Excel Awarding Scope only. Then whatsoever the activities you are going to work should be nether the Excel Application Telescopic.

Next, add the Excel Awarding Telescopic activity inside the sequence, which actually creates an excel file in the UiPath directory. Enter the name of the excel file you want to create, and I am creating readexcel.xlsx, as shown below.

Once you relieve and run the sequence, the readexcel.xlsx file has been created in the UiPath directory with no data in it.

Next, add Write Jail cell action within the sequence, to write inside the excel sheet we have to enter the sheet name and jail cell proper name in the Write Cell activity every bit shown below.

Next, add ane more Write Jail cell activity inside the Excel Awarding Scope activity and enter the details as shown below.

Salve and run the sequence. Subsequently the execution, open up the excel file, you lot can see that the excel file contains 3 columns with the column proper name as Name, Email id, and Telephone Number.

PDF Automation by Using UiPath Studio

Create a new sequence called Read_Excel_Example.

Add the Excel Application Scope inside the sequence and add the path of the excel file which you want to read. I am going to read the readexcel.xlsx file.

Adjacent, add Read Range Activity inside the application scope. The Read Range activity going to read the value of an Excel range and stores it in the DataTable variable

• If the Range is non mentioned, the Read Range Activity is going to read All the information from the spreadsheet.
• If the Range is specified as sheet1, then Reads only the Sheet1,
• If the Range is specified as from A1 to C1, It volition read from A1 prison cell to C1 cell(with given canvas name)

I am going to read the complete sail, so I am not going to mention whatsoever prison cell name here.

The data will be stored in a data table, and so create a data table variable as Excel_DataTable_Variable in the output department nether the property pane. Once you click on the Variable by selecting Read Range activeness, you will see an Excel_DataTable_Variable.

Ready the scope of the Excel_DataTable_Variable variable to global(Read_Excel_Example)

Add together For Each Row Activity inside the sequence and and then enter the proper noun of the data type variable inside the text box and then add Write Line Activity inside the For Each activity and write the lawmaking to read the rows and columns of the excel sail equally shown below.

            row.item(0).ToString //*item(0) reads the data row wise from first column(Name)*// row.item(2).ToString//*item(i) reads the information row wise from 2nd column(Email id)*//          

Save and run the sequence, the Read Range activity is going to read content from the excel sheet except the header, So for testing purposes, I am entering Some data into the Excel Sail.

In one case you run the sequence, y'all will see the output in the output pane, where the data has been displayed row by row, as shown beneath.

Project Structure, Annotations, and Logs

In this example, we are going to read from 1 excel and write it to another excel. I am going to read from the readexcel.xlsx file which is as shown beneath.

I am going to create a new excel file writexcel_example.xlsx, to which I am going to write from readexcel.xlsx file, which is every bit shown below.

Now, create a new sequence chosen Write_to_Excel_Example.

Add Excel Awarding Scope activity into the sequence and mention the name of the excel file(readexcel.xlsx) from which you are going to read the data. Next, add the Read Range activity inside the sequence.

Utilize the Excel_DataTable_Variable in the higher up Read Range Activity and set the scope of the variable to global.

Next, add one More Excel Application Scope activity into the sequence and enter the name of the Excel file to which y'all are going to write. I am entering the proper noun of the excel equally Writexcel_example.xlsx.

Add Write Range Activity in the Excel Application Telescopic. Use the Excel_DataTable_Variable in the Write Range Action.

Now, save and run the sequence. In one case the sequence starts to execute, the content from the readexcel.xlsx has been written to the Writexcel_example.xlsx file.

Provisional Statements in UiPath Studio

Fix Colour to Cells in the Excel Canvas by using Set Range Color activity

• Create a new sequence called Set_Range_Color_Example
• Add Excel Awarding Scope activeness inside the sequence
• Mention the Excel file name yous want to alter the color of the cells in information technology
• I am using the readexcel.xlsx file hither.
• Next, add together Set Range Colour activity inside the Exercise container of Excel Awarding Scope activeness
  
• Click on the Set Range Color action and create a variable called Color_Variable in the Properties pane under the Color property.
  
  
• Adjacent, enter the cell range for the Range in the backdrop panel, I am selecting A1 to A4(Should be in double-quotes, delimited by a colon:)
• Next, select the SheetName in which canvas of excel file you desire to set the cell color(Should be in Double quotes)
  
• Next, click on the Set Range Color activity and select the variable type as organization.drwaing.color in the variable pane, as shown below.
  
• Now, go to the properties panel and click on the Color box and enter color., once you enter color. y'all volition detect the driblet-downwards with a list of colors, select your preferred color, and press enters, so click on the Ok push button.
  
• Now Salve the sequence and run. In the output, you can encounter that the color of the Cells from A1 to A4(1st Column) has changed to blue, every bit shown below.
  
• Past setting the Range in the properties pane, you can also change the color of the remaining cells with other colors.

Loops in UiPath Studio

The Go Prison cell Color activity Extracts the groundwork color of a cell and saves information technology every bit a Color variable. It can only be used within the Excel Application Scope activeness.

The following instance demonstrates how to extract the prison cell colour from the excel sheet.

I am creating a new excel file called as shown below, which contains a background color in each cell. Nosotros can extract the background color of the cell by using Go Cell activeness.

Create a new sequence called Get_Cell_Color_Example as shown beneath and and so add Excel Application Scope action inside the sequence and enter the path of the Fruit_List.xlsx inside the activity, every bit shown beneath.

Next, add Go Prison cell Colour Activity inside the do container of the Excel Application Telescopic activeness. Yous volition find the default canvas name and prison cell name every bit Sheet1 and cell name equally written in double-quotes.

I am going to proceed the Sheet1 as it is and changes the cell proper noun to A2, where A2 is the proper name of the cell, which I want to extract from the background color.

Click on the Become Prison cell Color activity and create a variable in the Backdrop pane for Color belongings nether Output. I am creating a variable chosen Cell_Color_Variable.

By clicking on the variable pane, set up the telescopic of the variable to Get_Cell_Color_Example, every bit shown beneath.

Next, add write cell action inside the sequence and and then enter the sheet proper noun as Sheet1 and Prison cell name as B2. The prison cell proper noun B2 represents the cell where you want to extract the groundwork-color. And join the variable name to shop the name of the color in the action, every bit shown below.

At present save and run the sequence. Afterwards execution, the background color of a Banana cell has been extracted equally yellow, as shown below.

Creating an Automation Procedure in the UiPath Studio

The Read cavalcade activity is going to read the content of the column with a mentioned range.

Create a new sequence called Read_Column_Example

Next, add the Excel Application Scope activity inside the sequence and enter the excel file proper noun in which yous want to read the cavalcade. Here I am going to read the readexcel.xlsx file which is as shown below.

Adjacent, add Read Cavalcade activeness within the sequence and enter the sheet name and column which you want to read. I am coming Sheet1 and cell proper noun equally C1

Create a new variable to shop the output by clicking on the Read Column in the Properties pane. I am creating a variable called Read_Column_Variable and gear up the telescopic of the variable to Read_Column_Example

Next, add For each action to read range elements from the column and enter the details as shown below.

Now, save and run the sequence; afterward execution, the pop-upward message displays the Elements from the column Telephone Number.

Once you click on the Ok push button in the pop-upwardly message, it will display the adjacent element from that column.

Introduction and Installation of RPA UiPath

The Append Range activity adds the information stored in a DataTable variable to the end of a specified Excel spreadsheet. If the sheet does non exist, a new one is created with the name indicated in the SheetName field.

I take data in the excel file called Exceldemo.xlsx in. sheet1

And sheet2 every bit shown beneath.

I want to add these details to the existing excel file chosen LoginData.xlsx which contains the following details.

Now create a new sequence called Append_Range_Activity

Next, add Excel Application Telescopic activity inside the sequence, as shown below, and enter the excel file proper name(Exceldemo.xlsx) from which you want to read the data.

Side by side, add Read Range activity inside the Do container of the Excel Application Telescopic action and specify the sail name from which sheet you lot want to read the data. From Exceldemo.xlsx file, I have data in two sheets, namely Sheet1 and Sheet2. So enter the sheet name as Sheet1

Create a new variable to shop the information from the sheet past clicking on Read Ramge activeness in the Properties pane for DataTable. I am creating a variable chosen Sheet1_Variable.

Add one more Read Range activeness and enter the canvas proper noun and then create a variable for Sheet2 as Sheet2_Variabe.

Next, add Append Range activity inside the sequence and then add the Excel Filename(LoginData.xlsx) to which you want to suspend the information from exceldemo.xlsx and mention the Canvas name (Master Canvas) and enter the variable name

Every bit we are reading from 2 sheets from Exceldemo.xlsx, so add on more Append Range Activity and mention the File name and Sheet name equally shown beneath.

Now save and run the sequence. After execution, the Logindemo.xlsx contains the data from exceldemo.xlsx.

By using the Sort Table action, nosotros tin can sort the content of the excel sail according to user requirements.

I have an Excel file that contains the candidate attends, which is every bit shown below.

Let us sort the candidate omnipresence. I am going to sort the Solar day in accending order, Create a new sequence called Sort_Data_Table

Add the Excel Application Scope activity within the sequence and enter the excel file name in which you lot want to sort the data.

Next, add Sort Table activeness inside the Do container of the Excel Application Scope activity and enter the Canvas Proper name, Table Name, Column Proper noun.

Convert the excel sail data into a tabular array format :

Now, enter the Table proper name as Table1 and Sheet name as Sheet1 and so Column name every bit Day in Sort Tabular array activity.

At present, save and run the sequence, After the execution, The Day Content in the table has been sorted in the ascending society.

In the aforementioned way, nosotros can sort the Date also. For kickoff, you need to change the Engagement in the DD-MM-YY format.

To Covert the Date into DD-MM-YY format :

Subsequently the execution, the date in the data table has been sorted in the ascending gild.

Debugging in UiPath Studio

Create a New Process called Real-Time Examples

In the instance, nosotros are going to extract some details from invoices for that I have downloaded some of the sample invoices which are in the PDF format and saved within the Real_Time_Example project, nether the input folder.

The Invoice is having several fields such as Client name, vendor proper noun, Particular Description, Unit Cost, Quantity, and Total price. I desire to extract Invoice Number from these invoices.

Let us do this in a flowchart. Create a new flowchart chosen Invoice_Automation_example

• Add together Assign activity within the flowchart and create a new variable in the To field. I have created a new variable called. Var_PDF_Files
  
• Click on the Variables pane and set the variable blazon to Assortment of [T] and and so select String from Select Types wizard, considering it is going to contain the string values.
  
• Next, we have to access all the PDF files from the directory so, the formula is Directory.GetFiles(Path,"*.pdf") Enter this formula in the Assign activity in the VB expression field.
  
• Next, create a path variable in the Variable Pane and set the default value as Input.
  
• Side by side, connect the strat button with Assign activity by drawing a line from Start.
  
• Now nosotros want to iterate through all the PDF files so, add For Each Activity inside the flowchart and enter the Var_PDF_Files variable in it.
  
• Next, add together Read PDF Text activity to read the text from the file, inside the For Each action.
• To avail the Read PDF Text activity, commencement, we need to install the PDF activity from the Manage packages.
• Go to Manage Packages and search for PDF in the Official package.
  
  
• Next, click on Install and Salvage.
• Now add Read PDF Text activity inside the For Each Activity.
  
  
• Enter the File name equally the detail.tostring within the Read PDF Text activity
  
  
• Click on the Read PDF Text activity and create a new variable in the Properties pane for the Output variable property.
  
• Next, add together Message Box activity inside the flowchart and enter the output variable inside it.
  
• At present, relieve and run the sequence; after the execution, the pop-up message box displays the text from the PDF invoice.
  

The pattern of the PDF is the aforementioned for all the Invoices, and then you can easily write the automation for this. In the above image, the message box is displaying the invoice line past line. Now yous know that in which line Invoice number is present.

Now we accept to extract only the required data from the Invoice, I want to extract the Invoice Number, which is nowadays in the second line.

To become merely the required field we accept a role called Split(), Where split up is a role that can split a string into multiple strings.

Example.ii: Splitting Invoice into Line by Line:

• For this example, I am using the Following 3 invoices.
  
  
  
• From these invoices, I wanted to extract From, Customer and Email. For that, I will create a new folder by the proper name-calling Output in the same project file. And creating a new excel file called Invoice.xlsx in it.
  
• Allow us try to print email id from all the invoices in the popular-upwardly message box, For that add an Assign action within the sequence and create a new variable called id.
• And prepare the variable blazon of id to String and write the function to split up the e-mail, we accept email id in the 7th line (the line count will commencement from zero(0), while counting, you take to consider the blank line too)
  

                  Divide(pdf2text,Environs.NewLine)(7)​              

  
• Now save and run the flowchart, after the execution, the popular-up message will exist displayed.
  
• Add together one more Assign activeness inside the flowchart and try to get From and To . Write the office to split the From and To from the invoice.
  

                  Split(pdf2text,Environment.NewLine)(half-dozen)​              

• At present salve and run the flowchart, Later on the execution, the popular-up message will be displayed. Here Sha is From and Client1 is To.
  
• Here From and To details are in one line and then let us separate them by using space.
• Add Assign activity within the flowchart and create a new variable(I have created a new variable called fromcompany) and so write the office to divide them as shown below.
  

                  Split(FromTo," ")(0)​              

• And besides add together ane more Assign activity into the flowchart and create a variable for Client every bit Client but and and so write the split function as shown below.
  

                  Split(FromTo," ")(1)​              

• Now put the body of the For Each activeness into Endeavor Catch activeness.
• Select the For Each activity and press Ctrl+T and the whole For Each activity will be inside the Try-Grab cake.
  
  
• Click on the Exception and select System.Exception
  
  
• Next, Add Write Line activity inside the Exception and write the text as follows.
  
• At present close the Try block and enter the variable name client within the Message box activity and save and run the flowchart.
  
• Now run the sequence, after the execution, the pop-upward message will display the client names equally follows.
  
• The next thing is nosotros accept to write this information into excel. But we cannot write this information directly into the Excel, permit us create a new sequence and invoke it.
• We are going to get the details of Invoice_Automation_Example into WritetoExcel.
• Add Invoke Workflow File activity within the flowchart and enter the file proper name to import the details.
  
  
• Adjacent, click on the import arguments and create new arguments and pass the value for information technology. Nosotros are passing the values from one flow to some other flow.
  

Instance.iv: Writing information into the Excel

• Create a new sequence called WritetoExcel
  
  
• Open the WritetoExcel sequence and here we have to receive the arguments by creating arguments in the arguments pane.

• Next, add together Excel Awarding Scope activity inside the sequence and enter the output file path.
  
• Next, add Write Cell activity into the Do container of the Excel Application Scope activity and enter the Variable name as shown below.
  
  
• Now Salve and run the sequence, Later the execution, you tin can see the details in the excel file.
  
• Yous can see that in the in a higher place excel sheet the details are overwritten, this is because we had given abiding values equally A2, B2, and C2.
• And then, let us increment the values.
• We can increase the value by creating a variable, go to chief workflow and add Assign activity between the first Assign activity and For Each loop.
  
• Create a variable in the Assign activity chosen Counterand then increase by 2.
  
• Add Assign activity inside the Invoke workflow file activity and so increase the counter value past 2 every time.
  
• Click on the Import arguments, and pass the counter-arguments as follows.
  
• Now, create an in_counter arguments in the Writetoexcel sequence in the arguments pane and set the datatype to Int.
  
• Next, change the constant values into every bit shown below in each Write Jail cell activeness
  

                  "A"+in_counter.ToString "B"+in_counter.ToString "C"+in_counter.ToString​              

• Save and run the sequence, later on the execution you will find the details in the excel file.
  

In the same way, we can perform May other activities using UiPath Automation.

• Closing Workbook
• Write Cell
• Delete Range
• Copy Sheet
• Become Workbook sheet
• Get Workbook Sheets
• Read Prison cell Formula...Etc..

0 results

dialwelyine.blogspot.com

Source: https://chercher.tech/uipath/excel-automation-rpa-uipath

Share this post