1

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)

Grant
  • 33
  • 8
  • Answering questions on code I wrote about 5 years ago. Hmmmm – Sam Makin Oct 02 '17 at 11:45
  • hahah yeah i saw, this is baffling me. Only thing that was changed is the encoding and the solution that you sent below doesn't help. checked everything. Now I have added more data.. 1 column conforms to the " text encapsulation and the other doesn't. Microsoft for you >.< https://imgur.com/a/XH1OP – Grant Oct 02 '17 at 11:59
  • The decimal point in the first line, instead of a comma, is rather weird. – Hans Passant Oct 02 '17 at 12:33
  • its not a decimal point, its a comma. – Grant Oct 02 '17 at 12:41

3 Answers3

0

Excel is treating the first line as headers.

https://stackoverflow.com/a/24923167/2319909

Sam Makin
  • 1,526
  • 8
  • 23
0

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.

Grant
  • 33
  • 8
0

Something like this should work for you.

Dim str As New StringBuilder

For Each dr As DataRow In Me.NorthwindDataSet.Customers

     For Each field As Object In dr.ItemArray

     str.Append(field.ToString & ",")

     Next

     str.Replace(",", vbNewLine, str.Length - 1, 1)

Next



Try

     My.Computer.FileSystem.WriteAllText("C:\temp\testcsv.csv", str.ToString, False)

Catch ex As Exception

     MessageBox.Show("Write Error")

End Try
ASH
  • 20,759
  • 19
  • 87
  • 200
  • Thanks, but that wasnt the cause of the issue, it was the BOM at the start of the file which i was using to manually set the encoding of the file even through the stream writing was using UTF8. Iin terms of efficiency the string builder is better to use. – Grant Oct 06 '17 at 15:28