05-13-2024 08:50 AM
Hi,
I am using the LoadCustomTableUsingExcel function to load an Excel file to an application table. The function seems to be working fine in most cases. However, I am having issue with tables having unique constraints.
The table has Payroll, PeopleGroup and Amount columns. The unique constraint was added because users can also insert into the table using an SQL editor component, so we want to avoid them do be able to insert duplicated rows.
When trying to load a row which already exist in the table using the LoadCustomTableUsingExcel function I get the following error (example): “Column ‘Payroll, PeopleGroup' is constrained to be unique. Value 'Payroll_AE, PplGroup001' is already present.”
This issue only appears when loading in Merge. Replace works fine. Normally (with table not having a unique constraint) it is not an issue loading rows that are already in the application table because the Merge tag in the Excel file make so that only new rows are added to the table while rows already present are ignore and not duplicated (as you would expect from a merge).
Thank you for any suggestion you can give me
Solved! Go to Solution.
05-13-2024 07:59 PM
Hi Andrea,
I don't know much about this function as I haven't used it (here are some other .net native functions to load Excel files that I have used)
Regardless, I think the simplest option might be to load your data to a staging table and then insert/update/merge the rows from there to your main table. The MERGE sql statement sounds like it would be your friend here. This would allow you to maintain your constraints and not have to change your existing process significantly (just a different target table for the LoadCustomTableUsingExcel and an SQL statement execution.
- Daniel
05-13-2024 10:20 AM
Hi Andrea,
Interesting, a workaround you can maybe consider is to disable the constraints prior to executing the LoadCustomTableUsingExcel function and re-enable the constraints after the load has been completed?
Thanks,
Fred
05-14-2024 06:02 AM
Thank you for your suggestion. This can be risky: if there are duplicates in the file users are trying to load, these are going to be loaded since the constraint is disabled and then we will get an error when trying to re-enable the constraint
05-14-2024 08:14 PM
And users could load data manually while constraints are disabled
05-13-2024 01:13 PM
@AndreaF - It is working as expected.
1. If you have a constraint on a column and trying to merge with same value will throw an error. Since it is trying to merge with the same value.
2. Replace is working because it will clear and trying to reload so there are no errors.
I would suggest creating a condition to check the File value is already present in the table if so, do not add the row else add it.
Hope this helps.
05-14-2024 06:40 AM
Hi, thank you for your comment. I don't think it is an expected behaviour: if I make an exact copy of the table, same columns, same constraints, same data, just different table name, and then merge one table into the other using the SQL merge statement, I don't get any error. I would have expected the Merge with the LoadCustomTableUsingExcel function to do the same.
In any case, where do you think the check you suggest should be placed?
05-13-2024 07:59 PM
Hi Andrea,
I don't know much about this function as I haven't used it (here are some other .net native functions to load Excel files that I have used)
Regardless, I think the simplest option might be to load your data to a staging table and then insert/update/merge the rows from there to your main table. The MERGE sql statement sounds like it would be your friend here. This would allow you to maintain your constraints and not have to change your existing process significantly (just a different target table for the LoadCustomTableUsingExcel and an SQL statement execution.
- Daniel
05-14-2024 06:52 AM
@DanielWillis what are the functions you usually use to load Excel files?
Thank you for you comment, that would work, although I am not sure I want to add that extra complexity to the solution. Perhaps I will simply explain to the users the merge template should only have new rows and will not accept rows already present in the table...
05-14-2024 08:19 PM
I don't think its really complex. It'd probably be easier than teaching the users.
If you have multiple users loading files, to avoid them running into each other you might want to add columns for WFName/Time/Scenario and just delete from staging table based on those rather than clearing the whole table.
05-15-2024 09:44 AM
Hi, I have decided to go with the solution you suggested. One thing to notice is that by loading to a staging table the Replace option in the Excel template does not work anymore, so I needed a way to load to the Main table when the load method is set to Replace, and to the Staging table when the load method is set to Merge. I have achieved this with a simple condition in the Excel file itself: