03-11-2024 05:22 PM - edited 03-11-2024 06:27 PM
Hi Everyone.
Is there a way to prevent the insertion of duplicate information using that function?
Dim selectionChangedTaskResult As New XFSelectionChangedTaskResult()
selectionChangedTaskResult.ShowMessageBox = True
Try
Dim loadResults As New List(Of TableRangeContent)
Dim fileInfo As XFFileEx = BRApi.FileSystem.GetFile(si, FileSystemLocation.ApplicationDatabase, strFilePath, True, False, False, SharedConstants.Unknown, Nothing, True)
If strFilePath.XFContainsIgnoreCase("xlsx") Then
loadResults = BRApi.Utilities.LoadCustomTableUsingExcel(si, SourceDataOriginTypes.FromFileUpload, "Temp.xlsx", fileInfo.XFFile.ContentFileBytes)
selectionChangedTaskResult.Message = "File [" & strFilePath & "] Loaded"
selectionChangedTaskResult.IsOK = True
Else
selectionChangedTaskResult.Message = "File [" & strFilePath & "] NOT Loaded"
selectionChangedTaskResult.IsOK = False
End If
'Delete the temp file
If boolDeleteTempFile Then BRApi.FileSystem.DeleteFile(si, FileSystemLocation.ApplicationDatabase, strFilePath)
Return selectionChangedTaskResult
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
Solved! Go to Solution.
03-14-2024 12:54 PM
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'
);
03-11-2024 06:19 PM - edited 03-11-2024 06:27 PM
You don't say what might be duplicated, cells? records, ranges, ?
LoadCustomTableUsingExcel returns a List of TableRangeContent.
TableRangeContent Has properties that indicate where the data is stored and is a copy of that data. I would look at parsing this return object collection or if its the only data in the table, the table itself for duplicates.
Each TableRangeContent has the following properties:
string RangeName
string DbLocation
string TableName
string FullLoadMethod
string FullLoadMethodSubstituted
string LoadMethod
string ClearDataCriteria
SortedDictionary<int, TableFieldTokenParts> FieldTokens
SortedDictionary<int, SortedDictionary<int, string>> FieldValues
SortedDictionary<int, string> InvalidFieldNames
List<ThreeStrings> InvalidDataRows
string RangeDefinitionErrorDescription
03-11-2024 06:31 PM
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.
03-12-2024 05:28 AM - edited 03-12-2024 05:28 AM
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.
03-11-2024 07:20 PM - edited 03-11-2024 07:20 PM
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
03-14-2024 12:47 PM
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.
03-14-2024 12:54 PM
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'
);