Encoding extended ascii characters when opening as workbook

[Paul Meems]

I've got a stream coming from Azure Blob Storage which is a CSV file.

I open that stream using 

var workbook = _application.Workbooks.Open(stream);

and then I export the contents to a class:

var data = sheet.ExportData<T>(1, 1, usedRange.LastRow, usedRange.LastColumn, mappingProperties);

But when the CSV file contains extended ascii characters like Šašiæ the result is Šašiæ

Only a few extended ascii characters are read properly.

While testing with the original file I found this is working:

            using (var r = new StreamReader(filename, Encoding.GetEncoding(1252)))

            {

                _output.WriteLine(r.ReadToEnd());

            }

So I changed my Workbooks.Open as well:

var workbook = _application.Workbooks.Open(stream, Encoding.GetEncoding(1252));

But now 

var data = sheet.ExportData<T>(1, 1, usedRange.LastRow, usedRange.LastColumn, mappingProperties);

returns no data, because all data is in the first cell of each row. They are not separated.

I already set:

            _application.CSVSeparator = ";";

            _application.PreserveCSVDataTypes = true;

My question:

How do I read a CSV file with special encoding but still separate the data in several columns.

[Shamini Kiruba Sobers]

Hi Paul, 

 

Greetings from Syncfusion support. 

 

We are unable to reproduce the reported issue with the given information. Also, please try using Encoding.UTF8 in your sample once. 

 

The sample we tried (with dummy data) at our end can be downloaded from the following link. 

https://www.syncfusion.com/downloads/support/forum/159653/ze/F159653-1392147556 

 

Kindly modify the sample to reproduce the issue and share us the modified sample to analyze further on this. 

 

Regards, 

Shamini 

[Paul Meems]

Thanks for the quick reply.

When I use Encoding.UTF8 I don't get any data at all.

I've attached a small project with some sample data to reproduce.

Attachment: ReadExtendedChars_af7ab158.zip

[Shamini Kiruba Sobers]

Hi Paul, 

We are able to reproduce the reported issue and validating it currently. We will share the validation details in two business days on November 17^th, 2020. 

Regards, 

Shamini 

[Shamini Kiruba Sobers]

Hi Paul, 

 

We appreciate your patience. 

 

Kindly make use of the System.Text.Encoding.CodePages NuGet package and the following code snippet to resolve the reported issue. 

 

Code snippet: 

 

Encoding.RegisterProvider(CodePagesEncodingProvider.Instance); 

var workbook = application.Workbooks.Open(stream, ";", Encoding.GetEncoding(1252)); 

 

 

Please let us know if this helps. 

 

Regards, 

Shamini 

[Paul Meems]

Hi Shamini

I can confirm your solution works.

Thanks for the fix.

[Shamini Kiruba Sobers]

Hi Paul, 

 

We are glad that the solution helped you. 

 

Regards, 

Shamini