The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
LoadCustomTableUsingExcel function not working when loading to table having unique constraints
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 havi...
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 - 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.
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?