How to Import MS Word data table into Database





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.

Related product you might see:

Share this product :
 
Delphi Programming Tutorial © Mang Yadi Site 2015