Import CSV with Numbers having Comma as Thousand Seperator

[ponejai]

Hi All,

I am trying to import a csv file which has a few number columns defined as text with Comma in values "12,356.34" which eventually is imported as Text string and not Number when trying to save as Excel.

Have tried all following flags to no avail.

application.DefaultVersion = ExcelVersion.Excel2013;

workbook.Worksheets[0].UsedRangeIncludesFormatting = false;

destinationRange.NumberFormat = "###.##";

destinationRange.NumberFormat = "0";

The output excel is getting "12,356.34" as text string in the output column Please suggest to get the numbers "12,356.34" as numbers 12356.34 in the columns.

thanks & best regards,

Pav

[Rahul Balasundaram]

Hi Pavan,

 

XlsIO supports detecting the number values while saving the CSV to an Excel workbook. This can be achieved by making the PreserveCSVDataTypes property as True before opening the CSV document.

 

Please go through the below link for more details regarding this.

https://www.syncfusion.com/kb/12733/how-to-preserve-csv-data-types-in-winforms-excel-xlsio-library

 

Regards,

Rahul.

[ponejai]

Thanks Rahul,

Yes It worked to a large extent, but that makes it largely correct but not all correct,  my problem is some of the cells still remain unaddressed and considered as text, which makes the process unreliable without a round of validation by human eye

[Rahul Balasundaram]

Pavan, we have confirmed the issue as "Number with thousand separators is preserved as text while resaving the CSV as XLSX in Indian culture" and logged a defect report. We will include the fix for this issue in our weekly NuGet release scheduled for March 7th, 2023.

 

You can track the status of this defect report through below feedback link.

https://www.syncfusion.com/feedback/41202/number-with-thousand-separators-is-preserved-as-text-while-resaving-the-csv-as

 

Disclaimer: Inclusion of this solution in the weekly release may change due to other factors including but not limited to QA checks and works reprioritization.

[Konduru Keerthi Konduru Ravichandra Raju]

Pavan, We have resolved the issue Number with thousand separators is preserved as text while resaving the CSV as XLSX in Indian culture and included the fix in our weekly NuGet release version 20.4.0.53. Kindly upgrade your Syncfusion packages to this version and let us know if the issue is resolved.

Latest NuGet Package: https://www.nuget.org/packages/Syncfusion.XlsIO.WinForms/20.4.0.53