Recent

Author Topic: csv file - ReadAsText  (Read 3928 times)

kjteng

  • Sr. Member
  • ****
  • Posts: 259
csv file - ReadAsText
« on: December 03, 2017, 05:51:22 am »
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

  • Sr. Member
  • ****
  • Posts: 259
Re: csv file - ReadAsText
« Reply #1 on: December 03, 2017, 11:06:04 am »
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

  • Hero Member
  • *****
  • Posts: 11858
Re: csv file - ReadAsText
« Reply #2 on: December 03, 2017, 11:50:25 am »
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 Lazarus
  • Find the implementation of function TryFractionStrToFloat
  • Replace it by the following code
Code: Pascal  [Select][+][-]
  1. function TryFractionStrToFloat(AText: String; out ANumber: Double;
  2.   out AIsMixed: Boolean; out AMaxDigits: Integer): Boolean;
  3. var
  4.   p: Integer;
  5.   s, sInt, sNum, sDenom: String;
  6.   i,num,denom: Integer;
  7.   slashCount: Integer;
  8. begin
  9.   Result := false;
  10.   s := '';
  11.   sInt := '';
  12.   sNum := '';
  13.   sDenom := '';
  14.   slashCount := 0;
  15.  
  16.   p := 1;
  17.   while p <= Length(AText) do begin
  18.     case AText[p] of
  19.       '0'..'9': s := s + AText[p];
  20.       ' ': begin
  21.              sInt := s;
  22.              s := '';
  23.            end;
  24.       '/': begin
  25.              sNum := s;
  26.              s := '';
  27.              inc(slashCount);        // avoid confusion with dates (2017/1/1)
  28.              if slashCount > 1 then exit;
  29.            end;
  30.       else exit;
  31.     end;
  32.     inc(p);
  33.   end;
  34.   sDenom := s;
  35.  
  36.   if (sInt <> '') and not TryStrToInt(sInt, i) then
  37.     exit;
  38.   if (sNum = '') or not TryStrtoInt(sNum, num) then
  39.     exit;
  40.   if (sDenom = '') or not TryStrToInt(sDenom, denom) then
  41.     exit;
  42.   if denom = 0 then
  43.     exit;
  44.  
  45.   ANumber := num / denom;
  46.   if sInt <> '' then
  47.     ANumber := ANumber + i;
  48.  
  49.   AIsMixed := (sInt <> '');
  50.   AMaxDigits := Length(sDenom);
  51.  
  52.   Result := true;
  53. end;

Next thing to consider is that the CSVParams must be set correctly for your file. This works for me:
Code: Pascal  [Select][+][-]
  1. program Project1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. uses
  6.   SysUtils, fpspreadsheet, fpstypes, fpscsv, fpsutils;
  7.  
  8. var
  9.   wb: TsWorkbook;
  10.   ws: TsWorksheet;
  11.   cell: PCell;
  12. begin
  13.   csvParams.Delimiter := ',';
  14.   //csvParams.DetectContentType := false;
  15.   csvParams.FormatSettings.ShortDateFormat := 'dd/mm/yyyy';
  16.   csvParams.FormatSettings.DateSeparator := '/';
  17.   wb := TsWorkbook.Create;
  18.   try
  19.     wb.ReadFromFile('forum-csv.txt', sfCSV);
  20.     ws := wb.GetFirstWorksheet;
  21.     for cell in ws.Cells do
  22.       WriteLn(Format('Row %d, column %d: %s', [
  23.         cell^.Row, cell^.Col,
  24.         ws.ReadAsText(cell)
  25.         ]));
  26.  
  27.   finally
  28.     wb.Free;
  29.   end;
  30.  
  31.   ReadLn;
  32. end.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: csv file - ReadAsText
« Reply #3 on: December 03, 2017, 02:25:46 pm »
(Y)  Thank you for the explanation and fix.

 

TinyPortal © 2005-2018