Extracting list data to CSV using CSOM - encoding issues

Question

I have a very basic CSOM-based app that fetches data from SharePoint lists and writes them to CSV.

The problem is that when users open the resulting file in Excel, instead of certain characters like apostrophes, they get strings like –. Looks OK in Notepad.

I'm aware this is an issue with encoding - evidently Excel is expecting ASCII and is misinterpreting the input. I'd really like to understand how and where to fix this.

To write the CSV, I use a function like this, which I'm aware under the hood uses UTF-8 encoding:

Public Sub CSVWriter(ByVal CSVFile As String, ByVal theString As String)
    Using lf As IO.StreamWriter = IO.File.AppendText(CSVFile)
        lf.WriteLine("{0}", theString)
        lf.Flush()
        lf.Close()
    End Using
End Sub

And call it like this:

CSVWriter("C:\My\File.csv",String.Format("{0},""{1}""", ID, Title))

Where Title is basically ListItem("Title"), after the list item has has been retrieved from SharePoint Online using ...GetItemByID(123).

Answer 1

Most of the time when I've run into this, it's because the data was copied from outside of SharePoint, like Word or Excel.

I usually have to run those characters either through a regex scrub or use something like Notepad++ and clean them out by hand.

Had to do something similar once with 10K+ rows, not fun.