//**This page is in the 'old' namespace, and was imported from our previous wiki. 
We recommend checking for more up-to-date information using the search box.**//

====== Excel Processing Using DatabasePlugin ======
This describes my experience of getting DBPlug to process MS excel files.

  * My test collection is called testexce installed in /research/shaoqun/testing/gsdl(on Linux).
  * My excel file was called phonebook.xls and was put in my collection's directory (i.e. /research/shaoqun/testing/gsdl/collect/testexce). This location doesn't matter. If it goes in the import directory then you will either get warnings about how no plugin could process the file, or it will be inappropriately processed by another plugin as well as DBPlug.
  * I copied /research/shaoqun/testing/gsdl/etc/packages/example.dbi into the import directory of my collection.
  * This file was modified by setting the following variables:
<code>
 $db='DBI:Excel:file=/research/shaoqun/testing/gsdl/collect/testexce/phonebook.xls';
</code>
<code>
 $sql_query = 'SELECT * FROM Sheet1';
</code>

**Important**: the perl excel driver module used in this testing assumes TABLE = Worksheet and the contents of first row of each worksheet as column name. (//Sheet1// is the name of the first worksheet of phonebook.xls). Be aware that the worksheet name is case-sensitive.
<code>
%db_to_greenstone_fields=(
    "name" => "Title",
    "address" => "text",
    "id" => "Identifier",
);
</code>


This is a mapping between column names in the //Sheet1// worksheet, and metadata names in the Greenstone archive files.
  * Then I built the collection.
**Notes about Perl modules.**
  * download following modules from [[http://search.cpan.org/|CPAN]]
    * DBI
    * DBD::Excel
    * Spreadsheet::ParseExcel
    * Spreadsheet::WriteExcel
    * SQL::Statement
    * OLE::Store_Lite
  * install these modules: make sure you have run 'setup.bat' or 'source setup.bash' in your greenstone directory first)
  * put them in $GSDLHOME/packages/cpan.
  * untarred them (tar xzvf file.tar.gz)
  * run
<code>
 perl Makefile.PL INSTALLSITELIB="$GSDLHOME/perllib/cpan/perl-5.8" <br/> PREFIX="$GSDLHOME/perllib/cpan/XXX" SITEPREFIX="$GSDLHOME/perllib/cpan" 
 make 
 make test
 make install
</code>
This installs the modules into $GSDLHOME/perllib/cpan/perl-5.8

(XXX in the perl line should be set to the first component of the module name, e.g. DBI,DBD,SQL,etc)

//Note://for more information about using DBD::Excel, please refer to the perl files in the //sample// directory included in the Module.