Restoring dataset from delimiter separated values file

Question

In short, i'm new to delphi and I want to achieve the following:

• I have table definition as .cds file {index, data, date}, and some data in .csv format.
• I want to load the .csv file to the table and show log it's changes and errors (ex: invalid date format).

Question

How to solve this task elegantly?

Answer 1

You can read line by line from the .csv, set each line to 'DelimitedText' of a StringList, append a record to the dataset, loop the string list to set each field's value and then post to the dataset.
You can put the 'field value assinging'/'posting' in a try-except block and log any error message of raised exceptions together with information you like (e.g. malformed field value/name, line number, and/or entire line etc.) to a file f.i.

(I don't understand what you mean by 'changes', from what I understood, lines from the .csv will be inserted to a dataset, hence all changes will be inserts.)

edit: To be able to discuss on something concrete (I'm having a hard time grasping the task :))

Sample data (part of CodeGear sample 'Clients.cds'):

Davis;Jennifer;1023495,0000;100 Cranberry St.;Wellesley;MA;02181;516-292-3945;01.01.93 Jones;Arthur;2094056,0000;10 Hunnewell St;Los Altos;CA;94024;415-941-4321;07.02.81 Parker;Debra;1209395,0000;74 South St;Atherton;CA;98765;916-213-2234;23.10.90 Sawyer;Dave;3094095,0000;101 Oakland St;Los Altos;CA;94022;415-948-9998;21.12.89 White;Cindy;1024034,0000;1 Wentworth Dr;Los Altos;CA;94022;415-948-6547;01.10.92

procedure TForm1.FormCreate(Sender: TObject);
begin
  CDS.FieldDefs.Add('LAST_NAME', ftString, 20);
  CDS.FieldDefs.Add('FIRST_NAME', ftString, 20);
  CDS.FieldDefs.Add('ACCT_NBR', ftInteger);
  CDS.FieldDefs.Add('ADDRESS_1', ftString, 30);
  CDS.FieldDefs.Add('CITY', ftString, 15);
  CDS.FieldDefs.Add('STATE', ftString, 2);
  CDS.FieldDefs.Add('ZIP', ftString, 5);
  CDS.FieldDefs.Add('TELEPHONE', ftString, 12);
  CDS.FieldDefs.Add('DATE_OPEN', ftDate);
  CDS.CreateDataSet;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  csv: TextFile;
  Rec: string;
  Fields: TStringList;
  LineNo: Integer;
  i: Integer;
begin
  Fields := TStringList.Create;
  try
    Fields.StrictDelimiter := True;
    Fields.Delimiter := ';';

    AssignFile(csv, ExtractFilePath(Application.ExeName) + 'clients.csv');
    try
      Reset(csv);

      LineNo := 0;
      while not Eof(csv) do begin
        Inc(LineNo);
        Readln(csv, Rec);

        Fields.DelimitedText := Rec;
        CDS.Append;

        for i := 0 to Fields.Count - 1 do
          try
            CDS.Fields[i].Value := Fields[i];   // Variant conversion will raise
                                 // exception where conversion from string fails
          except
            on E:EDatabaseError do begin
              CDS.Cancel;        // Failed, discard the record

              // log the error instead of showing a message
              ShowMessage(Format('Cannot set field "%s" at line %d' + sLineBreak +
                  'Error: %s', [CDS.Fields[i].FieldName, LineNo, E.Message]));
              Break;             // Continue with next record
            end;
          end;

        if CDS.State = dsInsert then // It's not dsInsert if we Cancelled the Insert
          try
            CDS.Post;
          except
            on E:EDatabaseError do begin
              // log error instead of showing
              ShowMessage(Format('Cannot post line %d' + sLineBreak + 'Error: %s',
                  [LineNo, E.Message]));
              CDS.Cancel;
            end;
          end;

      end;
    finally
      CloseFile(csv);
    end;
  finally
    Fields.Free;
  end;
end;

procedure TForm1.CDSBeforePost(DataSet: TDataSet);
begin
  // Superficial posting error
  if CDS.FieldByName('LAST_NAME').AsString = '' then
    raise EDatabaseError.Create('LAST_NAME cannot be empty');
end;