Recent

Author Topic: [FIXED] CSV to XLSX Conversion  (Read 309 times)

rca

  • Jr. Member
  • **
  • Posts: 86
[FIXED] CSV to XLSX Conversion
« on: October 18, 2024, 03:20:28 pm »
I need to convert a CSV file to XLSX.

The content of the 'fruits.csv' file is as follows:
Code: Text  [Select][+][-]
  1. ITEM;REFERENCE;FRUIT;UNITS;PRICE
  2. 1;E015;PEAR;24;100
  3. 2;E024;APPLE;48;200
  4. 3;E012;ORANGE;24;100
  5. 4;E032;PINEAPPLE;12;150
  6. 5;E018;LEMON;24;80
  7. 6;E045;STRAWBERRY;24;120
  8.  


The problem I have is with the content of the 'REFERENCE' column:
* By default,
Code: Pascal  [Select][+][-]
  1. CSVParams.DetectContentType := true;
and when opening the converted file 'fruits.xlsx', it shows a value of zero. I attach image.

* When changing
Code: Pascal  [Select][+][-]
  1. CSVParams.DetectContentType := false;
  and when opening the converted file 'fruits.xlsx', it correctly shows the values, but now the cells containing numbers are full of warnings. I attach image.

I appreciate the help you can give me.
« Last Edit: October 18, 2024, 08:17:09 pm by rca »

wp

  • Hero Member
  • *****
  • Posts: 12416
Re: CSV to XLSX Conversion
« Reply #1 on: October 18, 2024, 04:36:34 pm »
The problem is that the procedure "val" (on which TryStrToFloat is based) assumes that a string like "E015" is a valid number. Delphi has the same bug while turbo Pascal does not... I modified the automatic number detection in FPSpreadsheet such that any string beginning with 'E' or 'e' cannot be a valid number - see current version on ccr.

If you use a release version from OPM you can fix it easily by yourself:
  • Open file "fpspreadsheet.pas"
  • Find procedure "TsWorksheet.WriteCellValueAsString"
  • Find the line with "TryStrToFloat" and replace it by this:
Code: Pascal  [Select][+][-]
  1.   if not (AValue[1] in ['E', 'e']) and TryStrToFloat(AValue, number, AFormatSettings) then
  • Save and recompile the IDE

rca

  • Jr. Member
  • **
  • Posts: 86
Re: [FIXED] CSV to XLSX Conversion
« Reply #2 on: October 18, 2024, 08:18:12 pm »
@wp

Thanks a lot!. Now is working.

 

TinyPortal © 2005-2018