Problems reading csv file

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]);

:)

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.