csv file - ReadAsText
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
kjteng · 2017-12-03 04:51

I am using non visual components (tsWorksheet and tsWorkbook) to
access data from a csv file (test.csv).  Test file contain the
following data:
IT,PV0711,10/12/17,105
IT,PV0712,12/12/2018,504
IG,PV0714,8/12/2019,332
...

ws.ReadAsText(0, 2)   give me  12/17 instead of 10/12/2017
ws.ReadAsText(1, 2)   give me  16/1009 instead of 12/12/2018
ws.ReadAsText(1, 2)   give me   4/673 instead of 8/12/2019


What have I done wrong ?

----------------------------------------------------------------------
kjteng · 2017-12-03 10:06

After some trial and error, I think the above problem may be solved by
add the following line before reading the file
    CSVParams.DetectContentType:= False;

I am not sure what is the purpose of detectContentType. Can any one
explain?

----------------------------------------------------------------------
wp · 2017-12-03 10:50

DetectContentType (a member of the CSVParams) is some kind of
"insurance" against the case that fpspreadsheet cannot detect the
contenttype of a cell (i.e. string vs. text vs. date vs. number vs
fraction, etc.). Since the csv file format does not provide any meta
data fpspreadsheet makes some reasonable assumptions which of course
may be wrong sometimes. In these cases, DetectContentType can be set
to false, and fpspreadsheet will read the csv files into string cells.
Then the user - who should know about the structure of the file - can
convert the cells to the correct content type manually.

In your case the format detection confuses the dates with fractions:
The date "10/20/17" is interpreted as fraction 10/12.

I uploaded a new version to svn which counts the number of slashes -
if there's more than 1 then it cannot be a fraction!

If you don't use the trunk version of fpspreadsheet you can fix it
easily:
Open the unit fpsutils in LazarusFind the implementation of function
TryFractionStrToFloatReplace it by the following codeCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TryFractionStrToFloat(AText: String; out ANumber: Double;  out
AIsMixed: Boolean; out AMaxDigits: Integer): Boolean;var  p: Integer; 
s, sInt, sNum, sDenom: String;  i,num,denom: Integer;  slashCount:
Integer;begin  Result := false;  s := '';  sInt := '';  sNum := ''; 
sDenom := '';  slashCount := 0;   p := 1;  while p <= Length(AText) do
begin    case AText[p] of      '0'..'9': s := s + AText[p];      ' ':
begin             sInt := s;             s := '';           end;     
'/': begin             sNum := s;             s := '';           
 inc(slashCount);        // avoid confusion with dates (2017/1/1)    
       if slashCount > 1 then exit;           end;      else exit;   
end;    inc(p);  end;  sDenom := s;   if (sInt <> '') and not
TryStrToInt(sInt, i) then    exit;  if (sNum = '') or not
TryStrtoInt(sNum, num) then    exit;  if (sDenom = '') or not
TryStrToInt(sDenom, denom) then    exit;  if denom = 0 then    exit;  
ANumber := num / denom;  if sInt <> '' then    ANumber := ANumber +
i;   AIsMixed := (sInt <> '');  AMaxDigits := Length(sDenom);   Result
:= true;end;
Next thing to consider is that the CSVParams must be set correctly for
your file. This works for me:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
Project1; {$mode objfpc}{$H+} uses  SysUtils, fpspreadsheet, fpstypes,
fpscsv, fpsutils; var  wb: TsWorkbook;  ws: TsWorksheet;  cell:
PCell;begin  csvParams.Delimiter := ','; 
//csvParams.DetectContentType := false; 
csvParams.FormatSettings.ShortDateFormat := 'dd/mm/yyyy'; 
csvParams.FormatSettings.DateSeparator := '/';  wb :=
TsWorkbook.Create;  try    wb.ReadFromFile('forum-csv.txt', sfCSV);  
ws := wb.GetFirstWorksheet;    for cell in ws.Cells do     
WriteLn(Format('Row %d, column %d: %s', [        cell^.Row,
cell^.Col,        ws.ReadAsText(cell)        ]));   finally   
wb.Free;  end;   ReadLn;end.

----------------------------------------------------------------------
kjteng · 2017-12-03 13:25

(Y)  Thank you for the explanation and fix.