11-20-2022 01:03 PM - last edited on 05-25-2023 06:03 AM by JackLacava
I want to retrieve data from a .csv file from Filestore\Everyone folder and later convert the data in to a a dataTable in an extensibility rule.
Does any one has a sample BR.
Solved! Go to Solution.
11-21-2022 05:37 AM
Hi,
You can try using this function
Public Sub ImportCSV(ByVal si As SessionInfo,ByVal FileName As String,ByVal TableName As String)
Dim sourceFolder As String = BRApi.Utilities.GetFileShareFolder(si,FileShareFolderTypes.FileShareRoot,Nothing) & "\System\Contents"
Dim fullPath = sourceFolder & "\" & FileName
Dim dt As New DataTable
Using reader As New FileIO.TextFieldParser(fullPath)
reader.TextFieldType = FileIO.FieldType.Delimited
reader.SetDelimiters(",")
Dim headers = reader.ReadFields()
For Each colName In headers
dt.Columns.Add(colName)
Next
While Not reader.EndOfData
Dim row = reader.ReadFields()
Dim content As New List(Of Object)
For Each item In row
content.Add(item)
Next
dt.Rows.Add(content.ToArray)
End While
End Using
'if you want to use the datatable use return dt
'if you want to write a csv file to the database
Using dbInfo As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
BRApi.Database.ExecuteActionQuery(dbInfo,"DELETE FROM " & TableName,True,True)
Using sqlObj As New SqlBulkCopy(dbInfo.ConnectionString)
For Each col As DataColumn In dt.Columns
sqlObj.ColumnMappings.Add(col.ColumnName,col.ColumnName)
Next
sqlObj.DestinationTableName = TableName
sqlObj.WriteToServer(dt)
End Using
End Using
End Sub
11-21-2022 05:37 AM
Hi,
You can try using this function
Public Sub ImportCSV(ByVal si As SessionInfo,ByVal FileName As String,ByVal TableName As String)
Dim sourceFolder As String = BRApi.Utilities.GetFileShareFolder(si,FileShareFolderTypes.FileShareRoot,Nothing) & "\System\Contents"
Dim fullPath = sourceFolder & "\" & FileName
Dim dt As New DataTable
Using reader As New FileIO.TextFieldParser(fullPath)
reader.TextFieldType = FileIO.FieldType.Delimited
reader.SetDelimiters(",")
Dim headers = reader.ReadFields()
For Each colName In headers
dt.Columns.Add(colName)
Next
While Not reader.EndOfData
Dim row = reader.ReadFields()
Dim content As New List(Of Object)
For Each item In row
content.Add(item)
Next
dt.Rows.Add(content.ToArray)
End While
End Using
'if you want to use the datatable use return dt
'if you want to write a csv file to the database
Using dbInfo As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
BRApi.Database.ExecuteActionQuery(dbInfo,"DELETE FROM " & TableName,True,True)
Using sqlObj As New SqlBulkCopy(dbInfo.ConnectionString)
For Each col As DataColumn In dt.Columns
sqlObj.ColumnMappings.Add(col.ColumnName,col.ColumnName)
Next
sqlObj.DestinationTableName = TableName
sqlObj.WriteToServer(dt)
End Using
End Using
End Sub