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

Marco's avatar
Marco
Contributor II
2 years ago
Solved

How can I modify the LoadCustomTableUsingExcel function to prevent it from adding duplicates?

Hi Everyone. Is there a way to prevent the insertion of duplicate information using that function? Dim selectionChangedTaskResult As New XFSelectionChangedTaskResult() selectionChangedTaskRe...
System
  • RobbSalzmann's avatar
    RobbSalzmann
    2 years ago

    You will have to manage the detection of and how to deal with duplicates.  If managing it in the Excel file doesn't work, because you're also dealing with the possibility that even though its not a dupe in the excel file, the data may already be in your sql table from some other excel file or process.

    If that's the case, and your very limited amount information keeps us guessing, then deal with it in your code using SQL commands.  e.g. 

    INSERT INTO YourTable (Column1, Column2)
SELECT 'Value1', 'Value2'
WHERE NOT EXISTS (
    SELECT 1 FROM YourTable WHERE Column1 = 'Value1' AND Column2 = 'Value2'
);

     

Marco's avatar
Marco
Contributor II
2 years ago

Hi Robb, 

I’m referring to the values within the Excel file. For example, in an Excel file, I have a list from 1 to 10, but in the table, I have 1 and 10. So, what I’m trying to do is to prevent 1 and 10 from being added again.

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

Unfortunately there is a slight inconsistency between LoadCustomTableUsingExcel and LoadCustomTableUsingDelimitedFile - the latter has a parameter to specify the load method (e.g. Replace) which would allow us to avoid duplicates, but the former has not. You can file a request on IdeaStream to change this, but I don't know what the chances of a speedy addition would be.

These utilities were mostly meant for one-off initialization of Marketplace solution tables, rather than repeated loads; the expectation was that you'd manage further additions to custom tables in different ways, e.g. through a Table Editor or some custom process that will check records before inserting them.