Update 2 different excel file fill to SQL database

Help Studio
,
1

Hi UiPath team,

Im quite new using the UiPath Studio.

Could anyone of you help me how to update using 2 different excel file to update the fill in SQL database?

Thank you and appreciate your help.

2

@Mohamad_Izudin_Bin_Abdul You mean, you want to check the database first and update it with new unique data?(Not add the duplicate ones)

Or you just want to add data from 2 excel files into database?

3

hi @AKM_Robinuzzaman ,thank you so much for your response on this.

yes,i would like to check the SQL database first and update it with new unique data based on excel file.

this is the SQL db data,i would like to update the virtual Account here based on paymentID.

image

and there will be 2 excel file with this data and the automation is to pick up the virtual account that have value and convert it to null(from SQL db) to value based on excel data
image

is that possible?

4

@Mohamad_Izudin_Bin_Abdul Yes that is possible.

  1. Connect to master database.
  2. Store database information into MasterDT(Datatable Variable)
  3. Read the excel sheet.
  4. Store the information into ExcelDT(Datatable Variable)

Now we need to check these 2 tables and find out the common "PaymentID"s

  1. Create CommonDT Variable(List → Datarow type variable)
  2. assing CommonDT = (From ExcelDT In ExcelDT.AsEnumerable
    Join MasterDT In MasterDT.AsEnumerable
    On ExcelDT(“PaymnetID”).ToString.Trim Equals MasterDT(“PaymentID”).ToString.Trim
    Select ExcelDT).toList

Now we have to check the CommonDT count to find and assign the new data in a new Datatable

  1. Use one if activity (Condition → CommontDT.count>0)
  2. if yes then assign the data in DataNotMatched (List ->Datarow type varibale)
    assign activity will look like this,
    DataNotMatched = ExcelDT.AsEnumerable.Except(CommonDT.AsEnumerable).ToList
  3. If no, then assign the in DataNotMatched = ExcelDT.AsEnumerable.ToList
  4. Now we need to create new datatable NewDT with column name PaymentID and VirtualAccount
  5. Use another if activity to check DataNotMatched.count>0
  6. If yes then use one for each activity
  7. Now for each item in DataNotMathed
  8. use add data row activity to add data in NewDT datatable.

Now we have the new items in NewDT datatable.

  1. Use insert query to add these data into database.

There could be another scenario where the VirtualAccount can be updated in excel. We might have update that information in the database.

In that case,

  1. We can use for each row for MasterDT datatable
  2. Another for each row for ExcelDT datatable
  3. Use one if activity inside it.
  4. Check if PaymentID of MasterDT = PaymentID of ExcelDT AND VirtualAccount of MasterDT = VirtualAccount of ExcelDT
  5. If Yes we will do nothing
  6. If no, we will use update query to update the information in database against that PaymentID

Hope this helps you.

Happy Automation :slightly_smiling_face:

2 Likes
5

@AKM_Robinuzzaman thank you so much for your feedback and reply,i will try it first. thank you so much sir

1 Like
6

Yes please try it first and see if everything works fine or not. Few things you can also find in forum for example, how to connect to database.

7

@Mohamad_Izudin_Bin_Abdul I hope these file will help you. There is some extra work where you might have to update the database if any virtual account was changed. Thats also added here. If the Sql queries are not working properly then you might be using different version. When its not working try them out in the workbench first.

Happy Automation. :slightly_smiling_face:
adding_to_masterDB.xaml (10.6 KB) comparing_database_entries_withNewOnes.xaml (21.5 KB) update_data_master_database.xaml (9.8 KB)

8

@AKM_Robinuzzaman hey Robin,thank you for your cooperation on this. looking forward on working with you more about automation for this. thanks ya!

1 Like
9

@Mohamad_Izudin_Bin_Abdul Did that work buddy?

10

hi @AKM_Robinuzzaman

i have encounter this during debugging.
do u hv any idea on how to put data instead of null.

thank you in advance sir

image
image1366Ă—681 28.7 KB

image
image793Ă—570 25.6 KB

11

@Mohamad_Izudin_Bin_Abdul , perhaps the sample datatable is empty inside this workflow. The sampleDT should be the datatable that you got from the database and should be passed inside this workflow as argument. Can you please check that?

Best,
Robin

1 Like
12

hi @AKM_Robinuzzaman when i execute im still having this issue as per image below,do you have any idea on how can i solve this issue? thank you

image
image711Ă—64 3.46 KB

13

@Mohamad_Izudin_Bin_Abdul , this error can appear when a user tries to insert a non-unique value into a table’s unique column such as the primary key.

14

image

i have another error appear :frowning:

1 Like