Best way to export collection to CSV

[naveed_raza]

Hi All,

What is the best to way to export collection to CSV in proper UTF-8 unicode compatibility.?

it should handle all double quotes , single quotes and commas between names and should be very reliable to handle thousands and lacks of records 

any suggestions and what are all multiple ways to do that ?

[naveed_raza]

Initially , i have exported data to Excel using MS Excel VBO and MS Excel VBO Extended object and saved the file as CSV using Workbook Save As. Its was saving the file in CSV format however it was not UTF-8 compatibility and when ever we open the file we are getting the pop up error.

[naveed_raza · Accepted solution]

here is the code which will export collection to CSV and this code will handle all double quotes, single quotes and commas between names

this code stage takes 2 input parameters (csv full file path and collection) and 2 output parameters (Success and error message)

Dim dt As DataTable = InCollection 
Dim filePath As String = csvFilePath

Try
	Success = True
	ErrorMessage = ""

	Using sw As New StreamWriter(filePath, False, System.Text.Encoding.UTF8)
		
		'Write headers
		Dim columnNames As String = String.Join(",", dt.Columns.Cast(Of DataColumn)().Select(Function(column) """" & column.ColumnName.Replace("""", """""") & """"))
		sw.WriteLine(columnNames)
 
		'Write rows
		For Each row As DataRow In dt.Rows

			Dim fields As New List(Of String)()
			For Each column As DataColumn In dt.Columns
				Dim value As String = row(column).ToString()
				'Escape double-quotes in values, always use double quotes around value
				value = value.Replace("""", """""")
				fields.Add("""" & value & """")
			Next
			sw.WriteLine(String.Join(",", fields))
		Next
	End Using

Catch Ex as Exception
	Success = False
	ErrorMessage = Ex.ToString()

End Try