Grid out of range error when importing CSV file

Question

I am following this tutorial on importing CSV file to Delphi. I drafted the code provided below. The program compiles with no problems but when I attempt to execute function to read the file I get the grid out of range error message.

unit geoimp;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ComCtrls, Vcl.Buttons, Vcl.StdCtrls,
  Vcl.Grids, Vcl.DBGrids, Data.DB, Datasnap.DBClient;


 const
  shfolder = 'ShFolder.dll';

type
  TMainForm = class(TForm)
    MainPageControl: TPageControl;
    ImportTab: TTabSheet;
    MapPreviewTab: TTabSheet;
    GeoMatchingTab: TTabSheet;
    ImportLbl: TLabel;
    SlctImportDta: TSpeedButton;
    MainOpenDialog: TOpenDialog;
    MainListBox: TListBox;
    SG1: TStringGrid;
    procedure SlctImportDtaClick(Sender: TObject);

  private
    { Private declarations }
    procedure ParseRecord(sRecord: string; Row: integer);
     procedure ReadCSVFile;

  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation

{$R *.dfm}





procedure TMainForm.ParseRecord(sRecord: string; Row: integer);
var
  Col, PosComma: integer;
  sField: string;
begin
  sRecord := StringReplace(sRecord, '"', '',
                           [rfReplaceAll]    ); // 1.
  Col := 0;    // first column of stringgrid
  repeat
    PosComma := Pos(',', sRecord);              // 2.
    if PosComma > 0 then
      sField := Copy(sRecord, 1, PosComma - 1)  // 3.a
    else
      sField := sRecord;                        // 3.b
    SG1.Cells[Col, Row] := sField;              // 4.
    if PosComma > 0 then begin                  // 5.
      Delete(sRecord, 1, PosComma);
      Col := Col + 1;                           // next column
    end;
  until PosComma = 0;                           // 6.
end;

procedure TMainForm.ReadCSVFile;
var
  FileName1, sRecord: string;
  Row: integer;
begin
  FileName1 := MainOpenDialog.FileName;
  MainListBox.Items.LoadFromFile(FileName1);
  SG1.RowCount := MainListBox.Items.Count;

  for Row := 0 to MainListBox.Items.Count - 1 do begin
    sRecord := MainListBox.Items[Row];
    ParseRecord(sRecord, Row);
  end;

  // 5. Select first "data" cell
  SG1.Row := 1;
  SG1.Col := 0;
  SG1.SetFocus;

end;


procedure TMainForm.SlctImportDtaClick(Sender: TObject);
begin

  // Create the open dialog object - assign to our open dialog variable
  MainOpenDialog := TOpenDialog.Create(self);

  // Set up the starting directory to be the current one
  MainOpenDialog.InitialDir := GetCurrentDir;

  // Only allow existing files to be selected
  MainOpenDialog.Options := [ofFileMustExist];

  // Allow only .dpr and .pas files to be selected
  MainOpenDialog.Filter :=
    'CSV Files|*.csv';

  // Select pascal files as the starting filter type
  MainOpenDialog.FilterIndex := 2;

  // Display the open file dialog
  if MainOpenDialog.Execute
  then ReadCSVFile
  else ShowMessage('Open file was cancelled');

  // Free up the dialog
  MainOpenDialog.Free;
end;

end.

How big did you make the StringGrid (rows and columns) ? How many rows and columns of data are in the CSV file? — crefird, Jul 13 '14 at 00:32
@David Schwartz has given you a good answer to this. More generally, it's a good idea to get into the habit of anticipating problems as you write your code, like "Will my Col value access a valid column in my grid and what do I do if it won't." You know what they say: "Expect the unexpected." — MartynA, Jul 13 '14 at 07:12
Judging by use of `StringReplace(..., [rfReplaceAll])` to deal with `Char` (and other stuff) this tutorial is flawed. Did you see [`TStrings.CommaText`](http://docwiki.embarcadero.com/Libraries/XE2/en/System.Classes.TStrings.CommaText)? (just remember about `StrictDelimiter`) — Free Consulting, Jul 13 '14 at 07:33

David Schwartz · Accepted Answer · 2014-07-13T05:47:18.000

Without seeing the data, you've made some dangerous assumptions by removing the quotes from the input strings. It's perfectly valid to have commas embedded inside of quoted strings in CSV files -- indeed, that's why they allow quoted strings. All you need is one embedded comma in one record and it'll blow up if you don't have enough columns defined.

You don't show how you're setting the grid's ColCount. By default it's set to 5.

The FixedRows/FixedColumns values need to be accommodated in the RowCount/ColCount as well.

Above line 6 you could insert this:

if (col >= (SG1.ColCount+SG1.FixedColumns)) then
  SG1.ColCount := SG1.ColCount + 1;

That will grow the number of columns in the grid and allow you to see the results of any errant commas embedded inside of quoted strings in your CSV data.

In the 3rd line of ReadCSVFile where you set SG1.RowCount, it will be short if SG1.FixedRows > 0.

These are all possible causes of the exceptions you're getting.

Grid out of range error when importing CSV file

1 Answers1