Read a .csv file from a file in filestore everyone folder and convet to datatable in a BR

[Nikpowar97]

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.

[sudarshan]

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