Title: Simple parsing procedure and function
Question: A simple way to read csv-files and other files into your program
Answer:
//--------------------------------------------------------------------
// Here is a procedures and a function, which may be handy parsing
// files.
// They are presented in a little program, with just a button on
// a form. Also put an OpenDialog (from the Dialogs tab) on the
// form. You can also open the Outlook Addressbook and export your
// Email addresses with "File", "Export", "Other addressbook" to a
// Comma Separated Value file. This exported CSV-file can be used
// as an input for this small application, which will read the
// exported data into the program and export them (via OLE) directly
// to Excel.
//--------------------------------------------------------------------
// Befor you want to write comments to this article, stating that
// Excel is able to import CSV-files directly:
//
// YES, I DO KNOW THAT EXCEL IS ABLE TO IMPORT CSV FILES DIRECTLY!
//
// This is written, just to demonstrate the working of the presented
// function and procedure.
//--------------------------------------------------------------------
// Unit1.pas
Unit Unit1;
Interface
Uses
Windows, Messages, SysUtils, Classes, Graphics, Controls,
Forms, Dialogs, StdCtrls, ComObj;
Type
TForm1 = class(TForm)
Button1 : TButton;
OpenDialog1 : TOpenDialog;
Procedure Button1Click(Sender : TObject);
Private
{ Private declarations }
Public
{ Public declarations }
End;
Var
Form1: TForm1;
Implementation
{$R *.DFM}
//--------------------------------------------------------------------
// With this function you can reduce the number of separation
// characters in a line to just one separator.
// This can be handy when values are separated by tabs or spaces.
//--------------------------------------------------------------------
Function ReduceSeparator(Const Ch : Char;
Const S : ANSIString) : ANSIString;
Var
L,I : DWORD;
Bo : Boolean;
Begin
Result := '';
L := Length(S);
Bo := True;
For I := 1 to L do
Begin
If S[I] Ch then
Begin
Result := Result + S[I];
Bo := False; // no separator
End;
If (S[I] = Ch) and (Bo = False) then
Begin
Result := Result + S[I];
Bo := True; // skip the next separators, if found
End;
End;
End;
//--------------------------------------------------------------------
// In some applications a simular visual basic script procedure is
// defined, which can also be very handy parsing strings.
// With this procedure you can easily read exported files into your
// application.
// F.i. Excel and Outlook (addressbook) are able to export in "csv"
// format. (CSV = Comma Separated Values)
//--------------------------------------------------------------------
Procedure SplitString(Const Ch : Char; Const AStr : ANSIString;
Var StrArr : TStringList);
Var
AStr1 : ANSIString;
P,L : Integer;
Begin
// Make a copy of the input string (Const)
AStr1 := AStr;
// Empty the stringlist
StrArr.Clear;
Repeat
P := 0;
L := Length(AStr1);
If L 0 then
Begin
// Check for the first occurence of the separator (Ch)
P := Pos(Ch,AStr1);
If P 1 then
// Copy the field into the stringlist
StrArr.Add(Copy(AStr1,1,P - 1));
If P = 1 then
// The field is empty
// The separator is the first char of the input string
StrArr.Add('');
If P 0 then
Begin
// Remove the field from the input string
AStr1 := Copy(AStr1,P + 1,L - P);
// Correct the length of the input string
L := Length(AStr1);
End;
If (P = 0) and (L 0) then
// Add the last field to stringlist
// A separator (Ch) at the end is not required
StrArr.Add(AStr1);
End;
Until (P = 0);
End;
//--------------------------------------------------------------------
Procedure TForm1.Button1Click(Sender : TObject);
Var
f : TextFile;
asStr : ANSIString;
slA : TStringList;
Row,Column : Integer;
Excel : OLEVariant; // Don't forget: Uses ComObj
Begin
// Set caption of open dialog
OpenDialog1.Title := 'Open file...';
// Set filter of open dialog
OpenDialog1.Filter := 'Comma separated value files|*.csv|All files|*.*';
// Set the default filter index of the open dialog
OpenDialog1.FilterIndex := 0;
If OpenDialog1.Execute then
Begin
// Thanks to article: 479 written by Berhard Angerer
Excel := CreateOLEObject('Excel.Application');
// Add workbook
// Standard containing 3 sheets
Excel.WorkBooks.Add;
// Show Excel
Excel.Visible := True;
// Initialise row
Row := 1;
// Change sheet name into "Demo"
Excel.WorkBooks[1].WorkSheets[1].Name := 'Demo';
// You can also access this worksheet with:
// Excel.WorkBooks[1].Worksheet['Sheet1']...
// but that's not very wise:
// In the English version of Excel a sheet is called: "Sheet".
// In the Dutch version of Excel a sheet is called: "Blad".
// So when you use: "Sheet" you'll get an error in all the
// versions of Excel, in which a sheet is not called: "Sheet".
AssignFile(f,OpenDialog1.Filename);
Reset(f);
// Create the stringlist
slA := TStringList.Create;
Repeat
If not Eof(f) then
Begin
Readln(f,asStr);
// If you want to reduce the columns on a row;
// with CSV files it's recommended not to do so;
// but you can by "un-commenting" the follwing line:
// asStr := ReduceSeparator(';',asStr);
// Parse the line
SplitString(';',asStr,slA);
// Copy the fields into Excel
For Column := 0 to slA.Count - 1 do
Excel.Worksheets[1].Cells[Row,Column + 1].Value :=
slA.Strings[Column];
// Next row
Inc(Row);
End;
Until Eof(f);
// Close the file
CloseFile(f);
// Free the stringlist
slA.Free;
End;
End;
//--------------------------------------------------------------------
End. // of unit1
//--------------------------------------------------------------------