Recent

Author Topic: Stringgrid vs. LoadFromCSVFile  (Read 48268 times)

asdf

  • Sr. Member
  • ****
  • Posts: 310
Stringgrid vs. LoadFromCSVFile
« on: June 01, 2012, 05:10:13 pm »
In MS Excel, I always select
delimiter = Comma
text qualifier = "
for example in csv file there are many commas like   58,073,560.23



StringGrid1.LoadFromCSVFile(OpenDialog1.FileName,',',true)

csv file ....

I t e m , D a t e , Q u o t a t i o n N o , A m o u n t
0 1 , 0 5 / 0 5 / 2 0 1 2 , Q T T 2 3 9 , " 3 , 6 2 4 , 9 9 7 . 3 1 "

StringGrid1 ...

I t e m D a t e Q u o t a t i o n N o A m o u n t
0 1 0 5 / 0 5 / 2 0 1 2 Q T T 2 3 9 3 , 6 2 4 , 9 9 7 . 3 1

It shows 1 character in 1 cell.

What should I do to solve this probem ?

Thank you in advance ...
Lazarus 1.2.4 / Win 32 / THAILAND

Bart

  • Hero Member
  • *****
  • Posts: 5275
    • Bart en Mariska's Webstek
Re: Stringgrid vs. LoadFromCSVFile
« Reply #1 on: June 01, 2012, 06:00:58 pm »
Which lazarus revision?

Bart

asdf

  • Sr. Member
  • ****
  • Posts: 310
Re: Stringgrid vs. LoadFromCSVFile
« Reply #2 on: June 01, 2012, 06:13:26 pm »
Version 1.1
2012/06/01
FPC 2.6.1
SVN Revision 37469
i386-win32-win32/win64

Regards,

 ::)
Lazarus 1.2.4 / Win 32 / THAILAND

Bart

  • Hero Member
  • *****
  • Posts: 5275
    • Bart en Mariska's Webstek
Re: Stringgrid vs. LoadFromCSVFile
« Reply #3 on: June 01, 2012, 06:42:44 pm »
Probably caused by my changes for r37318.

Problem: if you do not uses strictdelimiter := true, then all spaces are treated as delimters also.

Code: [Select]
var
  SL: TStringList;
....
  SL.Delimiter := ',';
  SL.StrictDelimiter := True;
  SL.DelimitedText := '01,05/05/2012,QTT239,"3,624,997.31"';

Gives:
Code: [Select]
01
05/05/2012
QTT239
"3
624
997.31"   

Can anyone compare to Delphi wether this is correct?

If so, we need to change the LoadFromCSVFile() and probably parse the strings ourself, since setting DelimitedText with either setting of StrictDelimiter will fail at some point.

@asdf: can you post a issue in the bugtracker, so it won't be forgotten?

Bart

JuhaManninen

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4459
  • I like bugs.
Re: Stringgrid vs. LoadFromCSVFile
« Reply #4 on: June 01, 2012, 07:11:53 pm »
This is a portion of TCustomStringGrid.LoadFromCSVStream:

Code: [Select]
      // Store the row data
      for i:=StartRow to RowCount-1 do begin
        Rows[i].Delimiter := ADelimiter;
        //do not allow #32 as separator in csv files
        Rows[i].StrictDelimiter := True;
        Rows[i].DelimitedText:=Lines[i-StartRow+j];
        //using StrictDelimiter means we have to handle quoted strings ourselfs
        for x := 0 to Rows[i].Count - 1 do
        begin
          S := Rows[i][x];
          if (Length(S) > 1) and (S[1] = '"') and (S[Length(S)] = '"') then
            Rows[i][x] := AnsiDeQuotedStr(S,'"');
        end;
      end;

The quotes are already handled by the code.
I think Stringgrid's CSV is compatible with itself, meaning it can always read the data it generated itself.
I don't know about compatibility with other tools.

Juha
Mostly Lazarus trunk and FPC 3.2 on Manjaro Linux 64-bit.

Bart

  • Hero Member
  • *****
  • Posts: 5275
    • Bart en Mariska's Webstek
Re: Stringgrid vs. LoadFromCSVFile
« Reply #5 on: June 02, 2012, 11:19:11 pm »
Quote from: juha
I think Stringgrid's CSV is compatible with itself, meaning it can always read the data it generated itself.

No it is not.
When saving a cell with a comma in the text, the contents are not quoted.
So when reading back, it will be read in as two fields...

We might try and comply with rfc 4180.
Amongst others it states (quoted from http://en.wikipedia.org/wiki/Comma-separated_values):
Quote
Fields containing a line-break, double-quote, and/or commas should be quoted

Clearly in our case, quoting a field which has a comma in it gives us wrong results upon reading.


Personally I'm surprised that setting DelimitedText with StrictDelimiter := True doesn't handle quoted fields, especially when quotes are there to "escape" fields that have delimiter as a literal in it.

Hence my question in my previous post: does Delphi do the same?

Bart

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Stringgrid vs. LoadFromCSVFile
« Reply #6 on: June 03, 2012, 01:18:26 pm »
@Bart:
I suppose the stringgrid export functionality uses SDFData export functionality?
Enabling the AllowMultiline property would help in quoting lines with embedded cr/lf/cr+lf:
http://bugs.freepascal.org/view.php?id=17285

The patch here:
http://bugs.freepascal.org/view.php?id=19376
could be adapted; it quotes embedded " (double quotes)... but not commas.

I think we need to investigate to what extent one can adapte the SDFdataset code to match RFC4180.
One of the problems seems to be that sdfdataset is not fully documented either, so we'd have to test with various Delphi versions.
If it can't totally be done, adding some options (like AllowMultiline) that allow both Delphi compatible sdf format behaviour and a robust RFC 4180 CSV format would be best IMO.

If the stringgrid code uses FPCSVExport - which I suppose it doesn't -, perhaps the patch in this issue can help:
http://bugs.freepascal.org/view.php?id=19759

Thanks.

Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Bart

  • Hero Member
  • *****
  • Posts: 5275
    • Bart en Mariska's Webstek
Re: Stringgrid vs. LoadFromCSVFile
« Reply #7 on: June 03, 2012, 01:42:30 pm »
Currently LineEndings cannot be part of Cells[] content AFAIK.

I will implement custom parsing of strings when reading from CSV file.

Bart

Bart

  • Hero Member
  • *****
  • Posts: 5275
    • Bart en Mariska's Webstek
Re: Stringgrid vs. LoadFromCSVFile
« Reply #8 on: June 03, 2012, 02:25:56 pm »
@asdf: can you try r37499?
I hope I fixed it.

Bart

asdf

  • Sr. Member
  • ****
  • Posts: 310
Re: Stringgrid vs. LoadFromCSVFile
« Reply #9 on: June 03, 2012, 03:17:37 pm »
Thank you so much, Bart  :D .

I will try and report to you on Tuesday.
 
Lazarus 1.2.4 / Win 32 / THAILAND

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Stringgrid vs. LoadFromCSVFile
« Reply #10 on: June 03, 2012, 03:35:24 pm »
Currently LineEndings cannot be part of Cells[] content AFAIK.
Then applying the setting won't hurt.
Edit: clarification: IIRC, if you don't use AllowMultiLine and you read a multiline file, the entire field structure will be messed up: fields will get the wrong data.
If you enable MultiLine, you'll get line endings in your data, which you could choose to filter out or, less preferably, ignore - e.g. specifying you only accept data generated by the stringgrid save to csv file functionality.

I will implement custom parsing of strings when reading from CSV file.
Yet another parsing routine... wouldn't be my preferred solution.. but you're the commiter...
« Last Edit: June 04, 2012, 09:06:20 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Bart

  • Hero Member
  • *****
  • Posts: 5275
    • Bart en Mariska's Webstek
Re: Stringgrid vs. LoadFromCSVFile
« Reply #11 on: June 04, 2012, 09:37:22 am »
Yet another parsing routine... wouldn't be my preferred solution.. but you're the commiter...

In principle I agree, I'ld like to see only one implementation, but I can't see how to use the proposed csvexport patches (database components) in a stringgrid?

Bart


BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Stringgrid vs. LoadFromCSVFile
« Reply #12 on: June 04, 2012, 09:51:04 am »
Bart,

Sorry, I probably wasn't clear enough. As I didn't know what stringgrid uses I suggested a couple of patches that might be useful.

Thanks.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Bart

  • Hero Member
  • *****
  • Posts: 5275
    • Bart en Mariska's Webstek
Re: Stringgrid vs. LoadFromCSVFile
« Reply #13 on: June 04, 2012, 02:28:25 pm »
Any help and suggestions are appreciated  :) :)

Bart

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Stringgrid vs. LoadFromCSVFile
« Reply #14 on: June 04, 2012, 03:24:02 pm »
Ok... we might be able to fix SetDelimitedText... but as you mention in the bug report, we'll need to test for Delphi compat.
IIRC, there was an FPC issue open for that as well.
Let me look...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018