Free Support Forum - aspose.com

Number formatting during import

During import of a ‘;’ separated file (using workbook.open(“filename”, ‘;’)), all of the numbers seems to be strings in the spreadsheet. I.e. when I call workbook.Cells[i, j].DoubleValue, an exception is thrown stating, that the cell is a string value.

The culture is “da-DK”, and the numbers in the file is formatted like
,34E+04 using ‘,’ as the decimalpoint.

Can Aspose.Cells import a ‘;’ separated file where ‘,’ is used as a decimalpoint?

Hi,

Thanks for considering Aspose.

We will response you soon related to your issue.

Regards

Amjad Sahi

Aspose Nanjing Team

Please try this piece of code with attached sample file:

Workbook wb = new Workbook();
wb.ConvertNumericData = true;
wb.Open("d:\\test\\book1.csv", ';');

Console.WriteLine(wb.Worksheets[0].Cells[0, 1].DoubleValue);

Thanks for your response. I tried your code using your sample file, and it works perfectly. Big Smile [:D].

The numbers in the file, that I'm working with is formatted slightly differently - with no digits before the decimalpoint. I.e. if the contents of the csv file is:

Number; ,23
Test;123

Where your file was:

Number; 1,23
Test;123

The number ,23 is read as a string and not as a number. Is there anyway to make sure that ,23 is read as a number?

Hi,

Thanks for considering Aspose.

You may add a line to convert it to number format like:

wb.Worksheets[0].Cells[0,1].Style.Number = 3;

Regards

Amjad Sahi

Aspose Nanjing Team

Thanks for your quick response.

Basically this doesn't help, as the type of the cell will still be a string, i.e. it will not become a number.

Please try this attached version.

Hi,

Could you please post us your sample code with template file here so that we may sort out the issue.

Thanks

Regards

Amjad Sahi

Aspose Nanjing Team

Amjad may not see my post. Actually my attached version has already solved this problem. Please try it.

Sure

I'm sorry, but I can't figure out how to upload stuff, so here is the file I'm working with:

"Plotid: ";"ALLEPRIS";"Beregning:";"2006_M_1";"Beregningstidspunkt:";"2006.10.13-12:33";"Programversion:";"2002.01.17";
"Dataudtrk ";"Periode:";"2006.01.01";"2006.12.31";"X-akse:";"Timer";
"PRIS_EL ";"PRIS_VARME ";"EL_ALLE ";"EL_FYV3 ";"EL_FYV7 ";"EL_SSV3 ";"EL_SSV4 ";"EL_NJV2 ";"EL_NJV3 ";"EL_ENV3 ";"EL_SKV3 ";"EL_ESV3 ";"EL_HEV ";"FJV_ALLE ";"FJV_FYV3 ";"FJV_FYV7 ";"FJV_SSV3 ";"FJV_SSV4 ";"FJV_NJV2 ";"FJV_NJV3 ";"FJV_ENV3 ";"FJV_SKV3 ";"FJV_ESV3 ";"FJV_HEV ";"OMK_ALLE ";"OMK_FYV3 ";"OMK_FYV7 ";"OMK_SSV3 ";"OMK_SSV4 ";"OMK_NJV2 ";"OMK_NJV3 ";"OMK_SKV3 ";"OMK_ENV3 ";"OMK_ESV3 ";"OMK_HEV ";"KUL_ALLE ";"GAS_ALLE ";"OLIE_ALLE ";"IMPORT_N ";"IMPORT_N_OMK ";"IMPORT_S ";"IMPORT_S_OMK ";"IMPORT_T ";"IMPORT_T_OMK ";"EKSPORT_N ";"EKSPORT_N_VAERDI ";"EKSPORT_S ";"EKSPORT_S_VAERDI ";"EKSPORT_T ";"EKSPORT_T_VAERDI ";"CO2 ";
"kr/MWh ";"kr/MWh ";"MWh ";"MWh ";"MWh ";"MWh ";"MWh ";"MWh ";"MWh ";"MWh ";"MWh ";"MWh ";"MWh ";"GJ ";"GJ ";"GJ ";"GJ ";"GJ ";"GJ ";"GJ ";"GJ ";"GJ ";"GJ ";"GJ ";"kr ";"kr ";"kr ";"kr ";"kr ";"kr ";"kr ";"kr ";"kr ";"kr ";"kr ";"GJ ";"GJ ";"GJ ";"MWh ";"kr ";"MWh ";"kr ";"MWh ";"kr ";"MWh ";"kr ";"MWh ";"kr ";"MWh ";"kr ";"kg ";
,315386E+03; ,112195E+03; ,137843E+04; ,000000E+00; ,294438E+03; ,750004E+02; ,250340E+03; ,000000E+00; ,323193E+03; ,000000E+00; ,101444E+03; ,261194E+03; ,728229E+02; ,539116E+04; ,000000E+00; ,149600E+04; ,000000E+00; ,155823E+04; ,000000E+00; ,549968E+03; ,000000E+00; ,573014E+03; ,543783E+03; ,513038E+03; ,652862E+06; ,000000E+00; ,131728E+06; ,930193E+05; ,119888E+06; ,000000E+00; ,120744E+06; ,584661E+05; ,000000E+00; ,114226E+06; ,128931E+05; ,123189E+05; ,258471E+04; ,768967E+04; ,000000E+00; ,000000E+00; ,000000E+00; ,000000E+00; ,797717E+03; ,194435E+06; ,947488E+03; ,520378E+06; ,473744E+03; ,261409E+06; ,000000E+00; ,000000E+00; ,185263E+07;
,276437E+03; ,172326E+02; ,128743E+04; ,000000E+00; ,291483E+03; ,750000E+02; ,249514E+03; ,000000E+00; ,239286E+03; ,000000E+00; ,975189E+02; ,261808E+03; ,728229E+02; ,540162E+04; ,000000E+00; ,149905E+04; ,000000E+00; ,155823E+04; ,000000E+00; ,549968E+03; ,000000E+00; ,580418E+03; ,543783E+03; ,513038E+03; ,197171E+06; ,000000E+00; ,479215E+05; ,120642E+05; ,388361E+05; ,000000E+00; ,308371E+05; ,239621E+05; ,000000E+00; ,342393E+05; ,746429E+04; ,116433E+05; ,105933E+04; ,184669E+03; ,000000E+00; ,000000E+00; ,000000E+00; ,000000E+00; ,797717E+03; ,183668E+06; ,947488E+03; ,507721E+06; ,473744E+03; ,255068E+06; ,000000E+00; ,000000E+00; ,113280E+07;

Here is the sample code, that reads the file:

Workbook workBook = new Workbook();

workBook.ConvertNumericData = true;

workBook.Open(fileName, ';');

// The following line fails

double x = workBook.Worksheets[0].Cells[4, 0].DoubleValue;

It seems to me, that the value is interpreted as a string, because the value begins with a , and not a digit.

Yours

Lars Hammer

Hi Lars,

Well It 's already been fixed and posted here... Don't you see an attachment (Aspose.Cells) three, four post before in this thread. If you did not find the fixed version please try the attached version(Aspose.Cells) to this post(message):

Regards

Amjad Sahi

Aspose Nanjing Team

This works perfectly well. Thank you for your quick response

Yours

Lars Hammer

I have found a new item. When I import ‘;’ separated file with european number formatting containing a number like: -,137329E-04, it is read as a string and not as a number.

Eg. all of the following numbers are read correctly, except the one above.

0,245315E+03;0,951376E+02;0,232638E+04;0,100000E+03;0,290111E+03;0,707882E+02;0,271602E+03;0,000000E+00;0,340952E+03;0,593146E+03;0,250063E+03;0,346499E+03;0,632205E+02;0,576140E+04;-,137329E-04;0,149200E+04;0,866422E+02;0,156748E+04;0,000000E+00;0,596756E+03;0,170502E+03;0,810926E+03;0,591700E+03;0,445389E+03;0,362797E+06;0,222927E+05;0,545110E+05;0,139121E+05;0,477120E+05;0,000000E+00;0,453344E+05;0,516556E+05;0,733579E+05;0,472274E+05;0,679400E+04;0,191449E+05;0,219998E+04;0,000000E+00;0,000000E+00;0,000000E+00;0,000000E+00;0,000000E+00;0,000000E+00;0,000000E+00;0,500000E+03;0,156147E+06;0,338328E+03;0,105596E+06;0,119669E+04;0,833208E+06;0,191671E+07;

Lars Hammer

No, I don't find your mentioned problem. Following is my code:

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("d:\\aspose.cells.lic");

Workbook excel = new Workbook();
excel.ConvertNumericData = true;
excel.Open("d:\\test\\csv.csv", ';');
excel.Save("d:\\test\\abc.xls");

Attached file includes:

1. New Aspose.Cells.dll. I add some other features but don't change anything on importing text file. However, you can try it.

2. Input file - csv.csv

3. Output file -abc.xls

To post an attachment, please click the option tab when reply to a post (not quick reply).