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

AndreaF's avatar
AndreaF
Contributor III
2 years ago
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...
Extenders
System
  • DanielWillis's avatar
    DanielWillis
    2 years ago

    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

FredLucas's avatar
FredLucas
Icon for OneStream Employee rankOneStream Employee
2 years ago

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's avatar
    AndreaF
    Contributor III
    2 years ago

    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's avatar
      DanielWillis
      Valued Contributor
      2 years ago

      And users could load data manually while constraints are disabled