Problem CSV Text Encoding Excel

Targets Windows
1

Hi,
I have the following problem. I am working with Xojo 2019 3.1 Windows.
I would like to create a CSV file with Xojo. When I open them in the text editor, the special characters are OK. If I open them with Excel 2019, the special characters are not OK. See pictures.
What am I doing wrong?

[code]Dim file As FolderItem = new FolderItem(“C:\tmp\BAB.csv” , FolderItem.PathTypeNative)

Dim fileStream As TextOutputStream

If file <> Nil Then

dim BA as string = “101020”
dim ArtikelNr as string = “banoio® FZK”

fileStream = TextOutputStream.Create(file)

fileStream.WriteLine(“Babnr;Artikelnr;”)

dim txt as string

txt = BA + “;”+ArtikelNr+";"

fileStream.WriteLine(ConvertEncoding(txt, Encodings.WindowsANSI))

fileStream.Close

end if
[/code]

Thank you in advance for the help.

Norbert

2

Every IO needs its strings encoded properly. You need to check and test for the matching one. In your case it could be ASCII and/or Windows ISO Latin 1. Please check XOJO documentation

3

Hello Tomas,

I changed the program. It now tests all 95 Xojo encodings. Nevertheless, the correct encoding is not included.

Does anyone else have an idea?

[code]Dim file As FolderItem = new FolderItem(“C:\tmp\BAB.csv” , FolderItem.PathTypeNative)

Dim fileStream As TextOutputStream

//system.DebugLog Encodings.count.totext

If file <> Nil Then

dim BA as string = “101020”
dim ArtikelNr as string = “banoio® FZK”

fileStream = TextOutputStream.Create(file)

fileStream.WriteLine(“Babnr;Artikelnr;”)

dim txt as string

for i as integer = 0 to Encodings.count-1

var t as TextEncoding = Encodings.item(i)
txt = t.internetName + ";"+ArtikelNr+";"
fileStream.WriteLine(ConvertEncoding(txt, t))

next

fileStream.Close

end if
[/code]

Thanks Norbert

4

The first thing to do is to use a dedicated csv writer and not do the csv yourself.

The macOS version of Excel opens a csv file without showing the import dialog in Excel. When doing an import you can set the encoding of the file. See


The last time I checked the encodings in Excel was that Excel only supported the usual IsoLatin for Windows out of the box.

5

Nice bug you’ve found there Norbert.

This is the code you should be using:

[code]Dim file As FolderItem = New FolderItem(“C:\tmp\BAB.csv”, FolderItem.PathTypeNative)

Dim fileStream As TextOutputStream

If file <> Nil Then

Dim BA As String = “101020”
Dim ArtikelNr As String = “banoio® FZK”

fileStream = TextOutputStream.Create(file)

fileStream.WriteLine(ConvertEncoding(“Babnr;Artikelnr;”, Encodings.UTF16))

Dim txt As String

txt = BA + “;” + ArtikelNr + “;”

fileStream.WriteLine(ConvertEncoding(txt, Encodings.UTF16))

fileStream.Close

End If[/code]

However, this only works in 2019r1.1 and fails in 2019r3.1, in latest it writes out UTF8 with a two byte ® which is confusing Excel

If you want to get it working in the mean time in 2019r3.1 change to the following line:

Dim ArtikelNr As String = "banoio" + String.ChrByte(&hAE) + " FZK"

and your output should be fine, but bear in mind that when this bug is fixed you’ll need to drop back to the other code above.

Nightmare

6

Looks like its been known about since March 4th <https://xojo.com/issue/59351>

Looks like another workaround is to use BinarySteam instead of TextOutputStream

7

Or you could add the following after your TextOutputStream.Create to work around the bug:

fileStream.Encoding = Encodings.UTF16
8

Not sure if that applies to your problem as you are under Windows. But Excel 2019 on the Mac is able to open and import a CSV file with the following properties flawlessly:

  • Field separator depends on OS UI language: For German it is ;, for English ,
  • UTF8 with BOM (this is not supported in older versions like Excel 2011. For this use MacRoman)
  • where necessary, quote field with "
  • Line/Record ending CRLF
  • Line endings within fields / cells LF and of course quoted
1 Like
9

Well question is already marked as answered but in my humble opinion Libre Office CSV import is much more reliable than anything I’ve seen from Excel.