Import data from Excel

Question

I want to import data from Excel into a Paradox database on Delphi 7.

How can I do that using Delphi?

Answer 1

Well, if you have the question tagged as Delphi 7 I assume you have BDE. This will enable you to do the whole process in Delphi.

You can modify and use this untested code (you will need to add the exception handling):

procedure TForm1.Button2Click(Sender: TObject);
var
  Cols: integer;
  Rows: integer;
  IntCellValue: integer;
  Excel, XLSheet: Variant;
  failure: Integer;

begin
  failure:=0;
  try
    Excel:=CreateOleObject('Excel.Application');
  except
    failure:=1;
  end;
  if failure = 0 then
  begin
    Excel.Visible:=False;
    Excel.WorkBooks.Open(<Excell_Filename>);
    XLSheet := Excel.Worksheets[1];
    Cols := XLSheet.UsedRange.Columns.Count;
    Rows := XLSheet.UsedRange.Rows.Count;

    // Value of the 1st Cell
    IntCellValue:=Excel.Cells[1, 1].Value;
    // Iterate Cals/Rows to read the data section in your worksheet
    // and you can write it in Paradox using the BDE by iterating all cells
    // somthing like this pseudo code:

      try            
        Query := TQuery.Create(nil)            
        Query .Databasename := PdxDBName; // must be configured in the BDE
        while Rows > 0
        begin
          while Cols > 0
          begin
            IntCellValue:=Excel.Cells[Cols,Rows].Value;
            Query .SQL.text := // SQLStmt including the IntCellValue
            ExecSQL;
            dec(Cols);               
          end;
          Cols := XLSheet.UsedRange.Columns.Count;
        Dec(Rows);
        end;
      finally
        Query.Free;
      end;

    Excel.Workbooks.Close;
    Excel.Quit;
    Excel:=Unassigned;
  end;
end;

Answer 2

What about CSV from Excel then import the CSV into Paradox DB? You may also try exporting XML from Excel, then load the XML into Padadox DB programmatically.

Answer 3

Use the OLEDB Provider and ADO component in Delphi 7 to achieve this. It should be simple to do, and you can work with Excel querying via SQL queries.

Use the TADO component to get the data and then use BDE components like TQuery to import the data to the Paradox table.

Answer 4

This tool SMImport says it can do it. While they want $50 for it, you can download a free trial version.