Custom Search
Bookmark this on Delicious
Recommend to StumbleUpon
This tutorial will show you two ways to load a StringGrid from a CSV text file. It was written to explain one of the core elements used in a much bigger tutorial, vetting data in a CSV file. (The code should also work in Delphi.)
Full sourcecode provided, as usual. (A full listing of the sourcecode is given at the bottom of this page.)
This page is dedicated to the memory of the wonderful English actress Ann Emery, 1930-2016. I believe that her wonderful realization of the role of "Grandma", and the support she gave to all around her, especially the children, were large parts of the phenomenal success of the original London production of the musical "Billy Elliot".
You can see her as Grandma in the DVD of the show, "Billy Elliot Live".
I learned the sad news of her death while creating this page.
----------------------------------
Lazarus provides a way to load CSV files. I would presume that Delphi does too. (If it doesn't, I believe that my way of loading them would work there.) I'll show it to you.
So why write another? I wrote it because I was misled by something I read in an official reference, late September 2016. I've kept, and am promoting, what I've done because I don't like an aspect of the "easy" answer.
This might be a good time to download the zip file with the sourcecode for my CSV- to- StringGrid loader. It has a compiled .exe to play with.
That tries to treat what is in the file "TestDataLDN189.txt" as CSV, and to load it into the StringGrid on the form. ("TestDataLDN189.txt" should be in the same folder as the .exe. Changing the application to load a different file, from any location, would be trivial.)
Or you can just examine the sourcecode, below.
The application has two buttons, "Load CSV with SheepdogGuides routine" and "Load CSV with Lazarus routine". Click either one and what's in the StringGrid changes. If you want to see the results with different test data, edit what is in TestDataLDN189.txt. (Or tweak the code slightly, to let you choose a file.)
You will also find an "How To" guide, over at my Open Office tutorials, which covers using a CSV file consisting of multiple records to append those records to a database table.
A quick "naming of parts", before we go on...
A CSV FILE is a collection of RECORDs, which are each made up of one or more FIELDS.
Example of two RECORDs:
Owl, Bird, Vertebrate Monarch Butterfly, Lepidoptera, Insect, Arthropod
The first record has three fields, the second has four.
Within CSV files, one line equals one record. (What defines "a line" can be a problem in CSV work! Beware! I use Textpad (from Textpad.com) to create CSV files. Notepad (basic Windows application) works just as well.)
Within a record, a FIELD SEPARATOR indicates where one field ends and the next begins. The comma has traditionally been used for this (CSV derives from COMMA Separated Values), but other characters, including the non-"printing" TAB character (ASCII 9) are often used. The field separator in the examples is a comma.
Definition of field separator character:In the Lazarus approach, you pass the field separator character to the procedure. The SheepdogGuides version could have this feature added extremely simply. (Look at the code, search on "kFieldSep".)
------------------
Multiple field separator characters:In the SheepdogGuides version, only the specified field separator is treated as a field separator. In the Lazarus version, spaces also count as separators. (Subject to the separator-as-data considerations we turn to in a moment.) So, in the SheepdogGuides version, the following record has 4 fields, but in the Lazarus version it has 5, "Monarch" and "Butterfly" being deemed to be "separated".
Monarch Butterfly, Lepidoptera, Insect, Arthropod
------------------
Separator-as-data: There is a way around "the problem.... in the Lazarus version. With a bonus.
In the Lazarus version, the following has four fields...
"Monarch Butterfly", Lepidoptera, Insect, Arthropod
(The SheepdogGuides version, at 01 Oct 16, would also treat it as four fields, as it did before, but would include the quotation marks as part of the first field datum.)
Furthermore, the Lazarus version would even consider...
"Monarch Butterfly, Orange", Lepidoptera, Insect, Arthropod
... to consist of four fields. It would "ignore" the comma inside....
Monarch Butterfly, Orange
The SheepdogGuides version, at 01 Oct 16, would consider the record given to have five fields:
"Monarch Butterfly Orange" Lepidoptera Insect Arthropod
The SheepdogGuides approach is less flexible... and more predicable. I kept saying "at 01 Oct 16", because I am tempted to add the "enclose a thing in quotes, and it becomes one field, regardless of contents" feature, but it won't be happening any time soon.
There's a discussion of some of the whys and wherefors in the thread at http://forum.lazarus.freepascal.org which the link will take you to.
------------------
Number of rows: Both ways of reading the CSV resize the StringGrid to contain as many rows as there are records in the CSV file. But...
------------------
Number of Columns: The Lazarus version sets the number of columns to however many fields there are in the first record. If a subsequent record has more, the data in the extra fields is just thrown away. (With both versions, if a record has fewer fields than the StringGrid has columns, the corresponding cells are just left empty.)
The SheepdogGuides version, at 01 Oct 16, is weak in this department... in three ways...
1) The programmer determines the size of the StringGrid, at design time, in the LoadFileToStringGrid. If sgData.ColCount is set to 7, the StringGrid will have 7 columns.
2) If sgData.ColCount has been set to 7, and a record with 8 fields is encountered, the application will not respond well. An error message is presented to the user, and the best bet would be to terminate the application, revise the CSV (or code), and re-run. I THINK this can be "fixed"... that's what the "OR (bColIndexL>8)" in StringToSGRow is all about. ("String To StringGrid ROW, by the way. Not a subroutine about anything "growing".)
3) Because of the "OR (bColIndexL>8)" just mentioned, the SheepdogGuides version, as at 01 Oct 16, can't handle records of lots of fields. The "8" was picked more or less at random. It has to be larger than (maybe "equal to" okay" the number you assigned to sgData.ColCount. When this code is More Better, I suspect that the cure of one of these shortcomings will also cure the other. At the moment, the "problem" is not severe if while you are writing applications with the code you know basic things about the CSV it will be working with. But it is untidy, I know.
------------------
Trimming: What about spaces at the start and end of anything else between the field separators?
In Lazarus, the rules are a little complex, because, remember, a space is itself a field separator. But, A THE ENDS of a field value, they are just removed. Always. I think. ("A little complex"?"Monarch Butterfly, Insect" is three fields. Suppose there were three spaces between the Monarch and the Butterfly. Shouldn't that make the whole record four fields, the second one holding a space? No, of course not, your HUMAN logic says. But it took some clever programming to get the computer to... in this case... think like a human. And I dislike "clever programming". It is just easier to work with a "dumb" computer, not have it... sometimes... being clever.)
In the SheepdogGuides version, spaces can, throughout the field value, be "just another character", i.e. like a, b, c... 1, 2, %, $, etc... OR, if you wish, spaces before or after the rest of the field's value can be trimmed off. (In general, I think you will want to trim.)
In the SheepdogGuides version, just set the boTrimFields parameter of the StringToSGRow "String To StringGrid Row" procedure "true" to have the data arriving from the CSV "topped and tailed", as my Dorset "mother" referred to preparing string beans for the dinner table. Beware: If you DON'T trim, and have a record like...
Aphid, Bug, Arthropod
... (that has a space after each comma, as is common practice in typing text)... then if you say...
if (second field value)='Bug' then showmessage('hi');
... it won't show "hi". Without trimming the second field's value would be "SPACE-B-u-g". Dumb computer. But CONSISTENT, and easily predictable computer.
------------------
No datum vs datum is "nothing": Here's a brain bender for you. It is along the lines of the old "if a man says "I can stop working now" five miles from the nearest woman, is he still wrong?"
What is the difference between a bank account for Joe Bloggs which has nothing in it, and the situation where Joe doesn't have the account? Quite significant, when you put it like that!
This comes up because when you "fill" a 4 column string grid with the fields in...
Owl,Bird,Vertebrate
... the last cell will be empty because you didn't put anything there.
... BUT!... If the record was....
Owl,,Bird,Vertebrate ^ notice: two commas
... then you would be saying "Put Owl in the first cell, NOTHING in the second cell, Bird in the third, and Vertebrate in the last.
Sometimes a cell is empty because nothing was put there. Sometimes it is empty because what was put there was "nothing". -*!*- (Re-read that, think about it!)
Arcane as it sounds, it matters.
For instance... what if the record is...
Owl,Bird,Vertebrate,
The above record would be treated by both methods of loading CSV to a StringGrid as having FOUR fields, even though the contents of the last field is "nothing" (Which is something, if you see what I mean.)
In the SheepdogGuides version, there's a remmed out line that you can restore to the code which will put "null" (or other text of your choosing) into any cell which had a "nothing" put in it, so you can tell which those cells were, vs the ones that were not accessed at all. (Search on "then sOneField:='null'").
------------------
The last line: Does it matter if, after the last printing character in the CSV, the person entering the data pressed the Enter key?
In the Lazarus version, empty lines at the end of the CSV are ignored. It even ignores lines with just spaces. (But not a line with a comma).
The SheepdogGuides version will "see" the line created by each "enter", add an empty row.
------------------
Column width adjustments: At one point, after the Lazarus version had loaded all of the records into the StringGrid, it adjusted the width of each column to make it just wide enough for the longest entry in that column. It doesn't do that any more.
The Lazarus version was not doing any width adjusting at 01 Oct 16... which is a good thing, inspired me to change the SheepdogGuides version.
Why "take out a feature"? Because it won't always be wanted. And if you DO want the feature, be it for the Lazarus or SheepdogGuides way to fill the StringGrid, just call the StringGrid's "AutoSizeColumns" method, if you want the widths adjusted.
By the way... sorry... until I can get to addressing all the "issues" of Fixed Columns (or rows) in a StringGrid, I have to ask you to set those properties to zero, if using my Load and Save. (In a nutshell: those columns ARE in the "cells[x,y] world, just like all the other cells of the StringGrid... but they are OUTSIDE of what you can "reach" at run time with mouse clicks or arrow keys.
The related propterties are usually set to zero or 1. When set to 1, you get the things where column titles/ row numbers typically go, at the top and on the left.
If you don't mind loading and saving all of the body of your StringGrid AND the fixed columns when you do a load or save, you will be mostly okay. But if you access the cells directly, by code, you'll have to be careful, or you may "do things" you didn't mean to to things in the (supposed to be) "fixed" columns/ rows.)
At 01 Oct 16, if you read about CSV stuff at http://wiki.freepascal.org/CsvDocument, it sounded like you had to "add stuff" to your basic Lazarus IDE.
For the CSV stuff discussed in this tutorial, you do not. (I was using Lazarus Vers #:1.0, with FPC 2.6.0, on a Windows 7 box.)
Go along to http://wiki.freepascal.org/Grids_Reference_Page for lots of good stuff about grids in general, which will help you work with StringGrids.
I hope this section doesn't grow much. It is for odds and ends which have occured to me since writing the code you see here.
It may be that a blank line in the file of records to be appended will terminate the loading of records from that into this. I don't aspire to make my code idiot PROOF. Idiot resistant will do. Rather than re-write the code, I will rely on users to notice the shortfall, visit the CSV file, and figure out what is wrong and fix it.
And so to the sourcecode! I try to keep in step what is here and what is in the .zip file with .exe, sample data, and full sourcecode, forms files, etc which you can download. Sometimes they get out of step though. I would be grateful if you let me know of anything puzzling that you encounter. Maybe you can save the next person the hassle of scratching heads?
unit ldn189u1;
{$mode objfpc}{$H+}
//This demo application, LDN_189, demonstrates loading a
// StringGrid from a CSV file. LDN_189 is explained in
// https://sheepdogguides.com/lut/ltn4d.htm
//A much bigger application, which uses the code in
// LDN_189, discusses using a StringGrid to access a
// CSV file, make checks on the validity of data in that
// file (for instance, one field is checked to see if
// it contains a valid abbreviation for a US state. Any
// invalid datum is highlighted, and can be edited.)
//That application is discussed in the tutorial at...
// https://sheepdogguides.com/lut/ltn4c.htm
//N.B.: At the moment, there is a Kludge... search on
// bColIndexL>8
// ... to find it. It limits the number of fields which
// can be in a record.
//Author's notes to himself: At bottom.
interface
uses
Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
Grids, ClipBrd;
const vers='1 Oct 16';
//started 29 Sep 16, on road to an application to
// vett DVD records data.
kGridMaxXIndex=15;
kGridMaxYIndex=25;
type
{ Tldn189f1 }
Tldn189f1 = class(TForm)
buQuit: TButton;
buLoadSGWay: TButton;
buAdjColWidth: TButton;
buAbout: TButton;
buLoadLazWay: TButton;
buLinkToWeb: TButton;
sgData: TStringGrid;
procedure buAboutClick(Sender: TObject);
procedure buLinkToWebClick(Sender: TObject);
procedure buLoadSGWayClick(Sender: TObject);
procedure buQuitClick(Sender: TObject);
procedure buAdjColWidthClick(Sender: TObject);
procedure buLoadLazWayClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ private declarations }
procedure LoadFileToStringGrid(sFileName:string);
procedure StringToSGRow(sgLocal:TStringGrid;
sRawData:string;iRow:integer;boTrimFields:boolean);
public
{ public declarations }
end;
var
ldn189f1: Tldn189f1;
implementation
{$R *.lfm}
{$R+}
{ Tldn189f1 }
procedure Tldn189f1.buQuitClick(Sender: TObject);
begin
close;
end;
procedure Tldn189f1.buAdjColWidthClick(Sender: TObject);
begin
sgData.AutoSizeColumns;
end;
procedure Tldn189f1.buLoadLazWayClick(Sender: TObject);
begin
//This may be "simple", but, sadly, it treats spaces as
//field separators, unless the whole field is
//enclosed in quotes. The last parameter has to do
//with whether there's a line of headers in the CSV
//
//This "answer" sets the number of columns in the
//StringGrid to the number of fields in the first
//record (and throws away "extras" on subsequent
//lines.) (My "answer" WILL throw away extras, too,
//but, at 30 Sep 16, set the number of columns
//crudely. (At present, "extras" cause problems.)
//
//My "answer" could be enhanced to set the number
//of colums as LoadFromCSVFile does.
//
//Both answers treat...
// My,Data,
//as a line with THREE fields, the last holding ''
//I.e. the third field is "nothing" which is DIFFERENT
//from "not specified".
//
//I'm not sure that this would work in Delphi, or, if it
//does, will work exactly as it does in Lazarus.
sgData.LoadFromCSVFile('TestDataLDN189.txt',',',true);
end;
procedure Tldn189f1.LoadFileToStringGrid(sFileName:string);
(*The file to be loaded should consist of one or more
RECORDS, each consisting of one or more FIELDS.
The file should consist printable characters, plus
(maybe) the code for TAB, and a FEW others like that.
The RECORDS are separated by CR/LF terminators,
FIELDS are separated by COMMAS. A line ending in
a comma is considered to indicate that the final
field of that record had nothing in it, e.g.....
Three fields: Fred,Bloggs,860-767-5555
Four fields: Fred,Bloggs,860-767-5555,
*)
var
cardCounter:Cardinal;
stringsTmp:TStrings;
begin //LoadFileToStringGrid
stringsTmp:=TStringList.Create;
try
stringsTmp.LoadFromFile(sFileName);
sgData.RowCount:=stringsTmp.Count;//Yes: .count, not .count-1
sgData.ColCount:=7;//This CLUMSY... But could fairly easily
//be replaced by code to count the fields in the first
//line, and make the StringGrid have that many columns.
//The "built in" LoadFromCSVFile does that. Both routines
//ignore the fact that a data file might have more
//fields in a subsequent record. (The extra fields are
//just thrown away in the LoadFromCSVFile. At present,
//they cause a problem (good! You'll know about them!)
//in this answer. (That can be fixed.)
//What's done here, for the moment, is to set, by hand, something
//that limits how many fields there can be in any record
//in the CSV file. Exceed that limit, and the application
//shuts down, in a messy way. Fall short of that on a given
//line, and you just have some empty cells at the right hand
//end of the line.
//For the future: Add a parameter to LoadFileToStringGrid
//If zero: Use the "answer" used by LoadFromCSVFile, if
//> zero, set ColCount from that. (If this is done, also
//provide a "scan file, find out longest (most fields) record"
//function.)
//
//In any case, add a "var" variable to return error codes
//to user.... "file not found","too many fields encountered",
//etc.
//Also add a parameter to specify the separator character.
//Add a way to use.... "Bloggs, Fred",123 to allow separator
// character inside a field? Make it optional, and tell
//users that the application runs faster if they don't
//use the feature?
for cardCounter:= 0 to stringsTmp.Count-1 do begin
StringToSGRow(sgData,stringsTmp[cardCounter],cardCounter,true);
end;
//To be checked... what does app do when a)there is / b) there
// is not a CR at the end of the file? (Prelim checks: It doesn't
// matter. (^_^)
//If there is a last line consisting just of spaces, the Lazarus
// version does NOT add a row to the StringGrid, the
// SheepdogGuides version DOES add a row
finally
stringsTmp.Free;
end;//Try... finally
//Put.... sgData.AutoSizeColumns;
// ... in your code, after the call of LoadFileToStringGrid,
// if you would like all of the columns' widths adjusted,
// leaving each column just wide enough for the widest datum
// in the column.
end;//LoadFileToStringGrid
procedure Tldn189f1.StringToSGRow(sgLocal:TStringGrid;
sRawData:string;iRow:integer;boTrimFields:boolean);
var bColIndexL:byte;
sOneField:string;
boDoneIt:boolean;
//This procedure is used within "LoadFileToStringGrid". You
// wouldn't be likely to want to call it directly.
//Takes, from sRawData, a string like...
// 860-555-1912,CT,Joe Smith
//... and puts the three fields in the first three
//colums of StringGrid sgLocal.
//What's in sRawData is "eaten away" over the course of the procedure.
//If boTrimFields is true, then spaces at either end of the field datum
// are trimmed off....
// " sample datum "
// ... would become...
// "sample datum"
function boChomp(var sSource:string;
var sDest:string):boolean;//SR of StringToSGRow
//N.B. BOTH PARAMS ARE *var* PARAMS... contents of the
//variables used to "feed" this SR will be changed by
//the execution of the procedure.
//
//The contents of sDest before the call are irrelevant.
//
//If the string passed to sSource has a comma at it's
// right hand end, then the routine assumes that to mean
// that there is a field after the comma, but that the
// contents of that field just happen to be "nothing",
// i.e. ''. In the processing of such a string, sSource
// will be passed back to the calling program with a
// rogue value, just before the call of Chomp which
// "reads" the "empty" field.
//In other words... string "a" has three fields,
// string "b" has FOUR...
// a) Fred,Bloggs,860-767-5555
// b) Fred,Bloggs,860-767-5555,
// ---^---
var iPosOfComma:integer;
const kFieldSep=',';//Rewrite to supply this in
//a parameter, if you feel having that
//flexiblity is important to you.
begin //main block of boChomp, SR of StringToSGRow
if sSource='' then result:=true//no ; here
//(See "N.B. bChomp returns...", below. THIS "true"
//arises if you call Chomp when there was no point...
//you already knew there was no more data to parse.
else begin //1 (Use last field in string that started
//String1, String2, .... , StringLast
//(There will be no comma after last field.)
//N.B. boChomp returns false until there is no more
// data to be harvested by a further call of Chomp.
//Note especially... the first time it
// returns TRUE, there is STILL one field to be
// USED by the calling program. It has been
// returned to the calling program in sDest,
// as usual.
if sSource='tkbRogueToIndicateNullFieldAtEndOfRaw' then
sSource:='';
iPosOfComma:=pos(kFieldSep,sSource);
if iPosOfComma=0 then begin //2
//The "=1" case arises if the string you are
//chomping ends with a comma, which is interpreted
//as meaning that there IS one more field in the
//string, but it just happens to consist of nothing,
//just happens to be ''}
sDest:=sSource;
sSource:='';
result:=true;//(See "N.B. bChomp returns..." THIS "true"
//is for the case where you have just harvested the last
//field from the string you have been "chomping" through.
end//no ; here. End of "then 2"
else begin //2
if iPosOfComma=length(sSource) then
sSource:=sSource+'tkbRogueToIndicateNullFieldAtEndOfRaw';
sDest:=copy(sSource,1,iPosOfComma-1);
sSource:=copy(sSource,iPosOfComma+1,length(sSource));
result:=false;
end;//of "else 2"
end;// of "else 1"
end;//boChomp, SR of StringToSGRow
begin //main block of StringToSGRow
bColIndexL:=0;
boDoneIt:=false;
repeat
boDoneIt:=boChomp(sRawData,sOneField);//N.B: These are "var"
//parameters. A bit is chopped off of sRawData, and
//sOneField is filled with a new value. Also: You can
//ignore compiler warning about sOneField not being
//initialized.
if boTrimfields then sOneField:=trim(sOneField);
//If you want to "see something" when a field in the
// CSV says "nothing, here", de-rem the following...
//if sOneField='' then sOneField:='null';
sgLocal.cells[bColIndexL,iRow]:=sOneField;
inc(bColIndexL);
until (boDoneIt) OR (bColIndexL>8);//second term to be refined in
//due course... at present, it is a kludge... but it WILL have
//a role to play one day. Keep note at top of code in step.
//(Eventually, before LoadFileToStringGrid,
//is called, somehow a determination will be made as to how many
//columns are to be filled. If, by chance or error, the CSV file
//has a record with too many fields, at the moment problems arise.
//When the "8" is replaced by a variable, and that is loaded to
//reflect how big the StringGrid available is, then steps can be
//taken to deal nicely with a "too big for the row" record from
//the CSV file.
end;//StringToSGRow
procedure Tldn189f1.FormCreate(Sender: TObject);
begin
ldn189f1.caption:='LDN189- Load CSV into StringGrid- ver: '+vers;
application.title:='LDN189';
end;
procedure Tldn189f1.buLoadSGWayClick(Sender: TObject);
begin
LoadFileToStringGrid('TestDataLDN189.txt');
end;
procedure Tldn189f1.buAboutClick(Sender: TObject);
begin
showmessage('This is one of many applications '+
'available from https://sheepdogguides.com/Lut/'+chr(13)+
'... a collection of Lazarsus and Delphi tutorials.');
end;
procedure Tldn189f1.buLinkToWebClick(Sender: TObject);
begin
Clipboard.AsText:='https://sheepdogguides.com/lut/lt4Nd.htm';
end;
(*Author's notes to himself...
LDN_189 derived from LDN_190, itself derived from LDN191!
LDN_191 is a complex application illustrating Doing Clever
Things with a StringGrid object. Sadly, at it's heart,
30 Sep 16, the code for the basic loading of the
StringGrid from data in a CSV file was flawed. I am
01 Oct 16, working on fixing that.
LDN_189 is a demo for the new, working (I hope!) core
"load CSV to StringGrid", believed to be fairly sound.
It demos two ways to do the job.
Where LDN_191 says/ said...
sgData.Rows[I-1].CommaText := CSV[I-1];
.... needs (needed? Maybe the changes have been made,
since this was written?)... needs to be changed to...
StringToSGRow(sgData,CSV[I],I,true);
... and the code to support that needs to be added.
*)
end.
Search across all my sites with the Google search...
|
|
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes. There were two "unknown attributes" in Google+ button code. Sigh.
....... P a g e . . . E n d s .....