insert records from an excel file to a DB MS access delphi

Question

insert records from an excel file to a DB MS access delphi

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm doing a program that imports Excel into the database, This is my code:

var
importdir, ipo: string;
begin
if OpenDialog1.Execute then
begin
importdir := ExtractFileDir( OpenDialog1.FileName );
ipo := OpenDialog1.FileName;
end;
ADOConnection1.Execute( 'insert into archivo select * from [Excel 8.0;  database='+ipo+'].hoja1' );
end;

When I select the file I get this error

The Microsoft Jet could not find the object 'leaf1' Make sure the object exists and the path is correct.

delphi excel ms-access

asked by pablo 11.09.2018 в 01:22

source

2 answers

Problems with Context in BroadcastReceiver: "Attempt to invoke virtual method 'android.database.sqlite.SQLiteDatabase openOrCreateDatabase" Call Controller with parameters from radio button click event

score 0 · Answer 1

Given that the connections with ADO must be different to connect to Excel and MSAccess (dodo not say that I assume you are doing it with ADO), I think the solution is to insert the records one by one. Create 2 connections one for Excel and one for Access.

Provider = Microsoft.Jet.OLEDB.4.0; Password=""; Data Source =. \ DataExcel.xls; Extended Properties="Excel 8.0; HDR = Yes; IMEX = 1"; Persist Security Info = True

.

Provider = Microsoft.Jet.OLEDB.4.0; Password=""; Data Source = .DataAccess.mdb; Persist Security Info = True

From there, it's about reading records using an ADOQuery / ADOTable from one connection and inserting them using the other.

score 0 · Answer 2

As a general rule, when you make queries using the JetEngine against Excel, the sheet names are followed by a dollar symbol, so it will probably work for you just by changing sheet1 by sheet1 $, something like this:

begin
  if OpenDialog1.Execute then
    ADOConnection1.Execute( 'insert into archivo select * from [Excel 8.0;  database=' + OpenDialog1.FileName + '].hoja1$');
end;

Greetings.