LoadCustomTableUsingExcel function not working when loading to table having unique constraints

[AndreaF]

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

[FredLucas]

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

[AndreaF]

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

[DanielWillis]

And users could load data manually while constraints are disabled

[Krishna]

@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.

[AndreaF]

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?

[DanielWillis · Accepted solution]

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

[AndreaF]

@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...

[DanielWillis]

I don't think its really complex. It'd probably be easier than teaching the users.

1. Create a table duplicate of the other. We'll call it staging table
2. In your BR:
   1. Clear staging table
   2. Modify your LoadCustomTableUsingExcel call to load to staging table
   3. Execute SQL statement merging stage table into new table

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.

[AndreaF]

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: