How Can I read the Sharepoint file's data using O365 and compare with SQL Datatable?

Help Activities
, ,
1

Hello,
(I want to eliminate data from Sharepoint excel file if it is already in SQL DB )
How Can I read data from Sharepoint Excel file ?
How Can I Compare sharepoint Excel file’s Data with SQL Execute Query Result ?

Thank You

2

@PALKUMARI_PATEL

Ypu can use excel related activities in office 365 package

https://docs.uipath.com/activities/other/latest/productivity/office365-activities#excel

Once they are in datatable get the data from sql also using database related activities and then can do comparision or can use queries with conditions to check if data is present…if none is returned then no data esle present in database

Cheers

3

Hi @Anil_G I am able to get both Datatable. Now the confusion is how can I use the comparison ? Can you provide me an sample of how can I do comparison ?

Thanks

4

@PALKUMARI_PATEL

What comparision do you want to do?

all columns?

or any specific?

provide some samples please

cheers

5

image

This screenshot from Excel. And I am getting few columns from SQL DB let’s assume Column A to compare with Num . I just want to check if the number is there in the Excel ? If it is there then I just want to exclude the number and want numbers without duplicate.

Thanks

6

@PALKUMARI_PATEL

you want non duplicates from db or excel(sp)?

Cheers

7

I want to check both DB (SQL and Excel) and just want unique values. Someone is updating Excel file manually so, bot just need to check the data on excel and have to exculde if any same values find from SQL. Those numbers from column A I am using in some other website to further process.

FYI- Excel has those values which are processes manually already. so, just want to exclude those and want unprocessed numbers only.

Thanks

8

@PALKUMARI_PATEL

Then perform a select columnname from Tablename…this will give you a datatable with all the values of column A

Now you have the data from excel already…

Use the below query to get the difference

arrayOfNum = Excel_dt.AsEnumerable.Select(function(x) x("Num").ToString).Except(db_dt.AsEnumerable.Select(function(x) x("ID").ToString)).ToArray()

This gives you an array of numbers which are not present in db table but are in excel

Hope this helps

Cheers

9

Hi @PALKUMARI_PATEL

Here’s a simplified example of how the workflow might look:

  • Excel Application Scope (SharePoint Excel)
    • Read Range (Read SharePoint Excel data into DataTable)
  • Connect (SQL Database)
    • Execute Query (Retrieve data from SQL into another DataTable)
  • For Each Row (SharePoint Excel DataTable)
    • Lookup Data Table (Check if the row data exists in SQL DataTable)
    • If Match Found:
      • Take appropriate action (e.g., update Excel, mark for deletion)
  • End For Each

Thanks!!

10

Thanks but I want all the numbers without duplicate. I have to use SQL DB but have to check if SQL number is in Excel ? If it is in Excel (meaning someone already processed that number) So, I have to exclude that number from both DB.

At last I just want DB with the data (no duplicates from excel and SQL).

11

Can you help me with that lookup and for each data ? I am not getting any activity to use in )365 for sharepoint.

12

I am not finding any sharepoint for each activity . Can you please help me with that ?