Forum Discussion

AndreaF's avatar
AndreaF
Contributor III
8 months ago

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
    8 months 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

DanielWillis's avatar
DanielWillis
Valued Contributor
8 months 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

  • AndreaF's avatar
    AndreaF
    Contributor III
    8 months ago

    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's avatar
      DanielWillis
      Valued Contributor
      8 months ago

      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's avatar
        AndreaF
        Contributor III
        8 months ago

        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: