Forum Discussion

Marco's avatar
Marco
Contributor II
10 months ago

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
    10 months 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'
);

     

RobbSalzmann's avatar
RobbSalzmann
Valued Contributor II

Since you can't pass a parameter that tells it not to load anything that already exists or will exist into the LoadCustomTableUsingExcel method, maybe its easier to sanitize the data at the source vs having the load process do it.  A simple excel macro can remove duplicates:

Sub RemoveDuplicatesFromRange()
    ' Change "A1:A20" to your desired range
    ActiveSheet.Range("A1:A20").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

 

Marco's avatar
Marco
Contributor II
10 months ago

What I wanted to resolve was the information that is in excel with the table to be inserted in Onestream, to avoid those duplicates in the table.

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    10 months 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'
);