Search code examples
excelvb.netcsvfilestreamstreamwriter

VB .Net when exporting to CSV issue when viewing in MS Excel

I have encountered something really weird. When exporting to CSV my top line shows the quotation marks yet the lines below down.

I use UTF8 encoding and manually add the double quotation marks to the value so that it is encased with quotation marks.

the code being used is

Dim fs As New IO.FileStream(GenericValueEditorExportFilename.Value, IO.FileMode.Create)
        Dim writer As New IO.StreamWriter(fs, Encoding.UTF8)
        fs.Write(Encoding.UTF8.GetPreamble(), 0, Encoding.UTF8.GetPreamble().Length)
.... 
.... 
....

While reader.Read
                If reader("TargetLanguageID") = targetLanguageID Then
                    writer.WriteLine(Encode(reader("SourcePhrase")) & ", " & Encode(reader("TargetPhrase")))
                End If 
.... 
.... 
....

Friend Shared Function Encode(ByVal value As String) As String
    Return ControlChars.Quote & value.Replace("""", """""") & ControlChars.Quote
End Function

the result when displayed in excel is shown as (https://ibb.co/ntMYdw)

when i open the file in Notepad++ the text is shown as below. But each line is displayed differently. Why is it that the 1st row displays them and the 2nd does not. Notepad++ result is displayed as (https://ibb.co/fMkWWG)

Solution

  • So the issue was being caused by the BOM that was created to manually set the encoding for the file as a start writing to the file.

    fs.Write(Encoding.UTF8.GetPreamble(), 0, Encoding.UTF8.GetPreamble().Length)

    Removing this resolves by issue and the file remains in the desired UTF8 encoding as it is set on the stream writer. so there is no need to add the BOM to set the encoding.