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]](/Community/emoticons/emotion-2.gif)
.
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).