How to save many data tables to one file using tFDMemTable

Question

I imported many Excel worksheets to many tFDMemTables one by one and modified them. Now I try to save them to any kind of ONE file to maintain with tFDMemTable further, no more Excel. How can I make many tFDMemTables to ONE object to save to ONE file, not by APPEND?

I use Delphi 10.3 Community in Windows 10. The reference recommends FireDAC that I use it.

In .Net I make multi table layers into ONE DataSet and ONE XML file with the following simple code. But in Delphi it seems that a data table means a data set. So what is that holds many data tables like .Net DataSet?

DataSet.Tables.Add(Table);

DataSet.WriteXml(FileName);

Answer 1

The code below shows how to save a series of Excel files (workbooks) into rows of an FDMemTable on the basis of one workbook per FDMemTable row. This is done by saving the Excel file in a blob field of the FDMemTable.

The GetFiles method shows how to scan a folder for Excel files and save them to the FDMemTable using the SaveFile method. The FDMemTable row includes the name of the Excel file and the path to where it was found.

When the GetExcelMethod completes, it saves the FDMemTable contents to a file which can then be copied elsewhere.

The WriteFiles method reads the Excel files from the FDMemTable and writes them to a specified directory: this method also shows how to open the file using Excel.

Of course, the techniques shown here are not restricted to Excel files: by adjusting the file mask in the GetFiles, it could find and save files of any type.

  uses [...]  ShellAPI;

  type
    TForm1 = class(TForm)
    [...]
    private
      ID : Integer;  //  used to generate iD field for FDMemTable1
      [...]
    public
    end;

  procedure TForm1.GetFiles(Path : String);
  //  Find all files in a given directory and save them to FDMemTable1
  var
    SearchRec : TSearchRec;
    Res : Integer;
    FN : String;
  begin
    Path := Path + '\*.xl*';
    Res := FindFirst(Path, faAnyFile, SearchRec);
    if Res = 0 {SearchRec.Attr and faAnyFile = faAnyFile} then begin
      repeat
        SaveFile(ExtractFilePath(Path) + SearchRec.Name);
        Res := FindNext(SearchRec);
      until Res <> 0;
      FindClose(SearchRec);
      FN := ExtractFilePath(Application.ExeName) + 'Excelfiles.XML';
      FDMemTable1.SaveToFile(FN, sfXML);
    end;
  end;

  procedure TForm1.SaveFile(FileName : String);
  //  Save an individual file to FDMemTable1
  var
    APath,
    AName : String;
  begin
    APath := ExtractFilePath(FileName);
    AName := ExtractFileName(FileName);

    inc(ID);
    FDMemTable1.Insert;
    FDMemTable1.FieldByName('ID').AsInteger := ID;
    FDMemTable1.FieldByName('FilePath').AsString := APath;
    FDMemTable1.FieldByName('FileName').AsString := AName;
    TBlobField(FDMemTable1.FieldByName('FileData')).LoadFromFile(FileName);
    FDMemTable1.Post;

  end;

  procedure TForm1.WriteFiles;
  //  Extract files from FDMemTable1 to s given directory
  var
    FileName : String;
  begin
    if not FDMemTable1.Active then
      FDMemTable1.Open;
    FDMemTable1.First;
    while not FDMemTable1.Eof do begin
      FileName := FDMemTable1.FieldByName('FileName').AsString;
      TBlobField(FDMemTable1.FieldByName('FileData')).SaveToFile('C:\Temp\'+ FileName);
      // Uncomment the following line to have the file opened in Excel; you'll need to add ShellAPI to your Uses list
      // ShellExecute(Handle, 'Open', PChar('C:\Temp\' + Filename), '','',SW_SHOWNORMAL);
      FDMemTable1.Next;
    end;
  end;

  procedure TForm1.FormCreate(Sender: TObject);
  var
    FieldDef : TFieldDef;
  begin
    ID := 0;
    FieldDef := FDMemTable1.FieldDefs.AddFieldDef;
    FieldDef.Name := 'ID';
    FieldDef.DataType := ftInteger;

    FieldDef := FDMemTable1.FieldDefs.AddFieldDef;
    FieldDef.Name := 'FilePath';
    FieldDef.DataType := ftString;
    FieldDef.Size := Max_Path;

    FieldDef := FDMemTable1.FieldDefs.AddFieldDef;
    FieldDef.Name := 'FileName';
    FieldDef.DataType := ftString;
    FieldDef.Size := Max_Path;

    FieldDef := FDMemTable1.FieldDefs.AddFieldDef;
    FieldDef.Name := 'FileData';
    FieldDef.DataType := ftBlob;

    FDMemTable1.CreateDataSet;
  end;

  procedure TForm1.btnSaveClick(Sender: TObject);
  begin
    GetFiles('D:\aaad7\aaaofficeauto');
  end;

  procedure TForm1.btnWriteFilesClick(Sender: TObject);
  begin
    WriteFiles;
  end;