0

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).

  • I've just updated the CSVWriter such that the first line is now Using lf As New IO.StreamWriter(CSVFile, True, Text.Encoding.GetEncoding("Windows-1252")) which seems to fix the issue, but I'm still keen to understand what's happening to ensure I'm not just hacking around something. – sasfrog Feb 22 '18 at 3:26
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.