Names with a accent in SQLite

I am reading a csv file into a SQLite database.
The field “art_omschrijving” has a lot of names with an accent. But in all the names with a accent as “Caf, Itali”, that accent is replaced by the " " sign. (question mark on a black background).
Is this a problem off wrong encoding?
How can i prevent it?

 if myFile <> Nil then
    t = TextInputStream.Open(myFile)
    While Not t.EOF
      t.Encoding=Encodings.MacRoman
      aSamenstelling = Split(t.ReadLine,";")
      If CountFields(t.ReadLine,";") = 13 Then
        MijnRec.Column("art_code")=aSamenstelling(0)
        MijnRec.Column("art_omschrijving")=aSamenstelling(1)
        MijnRec.Column("art_leverancier")=aSamenstelling(3)
        MijnRec.Column("art_categorie")=aSamenstelling(4)
        MijnRec.Column("art_subcategorie")=aSamenstelling(5)
        MijnRec.Column("art_barcode")=aSamenstelling(6)
        MijnRec.Column("art_ap_excl")=aSamenstelling(8)
        MijnRec.Column("art_ap_incl")=aSamenstelling(9)
        MijnRec.Column("art_vp_excl")=aSamenstelling(10)
        MijnRec.Column("art_vp_incl")=aSamenstelling(11)
        MijnRec.Column("art_btw")=aSamenstelling(12)
        MijnDb.InsertRecord("artikels",MijnRec)
        If MijnDb.Error Then
          MsgBox MijnDb.ErrorMessage
        Else
          MijnDb.Commit
        End If
      End If
    Wend
  End If

It sounds like this is some multi-byte encoding like UTF8, not a single-byte encoding like MacRoman.

BTW, your code is skipping every other line. Is that what you wanted? (You call t.ReadLine for the Split and again for CountFields.)

Thans for the reply.
I changed my code, but the “strange signs” ar still there.
You are right. I was missing some lines.
I am new to Xoyo and still learning.

  if myFile <> Nil then
    t = TextInputStream.Open(myFile)
    While Not t.EOF
      t.Encoding=Encodings.UTF8
      aSamenstelling = Split(t.ReadLine,";")
      MijnRec.Column("art_code")=aSamenstelling(0)
      MijnRec.Column("art_omschrijving")=aSamenstelling(1)
      MijnRec.Column("art_leverancier")=aSamenstelling(3)
      MijnRec.Column("art_categorie")=aSamenstelling(4)
      MijnRec.Column("art_subcategorie")=aSamenstelling(5)
      MijnRec.Column("art_barcode")=aSamenstelling(6)
      MijnRec.Column("art_ap_excl")=aSamenstelling(8)
      MijnRec.Column("art_ap_incl")=aSamenstelling(9)
      MijnRec.Column("art_vp_excl")=aSamenstelling(10)
      MijnRec.Column("art_vp_incl")=aSamenstelling(11)
      MijnRec.Column("art_btw")=aSamenstelling(12)
      
      MijnDb.InsertRecord("artikels",MijnRec)
      If MijnDb.Error Then
        MsgBox MijnDb.ErrorMessage
      Else
        MijnDb.Commit
        ProgressBar1.Value=ProgressBar1.Value+1
      End If
    Wend
  End If

Where are you seeing the strange chars? In the debugger before you put them in to the database? Or after you pull them back out to display them? Be sure to DefineEncoding after you read the values back from the database before you display them.

I use SQLite Database Browser 2.0 to read the data and see the names with strange chars there.
When i use MacRoman and write the data from the csv file into a listbox then the names are correct and i see in the listbox “Caf, Itali” as it shout be.

  if myFile <> Nil then
    t = TextInputStream.Open(myFile)
    While Not t.EOF
      t.Encoding=Encodings.MacRoman
      sLijn=t.ReadLine
      Listbox1.AddRow(sLijn)
      aSamenstelling = Split(sLijn,";")
      MijnRec.Column("art_code")=aSamenstelling(0)
      MijnRec.Column("art_omschrijving")=aSamenstelling(1)
      MijnRec.Column("art_leverancier")=aSamenstelling(3)
      MijnRec.Column("art_categorie")=aSamenstelling(4)
      MijnRec.Column("art_subcategorie")=aSamenstelling(5)
      MijnRec.Column("art_barcode")=aSamenstelling(6)
      MijnRec.Column("art_ap_excl")=aSamenstelling(8)
      MijnRec.Column("art_ap_incl")=aSamenstelling(9)
      MijnRec.Column("art_vp_excl")=aSamenstelling(10)
      MijnRec.Column("art_vp_incl")=aSamenstelling(11)
      MijnRec.Column("art_btw")=aSamenstelling(12)
      
      MijnDb.InsertRecord("artikels",MijnRec)
      If MijnDb.Error Then
        MsgBox MijnDb.ErrorMessage
      Else
        MijnDb.Commit
        ProgressBar1.Value=ProgressBar1.Value+1
      End If
    Wend
  End If

I bet your browser is assuming (or set to) UTF-8 string. You could use ConvertEncoding on each aSamenstelling member to convert them from MacRoman to UTF-8 before storage. Or set your browser to MacRoman.

Honestly, it doesn’t really matter which encoding you use, as long as two criteria are met:

  1. The encoding can express all the characters necessary for your app.
  2. Data stored and retrieved in the same encoding.

That does it.
I changed my code and now the data are seen in SQLite Database Browser as “Caf”, “Itali”.
My browser is indeed set to UTF-8 string.
But why do I have to use MacRoman for the listbox and UFT-8 for the browser? When i use UTF8 for the listbox the names have the strange chars.
Thanks a lot!!

  if myFile <> Nil then
    t = TextInputStream.Open(myFile)
    While Not t.EOF
      t.Encoding=Encodings.MacRoman
      sLijn=t.ReadLine
      Listbox1.AddRow(sLijn)
      aSamenstelling = Split(sLijn,";")
      MijnRec.Column("art_code")=ConvertEncoding(aSamenstelling(0),Encodings.UTF8)
      MijnRec.Column("art_omschrijving")=ConvertEncoding(aSamenstelling(1),Encodings.UTF8)
      MijnRec.Column("art_leverancier")=ConvertEncoding(aSamenstelling(3),Encodings.UTF8)
      MijnRec.Column("art_categorie")=ConvertEncoding(aSamenstelling(4),Encodings.UTF8)
      MijnRec.Column("art_subcategorie")=ConvertEncoding(aSamenstelling(5),Encodings.UTF8)
      MijnRec.Column("art_barcode")=ConvertEncoding(aSamenstelling(6),Encodings.UTF8)
      MijnRec.Column("art_ap_excl")=ConvertEncoding(aSamenstelling(8),Encodings.UTF8)
      MijnRec.Column("art_ap_incl")=ConvertEncoding(aSamenstelling(9),Encodings.UTF8)
      MijnRec.Column("art_vp_excl")=ConvertEncoding(aSamenstelling(10),Encodings.UTF8)
      MijnRec.Column("art_vp_incl")=ConvertEncoding(aSamenstelling(11),Encodings.UTF8)
      MijnRec.Column("art_btw")=ConvertEncoding(aSamenstelling(12),Encodings.UTF8)
      
      MijnDb.InsertRecord("artikels",MijnRec)
      If MijnDb.Error Then
        MsgBox MijnDb.ErrorMessage
      Else
        MijnDb.Commit
        ProgressBar1.Value=ProgressBar1.Value+1
      End If
    Wend
  End If

I do not recall where in the documentation I read it, but UTF8 is the Xojo default encoding.

I do not have troubles with diacitical characters inside a ListBox.

Yeah, ListBox definitely handles UTF-8 correctly. Have you tried converting sLijn to UTF-8 before doing anything else with it?

You don’t. You could convert the data before inserting it into the listbox (and simplify your code quite a bit) and everything would be fine.

 t = TextInputStream.Open(myFile)
 t.Encoding=Encodings.MacRoman  // no need to do this inside the loop
    While Not t.EOF
      sLijn=t.ReadLine
      sLijn = ConvertEncoding(sLijn, Encodings.UTF8)  // do it all at once
      Listbox1.AddRow(sLijn)

Works fine!
Thanks a lot.