How to import an Excel file into Access database, using delphi

Question

Im writing a database application, using Delphi and need to import data on a excel sheet and save it in a access database. I have no idea how to do this, what components to use, or if it is even possible, can you please help me.

Answer 1

You have several options, try one of these

1) using the DoCmd.TransferSpreadsheet function , this method is simpler but not very flexible.

{$APPTYPE CONSOLE}

{$R *.res}


uses    
  SysUtils,
  ActiveX,
  ComObj;

procedure ImportDataAccess(const AccessDb, TableName, ExcelFileName:String);
Const
  acQuitSaveAll             = $00000001;
  acImport                  = $00000000;
  acSpreadsheetTypeExcel9   = $00000008;
  acSpreadsheetTypeExcel12  = $00000009;
var
 LAccess : OleVariant;
begin
 //create the COM Object
 LAccess := CreateOleObject('Access.Application');
 //open the access database
 LAccess.OpenCurrentDatabase(AccessDb);//if the access database doesn't exist use the NewCurrentDatabase method instead.
 //import the data
 LAccess.DoCmd.TransferSpreadsheet( acImport, acSpreadsheetTypeExcel9, TableName, ExcelFileName, True);
 LAccess.CloseCurrentDatabase;
 LAccess.Quit(1);
end;

begin
 try
    CoInitialize(nil);
    try
      ImportDataAccess('C:\Data\Database1.accdb','Sales','C:\Data\Sales.xlsx');
      Writeln('Done');
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.

2) using the ado components, a more flexible way.

{$APPTYPE CONSOLE}

{$R *.res}

uses
  Data.DB,
  Data.Win.ADODB,
  SysUtils,
  ActiveX,
  ComObj;


procedure ImportDataADO(const AccessDb, TableName, ExcelFileName:String);
var
  LAdoQueryExcel  : TADOQuery;
  LADOAccesCmd    : TADOCommand;
begin
   LAdoQueryExcel:=TADOQuery.Create(nil);
   LADOAccesCmd:=TADOCommand.Create(nil);
   try
    //set the connection string for access
    LADOAccesCmd.ConnectionString:=Format('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;',[AccessDb]);
    LADOAccesCmd.Parameters.Clear;
    LADOAccesCmd.CommandText:='INSERT INTO Sales (id,name) VALUES (:id,:name)';
    LADOAccesCmd.ParamCheck:=False;

    //set the connection string for excel
    LAdoQueryExcel.ConnectionString:=Format('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"',[ExcelFileName]);
    LAdoQueryExcel.SQL.Add('SELECT * FROM [Sheet1$]');
    LAdoQueryExcel.Open;
    while not  LAdoQueryExcel.eof do
    begin
      LADOAccesCmd.Parameters.ParamByName('id').Value      := LAdoQueryExcel.FieldByname('id').AsInteger;
      LADOAccesCmd.Parameters.ParamByName('name').Value    := LAdoQueryExcel.FieldByname('name').AsString;
      LADOAccesCmd.Execute;
      LAdoQueryExcel.Next;
    end;
   finally
     LAdoQueryExcel.Free;
     LADOAccesCmd.Free;
   end;
end;


begin
 try
    CoInitialize(nil);
    try
      ImportDataADO('C:\Datos\Database1.accdb','Sales','C:\Datos\Sales.xlsx');
      Writeln('Done');
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.