Parse the lines of a text file and import them into a Paradox table

Problem/Question/Abstract:

I have a text file with a certain format where only the first line is of type year and month. The rest is always the same: Integer, String, String, Integer, Integer, Integer. Example:

2001,10
000368,"The Name","Category",000671000,0724690,009421
000701,"The Name","Category",000398500,0398500,005181

What's the best way to import this into Paradox tables?

Answer:

Solve 1:

I would read it one line at a time and parse it with something like the following parser. The variable ofs needs to be set to zero to start the parsing at the beginning of the line.

{ ... }
ReadLn(f, line);
ofs := 0;
if GetNextSepValueOK(line, ofs, YrStr, ', ', '"') and
        GetNextSepValueOK(line, ofs, MoStr, ', ', '"') then
  {prep date}
else
  raise Exception.Create('Cannot find year and month');
while not EOF(f) do
begin
  ReadLn(f, line);
  ofs := 0;
  {Do Append and try, etc. }
  while GetNextSepValueOK(line, ofs, value, ', ', '"') do
    {Do Post}
end;
end;
{ ... }

function GetNextSepValueOK(const line: string; var ofs: integer; out value: string;
  const Separator, Grouper: char): Boolean;
var
  i, oc, lnb, GrouperCount: integer;
  c: char;
  temp: ShortString;
begin
  oc := 0;
  lnb := 0;
  GrouperCount := 0;
  i := ofs;
  while (ofs < length(line)) do
  begin
    c := line[ofs + 1];
    if not Odd(GrouperCount) and (c = Separator) then
      break
    else if c = Grouper then
    begin
      inc(GrouperCount);
      if odd(GrouperCount) and (ofs > i) and (line[ofs] = Grouper) then
      begin
        inc(oc);
        temp[oc] := Grouper;
      end;
    end
    else if (c > ' ') or (lnb > 0) or odd(GrouperCount) then
    begin
      inc(oc);
      temp[oc] := c;
    end;
    if (c > ' ') or odd(GrouperCount) then
      lnb := oc;
    inc(ofs);
  end;
  if (ofs < length(line)) and (line[ofs + 1] = Separator) then
  begin
    inc(ofs);
    Result := true;
  end
  else
    Result := (i < length(line)) and not Odd(GrouperCount);
  if Result then
  begin
    temp[0] := char(lnb);
    value := temp;
  end;
end;


Solve 2:

procedure TForm1.ImportFile(const filename: string);
var
  F: Textfile;
  year, month: Integer;
  line: string;
  sl: Tstringlist;
begin
  Assignfile(F, filename);
  Reset(F);
  try
    ReadLn(F, line);
    sl := TStringlist.Create;
    try
      sl.QuoteChar := '"';
      sl.Commatext := line;
      year := StrToInt(sl[0]);
      month := StrToInt(sl[1]);
      while not EOF(F) do
      begin
        Readln(line);
        sl.Commatext := line;
        SaveRecord(sl);
      end;
    finally
      sl.free
    end;
  finally
    Closefile(f)
  end;
end;


The Saverecord method would be something like:

procedure Tform1.SaveRecord(sl: TStringlist);
begin
  if sl.Count <> 6 then
    raise Exception.Create('Invalid record');
  table1.Append;
  table1['ID'] := sl[0];
  table2['Name'] := sl[1];
  { ... }
  table1.Post;
end;


Solve 3:

You can use the CommaText property of a TStringList to parse the lines. Something like this:

procedure ReadFile(FileName: string);
var
  F: TextFile;
  S: string;
  List: TStringList;
  i: integer;
begin
  AssignFile(F, FileName);
  Reset(F);
  List := TStringList.Create;
  try
    Readln(F, S);
    List.CommaText := S;
    {do whatever you want with first line}
    while not EOF(F) do
    begin
      List.Clear;
      ReadLn(F, S);
      List.CommaText := S;
      {List now contains the integers and strings as separate strings}
      MyTable.Append;
      for i := 0 to 5 do
        MyTable.Fields[i].AsString := List.Strings[i];
      MyTable.Post;
    end;
  finally
    List.Free;
  end;
  closefile(f);
end;