Title: Exporting Access Tables and Queries
Question: How can I use Delphi to export tables or queries from Microsoft Access to other files?
Answer:
There are several components around in the web to accomplish this task. But in many cases, this can also be accomplished simply with SELECT INTO queries. The exact possibilities of this query type depend on the database type used. But the following procedures work at least for Access databases accessed by ADO and JET. The syntax for this query is [1, 2]:
SELECT Field1[, Field2[, ...]]
INTO NewTable [IN ExternalDatabase]
FROM Source [WHERE, ORDER BY etc. as usual]
An identifier for the format of the external database (such as EXCEL 8.0;) has to be included in the IN clause. A list of supported values is published by Microsoft in Ref. [3].
For simplification, Delphi functions are provided in the attached unit ADOExport.pas. They allow export of tables and queries into external files.
function ADOExportTable(AConnection:TADOConnection;
ASourceTable,AFieldList,ADestFile:string; AFormat:TExportFormat;
ADestTableName:string) : integer;
function ADOExportQuery(AConnection:TADOConnection;
ASourceSQL,ADestFile:string; AFormat:TExportFormat;
ADestTableName:string) : integer;
- ASourceTable is the name of the table to be exported. For queries, place the SQL string into the ASourceSQL parameter.
- For ADOExportTable only: AFieldList is a string containing the names of the fields (columns) to be exported. Multiple field names have to be separated by commas. Use * (or an empty string) in order to export all fields.
- ADestFile indicates the name of the exported file. Existing files will be overwritten without any warnings.
- AFormat species the format of the resulting file, for example dBase, Paradox, Excel, Text, HTML, and others. The possible values are indicated in the TExportFormat type declaration in the source code section below. Please note that the value for Lotus WK4 files apparently does not work.
- The parameter ADestTableName denotes the name of the exported table. It is evaluated only for those file types where several tables can be embedded in the same file, such as Excel 5 or later versions. In the other cases the table name is given by the file name (ADestFile). If the table already exists in the destination database an exception will be raised.
- The function returns the number of records exported or -1, in case of an error.
In the source code below, an additional function was added for export to XML files by calling the ADODatasets own SaveToFile method directly.
References:
----------
[1] Microsoft Help file JETSQL40.chm, to be found in the Office directory under C:\Program Files\Common Files\Microsoft Shared.
[2] http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html
[3] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adoproperties.asp, (section Extended Properties Property Settings)
-------------------- code below --------------------
unit ADOExport;
interface
uses
SysUtils, db, ADODB;
type
TExportFormat = (
efMDB,
efExcel3, efExcel4, efExcel5, efExcel8,
efLotusWK1, efLotusWK3, efLotusWK4, // Lotus WK4 is not working
efDBase3, efDBase4, efDBase5,
efParadox3, efParadox4, efParadox5,
efText,
efHTML,
efXML
);
const
ExportFilter = // for SaveDialog!
'Access (*.mdb)|*.mdb|'+
'Excel 3.0 (*.xls)|*.xls|'+
'Excel 4.0 (*.xls)|*.xls|'+
'Excel 5.0/95 (*.xls)|*.xls|'+
'Excel 97/2000 (*.xls)|*.xls|'+
'Lotus WK1 (*.wk1)|*.wk1|'+
'Lotus WK3 (*.wk3)|*.wk3|'+
'Lotus WK4 (*.wk4)|*.wk4|'+
'dBase III (*.dbf)|*.dbf|'+
'dBase IV (*.dbf)|*.dbf|'+
'dBase 5 (*.dbf)|*.dbf|'+
'Paradox 3.x (*.db)|*.db|'+
'Paradox 4.x (*.db)|*.db|'+
'Paradox 5.x (*.db)|*.db|'+
'Text-Dateien (*.txt)|*.txt|'+
'HTML-Dateien (*.htm)|*.htm|'+
'XML-Dateien (*.xml)|*.xml';
function ADOExportTable(AConnection:TADOConnection;
ASourceTable,AFieldList,ADestFile:string;
AFormat:TExportFormat; ADestTableName:string) : integer;
function ADOExportQuery(AConnection:TADOConnection;
ASourceSQL,ADestFile:string; AFormat:TExportFormat;
ADestTableName:string) : integer;
function ADOExportToXML(AConnection:TADOConnection;
ASourceSQL,ADestFile:string) : integer;
implementation
uses
Classes;
const
SQL_Mask = 'SELECT %s INTO [%s] IN "%s" %s FROM %s';
// | | | | |
// fields | | | |
// dest table | | |
// dest database | |
// database format |
// source dataset
DBCodes : array[TExportFormat] of string = (
'',
'"Excel 3.0;"', '"Excel 4.0;"', '"Excel 5.0;"', '"Excel 8.0;"',
'"Lotus WK1;"', '"Lotus WK3;"', '"Lotus WK4;"',
'"dBASE III;"', '"dBASE IV;"', '"dBASE 5.0;"',
'"Paradox 3.x;"', '"Paradox 4.x;"', '"Paradox 5.x;"',
'"Text;"',
'"HTML Export;"',
''
);
//------------------------------------------------------------------------
function ADOExportToXML(AConnection:TADOConnection;
ASourceSQL,ADestFile:string) : integer;
var
Dataset : TADODataset;
begin
result := -1;
Dataset := TADODataset.Create(nil);
try
Dataset.Connection := AConnection;
Dataset.CommandText := ASourceSQL;
Dataset.CommandType := cmdText;
Dataset.Open;
Dataset.SaveToFile(ADestFile, pfXML);
result := Dataset.RecordCount;
finally
Dataset.Free;
end;
end;
//------------------------------------------------------------------------
function ADOExportQuery(AConnection:TADOConnection;
ASourceSQL,ADestFile:string;
AFormat:TExportFormat; ADestTableName:string) : integer;
const
WhiteSpace = [' ', #13, #10, #9];
Ls = 6; // = Length('SELECT');
Lf = 4; // = Length('FROM');
var
SQL : string;
p, p1, p2, p3 : integer;
done : boolean;
sql1, sql2, _sql : string;
begin
result := -1;
if (ASourceSQL='') or (ADestFile='') or (AConnection=nil) then
raise Exception.Create('Incomplete parameters.');
if AFormat=efXML then begin
result := ADOExportToXML(AConnection, ASourceSQL, ADestFile);
end else begin
while (ASourceSQL'') and (ASourceSQL[1] in WhiteSpace)
do System.Delete(ASourceSQL, 1, 1);
_sql := Uppercase(ASourceSQL) + ' ';
p1 := Ls+1;
while (_sql'') and (_sql[p1] in WhiteSpace) do inc(p1);
p2 := 0;
done := false;
while not done do begin
p := pos('FROM', _sql);
if p0 then begin
done := (_sql[p-1] in WhiteSpace) and (_sql[p+Lf+1] in WhiteSpace);
if not done then begin
p2 := p2 + p;
_sql := copy(_sql, p+Lf, Length(_sql));
end;
end else
done := true;
end;
p3 := p2 + Lf;
while (p20) and (ASourceSQL[p2] in WhiteSpace)
do dec(p2);
while (p3and (ASourceSQL[p3] in WhiteSpace)
do inc(p3);
sql1 := copy(ASourceSQL, p1, p2-p1-1); // between "SELECT" and "FROM"
sql2 := copy(ASourceSQL, p3, Length(ASourceSQL)); // part after "FROM"
result := ADOExportTable(AConnection, sql2, sql1, ADestFile, AFormat,
ADestTableName);
end;
end;
//-----------------------------------------------------------------------
function ADOExportTable(AConnection:TADOConnection;
ASourceTable,AFieldList,ADestFile:string;
AFormat:TExportFormat; ADestTableName:string) : integer;
var
SQL : string;
wasConn : boolean;
fdir : string;
fnam : string;
L : TStringList;
begin
result := -1;
if (ASourceTable='') or (ADestFile='') or (AConnection=nil) then
raise Exception.Create('Incomplete parameters.');
if AFieldList='' then AFieldList := '*';
if AFormat=efXML then begin
SQL := Format('SELECT %s FROM %s', [AFieldList, ASourceTable]);
result := ADOExportToXML(AConnection, SQL, ADestFile);
end else begin
fdir := ExtractFileDir(ADestFile);
fnam := ExtractfileName(ADestFile);
case AFormat of
efDBase3,
efDBase4,
efDBase5,
efParadox3,
efParadox4,
efParadox5,
efLotusWK1,
efText,
efHTML :
begin
if FileExists(ADestFile) then DeleteFile(ADestFile);
SQL := Format(SQL_Mask,
[AFieldList, fnam, fdir, DBCodes[AFormat], ASourceTable]);
end;
efLotusWK3,
//efLotusWK4, -- not working !
efExcel3,
efExcel4 :
begin
if FileExists(ADestFile) then DeleteFile(ADestFile);
SQL := Format(SQL_Mask,
[AFieldList, fnam, ADestFile, DBCodes[AFormat], ASourceTable]);
end;
efExcel5,
efExcel8 :
begin
if FileExists(ADestFile) then DeleteFile(ADestFile);
SQL := Format(SQL_Mask,
[AFieldList, ADestTableName, ADestFile, DBCodes[AFormat],
ASourceTable]);
end;
efMDB :
SQL := Format(SQL_Mask,
[AFieldList, ADestTableName, ADestFile, DBCodes[AFormat],
ASourceTable]);
else
raise Exception.Create('ExportTable: Fileformat not supported.');
end;
with AConnection do begin
wasConn := Connected;
Connected := true;
Execute(SQL, result);
if not wasConn then Connected := false;
end;
end;
end;
end.