Values get converted to Scientific number in csv format

Aspose.Cells Product Family
1

hi team,

I am facing issue of values getting converted to scientific number in csv format.



Code example below,

Aspose.Cells.License licAsposeCells = new Aspose.Cells.License();

licAsposeCells.SetLicense(“Aspose.Total.lic”);



string filePath = @“D:\Hetal\Test_Scientific_Value.csv”;

Workbook workbook = new Workbook();

//int i = workbook.Worksheets.Add();

Worksheet worksheet = workbook.Worksheets[0];



DataTable dt = new DataTable();

dt.Columns.Add(“Account”, typeof(System.String));

DataRow dr = dt.NewRow();

dr[0] = “38817E70”;

dt.Rows.Add(dr);



worksheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, false, “m/d/yyyy”, true);



workbook.Save(filePath);



this.Close();



Thanks,

Hetal

2

Hi,


Thanks for your posting and using Aspose.Cells.

If you put apostrophe before your number than your number will be treated as string. For example, '123 will not be treated as a number but will be treated as a text.

Please try this code, it should fit your needs. Please read the comment inside the code. I have also attached the output csv file for your reference.

C#
Workbook workbook = new Workbook();
//int i = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[0];

DataTable dt = new DataTable();
dt.Columns.Add(“Account”, typeof(System.String));
DataRow dr = dt.NewRow();
dr[0] = “'38817E70”; //I have added apostrophe before your number
dt.Rows.Add(dr);

worksheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, false, “m/d/yyyy”, true);
workbook.Save(“output.csv”);


Another solution is that you set the last parameter of ImportDataTable() method to false and then your string will not be converted into number. Please see the following code and its output csv file for your reference.

C#
Workbook workbook = new Workbook();
//int i = workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[0];

DataTable dt = new DataTable();
dt.Columns.Add(“Account”, typeof(System.String));
DataRow dr = dt.NewRow();
dr[0] = “38817E70”;
dt.Rows.Add(dr);

//Here the last parameter is now false
worksheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, false, “m/d/yyyy”, false);
workbook.Save(“output1.csv”);


3

hi,
Thanks for the quick response. I tried both the approach but none worked.

Adding apostrophe also does not come in file when its csv. this works with xlsx but not csv.

Also, last parameter making false is not working. the output is still same.

I noticed that this works in xlsx but not with csv.

Thanks,
Hetal

4

Hi,


Thanks for your posting and using Aspose.Cells.

I have tested both of the above codes with the latest version: Aspose.Cells for .NET v9.0.9.0 and they work fine. I have also attached their output csv files. Please recheck. If you are still getting problem, I will share a sample console application project with you for testing.

However, I noticed that you might be having problem while loading csv file not while saving csv file. If you are loading csv file, then you should load it like this.

C#
TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.ConvertNumericData = false; //This parameter will enable you load numbers as strings

Workbook wb = new Workbook(“your.csv”, opts);


5

Hi,


I think you are using MS Excel to open the output CSV file where MS Excel automatically converts the string value to scientific notation (if possible). Please open the output CSV file (generated by the code segments provided by Shakeel Faiz) into notepad and you will see it is actually a string value (as you are inserting into DataTable’s field) instead of numeric value. In short, it is MS Excel’s behavior which does that conversion for a possible scientific value automatically when you load the CSV file into it.

Hope, this helps a bit.

Thank you.