Solved

Problems reading csv file

Posted on 2004-10-06
5
540 Views
Last Modified: 2010-04-05
I am having a problem reading lines in a csv file. if there are no spaces in the text for each column my program works fine, but if there is a space in any of those columns the line is not read. ie
02074937272,02/08/04,070740,Local,02082050960,26,0.0048,LOC,,VOCE  //is OK

02074937272,02/08/04,071211,Japan Tokyo,0081332108774,358,0.2476,TOK,,VOCE // This line would fail as there is a space between Japan and Tokyo.

This is basic version of the code which makes  calls to DLL functions to retrieve the data from each column.
code---------------------------------------------------------------
begin
 sl := TStringList.Create;
try
    sl.LoadFromFile(CdrImportEdit.Text);

   for i := 0 to sl.count - 1 do
       begin
          Extn_no := ExtNum(sl[i]);
          Trunk_no := TrunkNum(sl[i]);
          Direction := CallDir(sl[i]);
          CallDay := DateOfCall(sl[i]);
          SQLdate := SQLDateOfCall(sl[i]);
          CallTime := TimeOfCAll(sl[i]);
          Duration := LenOfCAll(sl[i]);
          dest := PhoneNun(sl[i]);
     end;
   end;
 sl.free;
end;
----------------------------------------------------------------------

DLL is basically as follows:
code---------------------------------------------------------------------
Function ExtNum(S: String): String; stdcall;
begin
StrNum := TStringList.Create;
StrNum.Delimiter := ',';
StrNum.DelimitedText := S;
Result := StrNum.Strings[0];
Result := copy(Result,5,7);
StrNum.Free;
end;

Function TrunkNum(S: String): String; stdcall;
begin
Result := '1';
end;
Function CallDir(S: String): String; stdcall;
Begin
Result := 'Out';
end;

Function DateOfCall (S: String): String; stdcall;
Begin
StrNum := TStringList.Create;
StrNum.Delimiter := ',';
StrNum.DelimitedText := S;
Result := StrNum.Strings[1];
StrNum.Free;
end;
Function SQLDateOfCall (S: String): String; stdcall;
Begin
StrNum := TStringList.Create;
StrNum.Delimiter := ',';
StrNum.DelimitedText := S;

      Result := '20'+copy(StrNum.Strings[1],7,2)+'-'+copy(StrNum.Strings[1],4,2)+'-'+copy(StrNum.Strings[1],1,2);
StrNum.Free;
end;
Function TimeOfCall (S: String): String; stdcall;
Begin
StrNum := TStringList.Create;
StrNum.Delimiter := ',';
StrNum.DelimitedText := S;
Result := copy(StrNum.Strings[2],1,2)+':'+copy(StrNum.Strings[2],3,2)+':'+copy(StrNum.Strings[2],5,2);
StrNum.Free;
end;

Function LenOfCall (S: String): Real; stdcall;
Begin
StrNum := TStringList.Create;
StrNum.Delimiter := ',';
StrNum.DelimitedText := S;
Result := StrToFloat(StrNum.Strings[5])/60;
StrNum.Free;
end;

Function PhoneNum (S: String): String; stdcall;
Begin
StrNum := TStringList.Create;
StrNum.Delimiter := ',';
StrNum.DelimitedText := S;
        if copy(StrNum.Strings[4],1,1) <> '0'
        then
        Result:= '00'+StrNum.Strings[4]
        else
        Result := StrNum.Strings[4];
StrNum.Free;
end;
------------------------------------------------------------

Is this a feature of TstringList or have I missed something?
0
Comment
Question by:lloydie-t
  • 2
5 Comments
 
LVL 17

Expert Comment

by:mokule
Comment Utility
Hi,
Apparently it would be better to have fields with spaces enclosed in quotes.
In case You can't have it try something like this

S := StringReplace(S,' ','*R*R',[rfReplaceAll]);
StrNum.DelimitedText := S;
StrNum.Text := StringReplace(StrNum.Text,'*R*R',' ',[rfReplaceAll]);

:)
0
 
LVL 4

Expert Comment

by:Colin_Dawson
Comment Utility
You don't need to enclose fields with spaces in quotes.  Forget using the TStringList for reading CSV files. I wrote a component a while back which I called a TCSVFile.  This should do everything that you could possibly want with a CSV File.

There is some old code in this unit, which I don't use anymore.     Just Create the TCSVFile and use the public and publish procedures, functions and properties.   I did spend quite a lot of time getting this component to fully and properly support the full CSV formating standard.   Using this you can even have commas and quote marks in the fields.

unit U_CSVFormat;

{******************************************************************************}
{*                                                                            *}
{* © Copyright 2000 Colin Dawson                                              *}
{*                                                                            *}
{******************************************************************************}
{*                                                                            *}
{* This unit is provides a set of standardized routines to allow for the      *}
{* importing and exporting of Standards CSV (Comma Seperated Values) files.   *}
{*                                                                            *}
{* This unit supports reading and writing of records with the following       *}
{*   .Commas in the fields - Uses Double Quotation marks as string delimeters *}
{*   .Multi Line Fields - Reads the fields as a stream of bits until either   *}
{*                        an end of record or end of file marker is found     *}
{*                                                                            *}
{* Modification Record                                                        *}
{*                                                                            *}
{* Date        | Developer    | Description                                   *}
{*-------------+--------------+-----------------------------------------------*}
{* 01-May-2000 | Colin Dawson | Initial Development                           *}
{* 29-Aug-2001 | Colin Dawson | Ressurected MakeCSVLine for use writing to a  *}
{*             |              | TStream.                                      *}
{* 29-Jan-2002 | Colin Dawson | Implemented Filemode variable to allow        *}
{*             |              | reading from CD rom drives.                   *}
{* 06-Aug-2002 | Colin Dawson | Created The TCSVFile object                   *}
{* 09-Dec-2002 | Colin Dawson | Added memory caching of file                  *}
{******************************************************************************}

interface

Uses
  Windows, Classes;

//New routines to reading and writing CSV Files - USE THESE ONLY!!
Type
  TOpenMode = ( omRead, omWrite );

  TCSVFile = Class( TObject )
  Private
    vFilename : String;
    vCached : Boolean;
    vOpenMode : TOpenMode;
    vIsInternalStream : Boolean; //Says whether the stream should be freed or just cut loose.
    vStream : TStream;

    vRecordStart : Int64;
    vFields : Array of String;

    Procedure AddField( var CurrentData : String; Const ReverseOrder : Boolean = False );
    function GetField(FieldNo: Integer): String;
    function GetFieldCount: Integer;
    function GetStreamPos: Int64;
    function GetStreamSize: Int64;
    function IsBOF: Boolean;
    function IsEOF: Boolean;
    Procedure ReadCSVRecordForward;
    Procedure ReadCSVRecordBackward;
  Protected
    Procedure CheckStream;
  Public
    Constructor Create( Const FileName : String; Const OpenMode : TOpenMode = omRead; Const Cached : Boolean = True ); OverLoad; //Opens the file
    Constructor Create( Stream : TStream ); OverLoad;
    Destructor Destroy; Override;                                //Closes the file
    Procedure First;                                             //Moves to first record
    Procedure Previous;                                          //Moves back one record;
    Procedure Next;                                              //Moves to the next record
    Procedure Last;                                              //Moves immediatly to the last record.
    Procedure Append( Const RecordData : Array Of String );      //Adds a record to the file.
    Procedure Insert( Const RecordData : Array Of String; Const InsertBefore : Boolean = True ); //Inserts a record into the file
    Procedure Replace( Const RecordData : Array Of String );     //Replace the current record with this array
    Procedure Delete;                                            //deletes the current record
    Property BOF : Boolean read IsBOF;                           //Beginning of file marker - set when viewing the first record in the file
    Property EOF : Boolean read IsEOF;                           //End of File mark - set when viewing the last record in the file.
    Property Position : Int64 Read GetStreamPos;                 //Current position withing the file. This is the location of the start of the next record.
    Property Size : Int64 Read GetStreamSize;                    //Size of the file
    Property FieldCount : Integer Read GetFieldCount;            //Number of fields in the current record
    Property Fields[ FieldNo : Integer ] : String Read GetField; //Provides access to the data in the current record.
  End;

//Open the CSV File for reading;
//This function will return the size of the file in bytes when opened successfully
Function OpenCSVFile( var CSVFile : File; CSVFileName : String; OpenMode : TOpenMode ) : Integer;

//Get the next CSV record from the file
//This will return the number of bytes read from the file
Function GetCSVRecord( var CSVFile : File ) : Integer;

//Get the number of fields in the current record
Function GetCSVFieldcount : LongInt;

//Get the contents of a cell, this is a 0 based array.
Function GetCSVField( Index : LongInt ) : String;

//Use this to generate a record in CSV Format. All necessary formatting will be done for you.
Function WriteCSVLine( var CSVFile : File; Values : Array Of String ) : Boolean;

// Old routines for legacy use only. - will be removed soon
Function MakeCSVLine( Values : Array Of String ) : String;
Function ExtractCSVString( Fieldindex : Longint; SourceLine : String ) : String;
Function CountCSVString( SourceLine : String ) : LongInt;

implementation

Uses
  U_Global,
  SysUtils, Forms, Dialogs;

Const
  BytesToRead = 2048; //Bytes to read at a time;

Type
  ECSVFileError = Class( CJDException );

Var
  //Using a global variable as there is no other way of retaining this information in memory
  //between reads and writes.  This has been done so as to maximise on file efficiency;
  CSVBuffer : Array[ 0..( BytesToRead - 1 ) ] of Char;  //used to store the current file information
  CSVPos, CSVChrs : LongInt;                      //Stores the position of the first usable CHR from the file.
  CSVRecord : Array of string;           //An Array containing the current record;
  RecordTemination : Boolean;  //Forces the CSV file to check for a Termination CHR After a read from the file.

Function OpenCSVFile( var CSVFile : File; CSVFileName : String; OpenMode : TOpenMode ) : Integer;
//Var
//  SearchRec : TSearchRec;
Begin
  //This function will open the file specified in CSVFileName;
  //Only after the file has been opened will the result be set to true;
  Try
    //Get the size of the file - used for a good return code;
    AssignFile( CSVFile, CSVFileName );
    Case OpenMode Of
      omRead  : Begin
                  FileMode := 0;
                  Reset( CSVFile, 1 ); //Make sure that the recordsize is set to one.
                End;
      omWrite : Begin
                  FileMode := 1;
                  ReWrite( CSVFile, 1 );
                End;
    End;
    CSVPos := -1;
    RecordTemination := False;
    Result := FileSize( CSVFile );
//    Result := True;
  Except
    Result := 0;
  end;
end;

Function GetCSVRecord( var CSVFile : File ) : Integer;
Var
  QuotesInARow, NumFields : LongInt;
  EndOfRecord : Boolean;
  WithinQuotations : Boolean;
  NoOfBytesRead : Integer;
//  BytesReadOffSet : DWord;
Begin
  NoOfBytesRead := 0;
//  Result := True;
  NumFields := 1;
  EndOfRecord := False;
  QuotesInARow := 0;
  WithinQuotations := False;
  SetLength( CSVRecord, 0 );
  SetLength( CSVRecord, NumFields );

  If ( EOF( CSVFile ) ) And ( CSVPos = CSVChrs ) then
  Begin
    Result := NoOfBytesRead;
//    Result := False;
    Exit; //End of file reached.
  end;

//  NoOfBytesRead := CSVChrs;

  While Not EndOfRecord Do
  Begin
    If CSVPos < 0 Then
    Begin
      //End of the Data buffer reached load more data!
      Try
        BlockRead( CSVFile, CSVBuffer, BytesToRead, CSVChrs );
        CSVPos := 0;

        If RecordTemination Then
        Begin
          If ( ( CSVBuffer[ CSVPos ] = #13 ) or ( CSVBuffer[ CSVPos ] = #10 ) ) Then
          Begin
            Inc( NoOfBytesRead );
            Inc( CSVPos ); //move past the EOL Terminator
          end;
        End;

      Except
        Result := NoOfBytesRead;
//        Result := False;
        Exit;
      End;
    End;

    While ( CSVPos < CSVChrs ) And ( Not EndOfRecord ) Do
    Begin
      //Cycle through the Block until End of record marker;
      If ( CSVBuffer[ CSVPos ] = ',' ) and ( Not WithinQuotations )
      Then Begin
             Inc( NumFields );
             SetLength( CSVRecord, NumFields ); //Found another field!
             WithinQuotations := False;
             QuotesInARow := 0;
           End
      Else Begin
             If ( CSVBuffer[ CSVPos ] = '"' )
             then Begin
                    WithinQuotations := Not WithinQuotations; //Either Entering or exiting field.
                    Inc( QuotesInARow );
                    If ( QuotesInARow > 1 ) and WithinQuotations Then
                    Begin
                      CSVRecord[ High( CSVRecord ) ] := CSVRecord[ High( CSVRecord ) ] + CSVBuffer[ CSVPos ];
                      QuotesInARow := 0;
                    End;
                  End
             Else Begin
                    QuotesInARow := 0;
                    //Might find a EOL Marker Here to dealwith.
                    If ( ( CSVBuffer[ CSVPos ] = #13 ) or ( CSVBuffer[ CSVPos ] = #10 ) ) And not WithinQuotations
                    Then Begin
                           EndOfRecord := True;
                           //Check for a Line feed in the next CHR
                           If CSVPos < ( CSVChrs - 1 ) then
                           Begin
                             If ( ( CSVBuffer[ CSVPos + 1 ] = #13 ) or ( CSVBuffer[ CSVPos + 1 ] = #10 ) ) Then
                             Begin
                               Inc( NoOfBytesRead );
                               Inc( CSVPos ); //move past the EOL Terminator
                             end;
                           End
                           Else RecordTemination := True;
//                           CSVRecord[ High( CSVRecord ) ] := CSVRecord[ High( CSVRecord ) ] + #13#10; //Put the EOL Marker into the string;
                         End
                    Else Begin
                           If Not EndOfRecord Then
                           Begin
                             CSVRecord[ High( CSVRecord ) ] := CSVRecord[ High( CSVRecord ) ] + CSVBuffer[ CSVPos ];  //Add the Current Chr to the Array
                             If ( ( CSVBuffer[ CSVPos ] = #13 ) or ( CSVBuffer[ CSVPos ] = #10 ) ) Then
                             Begin
                               If CSVBuffer[ CSVPos ] = #13
                               Then CSVRecord[ High( CSVRecord ) ] := CSVRecord[ High( CSVRecord ) ] + #10
                               Else CSVRecord[ High( CSVRecord ) ] := CSVRecord[ High( CSVRecord ) ] + #13;
                             End;
                           End;
                         End;
                  End;
           end;
      Inc( NoOfBytesRead );
      Inc( CSVPos );
    End;

    If CSVChrs < BytesToRead Then EndOfRecord := True; //End of File reached!!!
    If Not EndOfRecord Then CSVPos := -1; //Need more data;
  End;
  Result := NoOfBytesRead;
End;

Function GetCSVFieldcount : LongInt;
Begin
  Result := High( CSVRecord ) + 1;
End;

Function GetCSVField( Index : LongInt ) : String;
Begin
  If ( 0 <= Index ) And ( Index <= High( CSVRecord ) )
  Then Result := CSVRecord[ Index ]
  Else Result := ''; //No record found so return null.
end;

Function WriteCSVLine( var CSVFile : File; Values : Array Of String ) : Boolean;
Var
  Loop : LongInt;
  ValueLoop : LongInt;
  LineToWrite : Array Of Char;
  NumChars, CharsWritten : LongInt;
  PutInQuotes : Boolean;
Begin
  Result := False;
  NumChars := 0;
  SetLength( LineToWrite, NumChars );
  For Loop := Low( Values ) To High( Values ) Do
  Begin
    PutInQuotes := ( Pos( ',', Values[ Loop ] ) > 0 ) or
       ( Pos( '"', Values[ Loop ] ) > 0 ) or
       ( Pos( #13, Values[ Loop ] ) > 0 ) or
       ( Pos( #10, Values[ Loop ] ) > 0 ); //Must be in quotes
    If PutInQuotes Then
    Begin
      Inc( NumChars );
      SetLength( LineToWrite, NumChars );
      LineToWrite[ Numchars - 1 ] := '"';
    end;

    For ValueLoop := 1 to Length( Values[ Loop ] ) Do
    Begin
      Inc( NumChars );
      SetLength( LineToWrite, NumChars );
      LineToWrite[ Numchars - 1 ] := Values[ Loop, ValueLoop ];
      If Copy( Values[ Loop ], ValueLoop, 1 ) = '"' Then
      Begin
        Inc( NumChars );
        SetLength( LineToWrite, NumChars );
        LineToWrite[ Numchars - 1 ] := '"';
      End;
    End;

    If PutInQuotes Then //Mus be in quotes
    Begin
      Inc( NumChars );
      SetLength( LineToWrite, NumChars );
      LineToWrite[ Numchars - 1 ] := '"';
    end;

    If Loop <> High( Values )
    Then Begin
           Inc( NumChars );
           SetLength( LineToWrite, NumChars );
           LineToWrite[ Numchars - 1 ] := ',';
         end;
  End;

  //End of Line Terminator;
  Inc( NumChars );
  SetLength( LineToWrite, NumChars );
  LineToWrite[ Numchars - 1 ] := #13;
  Inc( NumChars );
  SetLength( LineToWrite, NumChars );
  LineToWrite[ Numchars - 1 ] := #10;

  Try
    BlockWrite( CSVFile, LineToWrite[ 0 ], NumChars, CharsWritten );
    Result := NumChars = CharsWritten;
  Except
  End;
End;

Function MakeCSVLine( Values : Array Of String ) : String;
Var
  Loop : LongInt;
  ValueLoop : LongInt;
Begin
  Result := '';
  For Loop := 0 To High( Values ) Do
  Begin
    If ( Pos( ',', Values[ Loop ] ) > 0 ) or ( Pos( '"', Values[ Loop ] ) > 0 ) Then
    Begin
      Result := Result + '"';
      For ValueLoop := 1 to Length( Values[ Loop ] ) Do
      Begin
        Result := Result + Copy( Values[ Loop ], ValueLoop, 1 );
        If Copy( Values[ Loop ], ValueLoop, 1 ) = '"' Then
          Result := Result + '"';
      End;
      Result := Result + '"';
    End
    Else Result := Result + Values[ Loop ];
    If Loop <> High( Values )
    Then Result := Result + ',';
  End;
  While Copy( Result, Length(Result) - 1, 1 ) = ',' Do
  Begin
    Result := Copy( Result, 1, Length(Result) - 1 );
  End;
End;

Function GetCSV( Index : LongInt; value : string ) : String;
Var
  RecEnd : Boolean;
  qFlag : Boolean;
  CurrentChar : LongInt;
Begin
  qFlag := False;
  RecEnd := False;
  CurrentChar := Index;
  While ( CurrentChar <= Length( Value ) ) and ( Not RecEnd ) do
  Begin
    If ( Value[ CurrentChar ] = ',' ) and ( Not qFlag )
    then RecEnd := True
    Else Begin
           If Value[ CurrentChar ] = '"'
           Then QFlag := not QFlag;
         End;
    CurrentChar := CurrentChar + 1;
  End;
  If Currentchar >= Length( Value )
  then Result := Copy( Value, Index, ( Currentchar - Index ) )
  Else Result := Copy( Value, Index, ( Currentchar - Index ) - 1 );
End;

Function DeQuoteCSV( Value : String ) : String;
Var
  WorkingString : String;
Begin
  WorkingString := Value;
  While ( Pos( '""', WorkingString ) > 0 ) And ( Pos( '""', WorkingString ) < ( Length( WorkingString ) - 1 ) ) do
    WorkingString := Copy( WorkingString, 1, Pos( '""', WorkingString ) ) + Copy( WorkingString, Pos( '""', WorkingString ) + 2,  Length( WorkingString ) );
  If Copy( WorkingString, 1, 1 ) = '"'
  Then WorkingString := Copy( WorkingString, 2, Length( WorkingString ) - 1 );
  If Copy( WorkingString, Length( WorkingString ), 1 ) = '"'
  Then WorkingString := Copy( WorkingString, 1, Length( WorkingString ) - 1 );
  Result := Trim( WorkingString );
End;

Function ExtractCSVString( Fieldindex : Longint; SourceLine : String ) : String;
Var
  CurrentChr : LongInt;
  CurrentCol : LongInt;
  SelectedText : String;
Begin
  CurrentChr := 1;
  CurrentCol := -1; //Not on a Column Yet!
  If Copy( SourceLine, Length( SourceLine ), 1 ) = ','
  Then SourceLine := Copy( SourceLine, 1, Length( SourceLine ) - 1 );
  While CurrentCol < FieldIndex Do
  Begin
    //Move to Next Column
    Inc( Currentcol );
    //Extract Column from CurrentRow
    SelectedText := GetCSV( CurrentChr, SourceLine );
    CurrentChr := Currentchr + Length( SelectedText ) + 1;
  End;
  If Copy( SelectedText, Length( SelectedText ), 1 ) = ','
  Then SelectedText := Copy( SelectedText, 1, Length( SelectedText ) - 1 );
  SelectedText := DeQuoteCSV( SelectedText );
  Result := SelectedText;
End;

Function CountCSVString( SourceLine : String ) : LongInt;
Var
  CurrentCHR : LongInt;
  InField : Boolean;
Begin
  Result := 0;
  Infield := False;
  If Length( SourceLine ) > 0 Then
  Begin
    Inc( Result ); //There is a least one field!
    CurrentCHR := 1;
    While CurrentCHR < Length( SourceLine ) Do
    Begin
      If SourceLine[ CurrentChr ] = '"' Then Infield := Not Infield;
      If ( SourceLine[ CurrentChr ] = ',' ) And ( Not Infield ) Then Inc( Result );
      Inc( CurrentChr );
    End;
  End;
End;


Function TemporaryDirectory : String;
Var
  Buffer : PChar;
Begin
  Buffer := StrAlloc( Max_Path );
  If GetTempPath( Max_Path, Buffer ) = 0
  Then Result := ''
  Else Result := IncludeTrailingPathDelimiter( StrPas( Buffer ) );
  StrDispose( Buffer );
End;

Function GetTemporaryFileName( Const Path : String = '' ) : String;
Var
  lvPath : String;
  lvBuffer : PChar;
Begin
  //Decide what directory to use.
  If Path = ''
  Then lvPath := TemporaryDirectory
  Else lvPath := IncludeTrailingPathDelimiter( Path );

  //Now to call

  lvBuffer := StrAlloc( MAX_PATH + 1);
  Try
    If GetTempFileName( PChar( lvPath ), '', 1, @lvBuffer[ 0 ] ) = 0 Then
      Raise Exception.Create( 'Cannot assign temporary file' );

    Result := StrPas( lvBuffer );
  Finally
    StrDispose( lvBuffer );
  End;
End;

{Used in TCSVFile}

Procedure ProcessCharacter( Var CurrentData : String; Const Character : Char; Const ReverseOrder : Boolean = False );
Begin
  If Not ReverseOrder
  Then CurrentData := CurrentData + Character
  Else CurrentData := Character + CurrentData;
end;

{ TCSVFile }


{ TCSVFile }

procedure TCSVFile.AddField(var CurrentData: String; Const ReverseOrder : Boolean = False);
Var
  lvNoOfFields : Int64;
  lvLoop : Integer;
Begin
  lvNoOfFields := Length( vFields );
  Inc( lvNoOfFields );
  SetLength( vFields, lvNoOfFields );

  If Not ReverseOrder
  Then vFields[ High( vFields ) ] := CurrentData
  Else Begin
         For lvLoop := High( vFields ) - 1 downto Low( vFields ) Do
         Begin
           vFields[ lvLoop + 1 ] := vFields[ lvLoop ]
         end;
         vFields[ Low( vFields ) ] := CurrentData
       End;
  CurrentData := '';
end;

procedure TCSVFile.Append(const RecordData: array of String);
Var
  lvLoop : LongInt;
  lvValueLoop : LongInt;
  lvBuffer : String;
  lvReadChar : Char;
Begin
  If vOpenMode = omRead Then
    Raise ECSVFileError.Create( 'Stream must be open in write mode' );

  CheckStream;

  //Convert the data array into CSV format.
  lvBuffer := '';
  For lvLoop := 0 To High( RecordData ) Do
  Begin
    If ( Pos( ',', RecordData[ lvLoop ] ) > 0 ) or ( Pos( '"', RecordData[ lvLoop ] ) > 0 ) or
       ( Pos( #13, RecordData[ lvLoop ] ) > 0 ) or ( Pos( #10, RecordData[ lvLoop ] ) > 0 ) Then
    Begin
      lvBuffer := lvBuffer + '"';
      For lvValueLoop := 1 to Length( RecordData[ lvLoop ] ) Do
      Begin
        lvBuffer := lvBuffer + Copy( RecordData[ lvLoop ], lvValueLoop, 1 );
        If Copy( RecordData[ lvLoop ], lvValueLoop, 1 ) = '"' Then
          lvBuffer := lvBuffer + '"';
      End;
      lvBuffer := lvBuffer + '"';
    End
    Else lvBuffer := lvBuffer + RecordData[ lvLoop ];
    If lvLoop <> High( RecordData )
    Then lvBuffer := lvBuffer + ',';
  End;
  While Copy( lvBuffer, Length(lvBuffer), 1 ) = ',' Do
  Begin
    lvBuffer := Copy( lvBuffer, 1, Length(lvBuffer) - 1 );
  End;

  //Next Set the stream to the end.
  vStream.Seek( vStream.Size - 1, soFromBeginning );
  vStream.Read( lvReadChar, SizeOf( lvReadChar ) );

  //Add a CRLF if needed.
  If Not ( ( lvReadChar = #13 ) or ( lvReadChar = #10 ) ) Then
  Begin
    If vStream.Position > 0 Then
      lvBuffer := #13#10 + lvBuffer;
  End;
  //Now write the information into the stream.
  Try
    vStream.Write( lvBuffer[ 1 ], Length( lvBuffer ) );
  Except
    vOpenMode := omRead;
    Raise ECSVFileError.Create( 'Cannot write to stream, switching to read only mode' );
  End;
end;

procedure TCSVFile.CheckStream;
begin
  If vStream = nil Then
    Raise ECSVFileError.Create( 'Stream not open' );
end;

constructor TCSVFile.Create( Const FileName : String; Const OpenMode : TOpenMode = omRead; Const Cached : Boolean = True );
Var
  lvFileStream : TStream;
begin
  Inherited Create;
  vFilename := FileName;
  vCached := Cached;
  vStream := nil;
  vOpenMode := OpenMode;
  vIsInternalStream := True;
  SetLength( vFields, 0 );
  Case vOpenMode Of
    omRead  : Begin
                If FileExists( FileName )
                Then vStream := TFileStream.Create( FileName, fmOpenRead or fmShareDenyNone{fmShareDenyWrite} ) //Open the file in read only mode, allow other programs to access it for reading only
                Else Raise ECSVFileError.Create( 'File not found', FileName );
              End;
    omWrite : Begin
                If FileExists( FileName )
                Then vStream := TFileStream.Create( FileName, fmOpenReadWrite or fmShareDenyWrite )  //Open the file in read/write mode, allow other programs to access it for reading only
                Else vStream := TFileStream.Create( FileName, fmCreate or fmShareDenyWrite );  //Create a new file. allow other programs to access it for reading only
              End;
  End;

  If vCached Then
  Begin
    lvFileStream := vStream;
    Try
      vStream := TMemoryStream.Create;
      TMemoryStream( vStream ).SetSize( lvFileStream.Size );
      vStream.CopyFrom( lvFileStream, 0 );
    Finally
      lvFileStream.Free; //Closes the file now!  Works with the cached memory version
    End;
  End;

  First;
end;

constructor TCSVFile.Create(Stream: TStream );
begin
  Inherited Create;
  vFilename := '';
  vCached := False;
  vStream := Stream;
  vOpenMode := omWrite; //foriegn Stream, can always write to it.
  vIsInternalStream := False;
  SetLength( vFields, 0 );
  First;
end;

procedure TCSVFile.Delete;
var
  lvTmpStream : TStream;  //Temporary file to store the rest of the data in.
//  lvTmpFileName : String;   //Get a temporary filename to use for the scratchpad.
  lvRemainderOfFile : Int64;
Begin
  If vOpenMode = omRead Then
    Raise ECSVFileError.Create( 'Stream must be open in write mode' );

  CheckStream;
//  lvTmpFileName := GetTemporaryFileName;
//  lvTmpStream := TFileStream.Create( lvTmpFileName, fmCreate );
  lvTmpStream := TMemoryStream.Create;
  Try
    //First copy off to the end of the file.
    lvRemainderOfFile := vStream.Size - vStream.Position;
    If lvRemainderOfFile = 0
    Then lvTmpStream.Size := 0
    Else lvTmpStream.CopyFrom( vStream, lvRemainderOfFile ); //Copy the rest of the stream to the temp file.

    vStream.Position := vRecordStart; //Rewind to the start of the record.
    vStream.Size := vRecordStart;
    lvTmpStream.Seek( 0, soFromBeginning ); //Rewind to the start of the scratch stream;
    vStream.CopyFrom( lvTmpStream, lvTmpStream.Size ); //Copy the entire content back from the scratch stream
    vStream.Position := vRecordStart; //Rewind to the start of the record again
  Finally
    FreeAndNil( lvTmpStream );
//    DeleteFile( lvTmpFileName ); //Finished with the file, so delete it.
  End;
  ReadCSVRecordForward; //Now read the next record from the file.
end;

destructor TCSVFile.Destroy;
Var
  lvMemoryStream : TMemoryStream;
begin
  If vIsInternalStream Then
  Begin
    Try
      If Not vCached
      Then Begin
             If vStream <> nil Then
               FreeAndNil( vStream );
           End
      Else Begin
             if vOpenMode = omWrite Then
             Begin
               If vCached
               Then Begin
                      lvMemoryStream := TMemoryStream( vStream );
                      Try
                        If FileExists( vFileName )
                        Then vStream := TFileStream.Create( vFileName, fmOpenReadWrite or fmShareDenyWrite )  //Open the file in read/write mode, allow other programs to access it for reading only
                        Else vStream := TFileStream.Create( vFileName, fmCreate or fmShareDenyWrite );  //Create a new file. allow other programs to access it for reading only
                        Try
                          lvMemoryStream.Seek( 0, soFromBeginning );
                          vStream.CopyFrom( lvMemoryStream, lvMemoryStream.Size );
                        Finally
                          vStream.Free;
                        End;
                      Finally
                        lvMemoryStream.Free; //Finished with the memory cache.
                      End;
                    End;
                  End
             Else Begin
                    If vStream <> nil Then
                      FreeAndNil( vStream );
                  End;    
           End;
    Except
      Raise ECSVFileError.Create( 'Error closing file' );
    End;
  End;
  Inherited Destroy;
end;

procedure TCSVFile.First;
begin
  CheckStream;
  vStream.Seek( 0, soFromBeginning );
  vRecordStart := GetStreamPos;

  ReadCSVRecordForward; //Read the first record from the file.
end;

function TCSVFile.GetField(FieldNo: Integer): String;
begin
  If FieldNo > High( vFields ) Then
    Raise ECSVFileError.Create( 'Field index too high', 'Field Queried = ' + IntToStr( FieldNo ) + #13#10 + 'Fields in row = ' + IntToStr( High( vFields ) ) );
  If FieldNo < 0 Then
    Raise ECSVFileError.Create( 'Field index too low', 'Field Queried = ' + IntToStr( FieldNo ) );

  Result := vFields[ FieldNo ];
end;

function TCSVFile.GetFieldCount: Integer;
begin
  Result := Length( vFields );
end;

function TCSVFile.GetStreamPos: Int64;
begin
  CheckStream;
  Result := vStream.Position;
end;

function TCSVFile.GetStreamSize: Int64;
begin
  CheckStream;
  Result := vStream.Size;
end;

procedure TCSVFile.Insert(const RecordData: array of String;
  const InsertBefore: Boolean);
var
  lvTmpStream : TStream;  //Temporary file to store the rest of the data in.
//  lvTmpFileName : String;   //Get a temporary filename to use for the scratchpad.
  lvSavedPosition : Integer;
  lvBuffer : String;
Begin
  If vOpenMode = omRead Then
    Raise ECSVFileError.Create( 'Stream must be open in write mode' );

  CheckStream;
//  lvTmpFileName := GetTemporaryFileName;
//  lvTmpStream := TFileStream.Create( lvTmpFileName, fmCreate );
  lvTmpStream := TMemoryStream.Create;
  Try
    If InsertBefore Then
      vStream.Position := vRecordStart; //Rewind to the start of the record.

    lvSavedPosition := vStream.Position;

    lvTmpStream.CopyFrom( vStream, vStream.Size - vStream.Position  ); //Copy the rest of the stream to the temp file.

    vStream.Size := lvSavedPosition;

    Append( RecordData );

    //Add a CRLF
    lvBuffer := #13#10;
    Try
      vStream.Write( lvBuffer[ 1 ], Length( lvBuffer ) );
    Except
      vOpenMode := omRead;
      Raise ECSVFileError.Create( 'Cannot write to stream, switching to read only mode' );
    End;

    lvTmpStream.Seek( 0, soFromBeginning ); //Rewind to the start of the scratch stream;


    vStream.CopyFrom( lvTmpStream, lvTmpStream.Size ); //Copy the entire content back from the scratch stream
    vStream.Position := lvSavedPosition; //Rewind to the start of the record again
  Finally
    FreeAndNil( lvTmpStream );
//    DeleteFile( lvTmpFileName ); //Finished with the file, so delete it.
  End;
  ReadCSVRecordForward; //Now read the next record from the file.
end;

function TCSVFile.IsBOF: Boolean;
begin
  Result := ( vRecordStart = 0 ) or ( vStream.Position = 0 );
end;

function TCSVFile.IsEOF: Boolean;
begin
  Result := GetStreamPos = GetStreamSize;
end;

procedure TCSVFile.Last;
var
  lvReadChar : Char;
begin
  CheckStream;
  vStream.Seek( -1, soFromEnd );

  vRecordStart := GetStreamPos; //put in a false entry, stops the BOF marker firing be mistake.

  //I'm at the end of the file, so just in case there's a spare empty line, read back past the line marker.
  vStream.Read( lvReadChar, SizeOf( lvReadChar ) );
  vStream.Seek( -1, soFromCurrent );

   While lvReadChar In [ #10, #13 ] Do
   Begin
     vStream.Seek( -1, soFromCurrent );
     vStream.Read( lvReadChar, SizeOf( lvReadChar ) );
     vStream.Seek( -1, soFromCurrent );
   End;

  ReadCSVRecordBackward; //Read the first record from the file.
end;

procedure TCSVFile.Next;
begin
  If Not IsEOF
  Then Begin
         CheckStream;
         vRecordStart := GetStreamPos;

         ReadCSVRecordForward; //Read the first record from the file.
       End
  Else Raise ECSVFileError.Create( 'End of file reached' );
end;

procedure TCSVFile.Previous;
Var
  lvReadChar : Char;
begin
  If Not IsBOF
  Then Begin
         CheckStream;
       //  vStream.Seek( vStream.Size, soFromBeginning );
         vStream.Position := vRecordStart - 1;

         //I'm at the start of the next record so

         vStream.Read( lvReadChar, SizeOf( lvReadChar ) );
         vStream.Seek( -1, soFromCurrent );

          While lvReadChar In [ #10, #13 ] Do
          Begin
            vStream.Seek( -1, soFromCurrent );
            vStream.Read( lvReadChar, SizeOf( lvReadChar ) );
            vStream.Seek( -1, soFromCurrent );
          End;

         ReadCSVRecordBackward; //Read the first record from the file.
       End
  Else Raise ECSVFileError.Create( 'Beginning of file reached' );
end;

procedure TCSVFile.ReadCSVRecordBackward;
Var
  lvCurrentData : String;
  lvReadChar : Char;
  lvWithinQuotes : Boolean;
  lvQuotesInARow : Byte;
  lvRecordTermination : Boolean;
  lvRecordEnd : Int64;
Begin
  CheckStream;
  {This procedure will search back through the file and frind the beginning on the previous record.
   Then call ReadCSVRecordForward To do the actual reading}
  SetLength( vFields, 0 );
  lvCurrentData := '';
  lvWithinQuotes := False;
  lvQuotesInARow := 0;
  lvRecordTermination := False;
  Repeat
    //Read a character from the file.
    vStream.Read( lvReadChar, SizeOf( lvReadChar ) );
    vStream.Seek( -1, soFromCurrent );
    //Force stop at the end of the File.
    If IsBOF Then
      lvRecordTermination := True;
    Case lvReadChar of
      ',' : Begin
              lvQuotesInARow := 0;
              If Not lvWithinQuotes
              Then Begin //Found and End of Field marker
                     AddField( lvCurrentData, True );
                   End
              Else Begin //Process like a normal character;
                     ProcessCharacter( lvCurrentData, lvReadChar, True );
                   End;
            End;
      '"' : Begin
              Inc( lvQuotesInARow );
              lvWithinQuotes := Not lvWithinQuotes;
              If ( lvQuotesInARow > 1 ) and lvWithinQuotes Then
              Begin
                //Process like a normal data character
                lvQuotesInARow := 0;
                ProcessCharacter( lvCurrentData, lvReadChar, True );
              End;
            End;
      #13,
      #10 : Begin
              lvQuotesInARow := 0;
              If Not lvWithinQuotes
              Then Begin  //Just found the End of record marker!
                     lvRecordTermination := True;
                   End
              Else Begin
                     //Process like a normal data character
                     ProcessCharacter( lvCurrentData, lvReadChar, True );
                   End;
            End;
      Else Begin
             //Process normal data character
             lvQuotesInARow := 0;
             ProcessCharacter( lvCurrentData, lvReadChar, True );
           End;
    End;
    If Not lvRecordTermination Then
      vStream.Seek( -1, soFromCurrent );
  Until lvRecordTermination;

  AddField( lvCurrentData, True ); //Add the last field.

  lvRecordEnd := vRecordStart + 1;
  vRecordStart := GetStreamPos; //put in a false entry, stops the BOF marker firing be mistake.
  vStream.Position := lvRecordEnd;
End;

procedure TCSVFile.ReadCSVRecordForward;
Var
  lvCurrentData : String;
  lvReadChar : Char;
  lvWithinQuotes : Boolean;
  lvQuotesInARow : Byte;
  lvNotRecordTermination : Boolean;
Begin
  CheckStream;
  {This procedure will read a CSV Record from the stream, assuming that the stream points to the
   first character in the record.}
  SetLength( vFields, 0 );
  lvCurrentData := '';
  lvWithinQuotes := False;
  lvQuotesInARow := 0;
  lvNotRecordTermination := True;
  While lvNotRecordTermination Do
  Begin
    //Read a character from the file.
    vStream.Read( lvReadChar, SizeOf( lvReadChar ) );
    //Force stop at the end of the File.
    If IsEOF Then
      lvNotRecordTermination := False;
    Case lvReadChar of
      ',' : Begin
              lvQuotesInARow := 0;
              If Not lvWithinQuotes
              Then Begin //Found and End of Field marker
                     AddField( lvCurrentData );
                   End
              Else Begin //Process like a normal character;
                     ProcessCharacter( lvCurrentData, lvReadChar );
                   End;
            End;
      '"' : Begin
              Inc( lvQuotesInARow );
              lvWithinQuotes := Not lvWithinQuotes;
              If ( lvQuotesInARow > 1 ) and lvWithinQuotes Then
              Begin
                //Process like a normal data character
                lvQuotesInARow := 0;
                ProcessCharacter( lvCurrentData, lvReadChar );
              End;
            End;
      #13,
      #10 : Begin
              lvQuotesInARow := 0;
              If Not lvWithinQuotes
              Then Begin  //Just found the End of record marker!
                     lvNotRecordTermination := False;
                   End
              Else Begin
                     //Process like a normal data character
                     ProcessCharacter( lvCurrentData, lvReadChar );
                   End;
            End;
      Else Begin
             //Process normal data character
             lvQuotesInARow := 0;
             ProcessCharacter( lvCurrentData, lvReadChar );
           End;
    End;
  End;

  AddField( lvCurrentData ); //Add the last field.

  //Read past any remaining EOL markers.
  Repeat
    vStream.Read( lvReadChar, SizeOf( lvReadChar ) );
  Until IsEOF or ( Not ( lvReadChar In [ #13, #10 ] ) );
  If ( Not IsEOF ) and ( Not ( lvReadChar In [ #13, #10 ] ) ) Then
    vStream.Seek( -1, soFromCurrent );
end;

procedure TCSVFile.Replace(const RecordData: array of String);
var
  lvTmpStream : TStream;  //Temporary file to store the rest of the data in.
//  lvTmpFileName : String;   //Get a temporary filename to use for the scratchpad.
  lvBuffer : String;
  lvRemainderOfFile : Int64;
Begin
  If vOpenMode = omRead Then
    Raise ECSVFileError.Create( 'Stream must be open in write mode' );

  CheckStream;
//  lvTmpFileName := GetTemporaryFileName;
//  lvTmpStream := TFileStream.Create( lvTmpFileName, fmCreate );
//  lvTmpStream := TFileStream.Create( 'd:\test.txt', fmCreate );
  lvTmpStream := TMemoryStream.Create;
  Try
    //First copy off to the end of the file.
    lvRemainderOfFile := vStream.Size - vStream.Position;
    If lvRemainderOfFile = 0
    Then lvTmpStream.Size := 0
    Else lvTmpStream.CopyFrom( vStream, lvRemainderOfFile ); //Copy the rest of the stream to the temp file.

    vStream.Size := vRecordStart;
    vStream.Position := vRecordStart; //Rewind to the start of the record.

    Append( RecordData );

    //Add a CRLF
    lvBuffer := #13#10;
    Try
      vStream.Write( lvBuffer[ 1 ], Length( lvBuffer ) );
    Except
      vOpenMode := omRead;
      Raise ECSVFileError.Create( 'Cannot write to stream, switching to read only mode' );
    End;

    lvTmpStream.Seek( 0, soFromBeginning ); //Rewind to the start of the scratch stream;
    vStream.CopyFrom( lvTmpStream, lvTmpStream.Size ); //Copy the entire content back from the scratch stream
    vStream.Position := vRecordStart; //Rewind to the start of the record again}
  Finally
    FreeAndNil( lvTmpStream );
//    DeleteFile( lvTmpFileName ); //Finished with the file, so delete it.
  End;
  ReadCSVRecordForward; //Now read the next record from the file.
end;

Initialization
  RecordTemination := False;
end.
0
 
LVL 4

Accepted Solution

by:
Colin_Dawson earned 125 total points
Comment Utility
My answer provided a complete method for reading CSV files, this is what was required to answer the question completely.  I feel that I should get some of the points.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now