This tutorial shows how to Import data table in MS Word into
Database, in this tutorial I will use Database MS SQL, you can use another database
product, because here I use standard SQL format.
1.
Prepare Ms Word file with data table in it, for
example my MS Word as follow
2.
Open Delphi
3.
Create new Application
4.
Setup the form as follow
Component
|
Property
|
Value
|
TButton
|
Name
|
Button1
|
Caption
|
Open MsWord File and Inser into
Database
|
|
TOpenDialog
|
Name
|
Opendialog1
|
TAdoquery
|
Name
|
Q1
|
Connection String
|
<assign to your database connection string>
|
5.
Prepare array data type to storing data table
from Ms Word file
6.
Create some functions for the process
As you see on the image there are 4
functions will be created in this tutorial.
Lets me explain the functionality of each function
1.
getSqlCreatetable
It will return SQL script to create a table
2.
getSqlInserttable
It will return SQL script to insert data into a table
3.
executeSQL
It will execute SQL script that returned by function number 1 and number
2
4.
getMSWordTable
it
will return arrays that contain data tables in MsWord file
The
content of each function as follow
function TForm1.executeSQL
(inSQL : string) : boolean;
begin
result := true;
Q1.close;
Q1.SQL.Clear;
Q1.SQL.Text := inSQL;
try
Q1.ExecSQL;
except
result := false;
end;
end;
|
function
TForm1.getSqlCreatetable(inTableName : string;inContent : array of string) :
string;
var sql : string;
i : integer;
begin
sql := 'create table '+
ExtractFileName(inTableName) + '(';
for i:=0 to
length(inContent)-1 do
if i < length(inContent)-1 then
sql := sql + 'COL'+
inttostr(i)+' varchar(255),'
else
sql := sql + 'COL'+
inttostr(i)+' varchar(255))';
result := sql;
end;
|
function
TForm1.getSqlInserttable(inTableName : string;inContent : array of string) :
string;
var sql : string;
i : integer;
begin
sql := 'insert into '+
ExtractFileName(inTableName) + ' values (';
for i:=0 to length(inContent)-1
do
if i < length(inContent)-1 then
sql := sql +
QuotedStr(inContent[i]) +','
else
sql := sql +
QuotedStr(inContent[i]) +')';
result := sql;
end;
|
function
TForm1.getMSWordTable(inFileName : string) : TTables;
var
MSWord, Table: OLEVariant;
iRows, iCols, i, x, y,
iNumTables : Integer;
CellData: widestring;
resultTables : TTables;
begin
SetLength(resultTables,0);
result := resultTables;
try
MSWord :=
CreateOleObject('Word.Application');
except
Exit;
end;
try
MSWord.Visible := False;
// Open file
MSWord.Documents.Open(inFileName);
// Get number of tables
iNumTables :=
MSWord.ActiveDocument.Tables.Count;
SetLength(resultTables,iNumTables);
for i:=1 to iNumTables
do
begin
// Read table
Table :=
MSWord.ActiveDocument.Tables.Item(i);
// get Row Number and Col Number
iCols :=
Table.Rows.Count;
iRows :=
Table.Columns.Count;
// Set Table Name
resultTables[i-1].name :=
MidStr(ExtractFileName(inFileName),1, length(ExtractFileName(inFileName)) -
length(ExtractFileExt(inFileName))) + inttostr(i) +
formatdatetime('yyyymmddhhnnss',now);
// Initialize Array
setLength(resultTables[i-1].data,iRows);
for x:=0 to
length(resultTables[i-1].data)-1 do
setLength(resultTables[i-1].data[x],iCols);
// loop through cells
for x := 1 to iRows do
for y := 1 to iCols do
begin
CellData :=
Table.Cell(y, x).Range.FormattedText;
if not VarisEmpty(CellData) then
begin
// Remove Tabs
CellData :=
StringReplace(CellData,#$D, '', [rfReplaceAll]);
// Remove linebreaks
CellData :=
StringReplace(CellData,#$7, '', [rfReplaceAll]);
// Load Cell data
resultTables[i-1].data[y-1][x-1] := CellData;
end;
end;
end;
finally
MSWord.Quit;
end;
result := resultTables;
end;
|
7.
Create On Click event for Button1, with script as follow
procedure TForm1.Button2Click(Sender: TObject);
var data : TTables;
numTable : integer;
iTable,iRow, iCol :
integer;
content : array of string;
fileName : string;
begin
if
OpenDialog1.Execute then
begin
fileName := OpenDialog1.FileName;
// check availability of file
if
not FileExists(fileName) then
begin
MessageDlg('Invalid file',
mtError,[mbOk],0 );
exit;
end;
// check file extensions
if
ExtractFileExt(fileName) <> '.doc' then
begin
MessageDlg('Invalid format', mtError,[mbOk],0
);
exit;
end;
data := getMSWordTable(fileName);
end
else
exit;
// get Number of
tables
numTable :=
length(data);
for
iTable:= 0 to numTable-1 do
begin
for
iRow := 0 to
length(data[iTable].data)-1 do
begin
SetLength(content,
length(data[iTable].data[iRow]));
for
iCol := 0 to
length(data[iTable].data[iRow])-1 do
content[iCol] :=
data[iTable].data[iRow][iCol] ;
if
iRow = 0 then
begin
// create table with 1st row as
header
if
not
executeSQL(getSqlCreatetable(data[iTable].name, content)) then
break;
end
else
begin
// insert the datas
if
not
executeSQL(getSqlInserttable(data[iTable].name, content)) then
break;
end;
end;
end;
MessageDlg('Finished',mtInformation,[mbOk],0);
end;
|
8.
Now it’s ready for test, run the application by
pressing F9 then open the MsWord file that you have prepared
Wait until finished dialog information appear
9.
Check the result into Database
Finish. You can download the source code from
download menu.
