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.
-
a longer approach would be to save it a csv using COM, and then parse the CSV file and also validate it.user497849– user4978492012-05-03 00:16:58 +00:00Commented May 3, 2012 at 0:16
Add a comment
|
1 Answer
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.
5 Comments
Japster
I will definitely try these two methods and see if I can manage, thanks a lot guys
Japster
@RRUZ I have tried your example code above, but I get an error: [DCC Error] Unit1.pas(26): E2004 Identifier redeclared: 'System.SysUtils'. So I could not not get the program to compile
RRUZ
@Japster, maybe you are using this code inside of another project, if that is the case check which the
SysUtils unit was declared once.Japster
@RRUZ I have sorted out the error, I declared it twice it seems. I used your first example and it worked fine. I just had problems with the elevation, if I switch of the UAC in Windows 7 it works fine, but Im worried if the people using the app does not swich of there UAC the app wont import data