mdb to txt files

[eric l]

Problem -> I have several thousand db files created by Access that I
need to convert to delimited plain text. Add to that that I'm a
unix/perl hack with next to zero development time on windows platforms
(and hence little clue about coding in VB).

What I'd like to do is be able to write a script to recursively go
through a directory, looking for mdb files, and convering them to tab
delimited text (with the header). I can handle everything by the
final part with Perl.

I've figured out how to do this in Access with TransferText Macros,
but need a solution with a lot less mousing. Suggestions/help/good
literature reference would be greatly appreciated.

Thanks,

-eric

 

[John Nurick]

Hi Eric,

Assuming these are .mdb files and you're just extracting the data from
the tables, you don't need to program Access for this.

Instead, you can stay in Perl and use Win32::OLE to access the DAO
object library (OK, you'll need Access for the DAO and Jet SQL help<g>).
DAO is usually the best way to program the Jet database engine that
manages mdb files.

E.g.
create a DAO.DBEngine object
foreach mdb in your recursive search {
use dbEngine.OpenDatabase method to open the mdb as a
DAO.Database object "dbD"
foreach (dbD.TableDefs) {
EITHER
Get the table name from TableDef.Name
Assemble a Jet SQL "INSERT INTO ... IN ..." query
to create the textfile, and execute it with dbd.Execute
OR
Open a DAO.Recordset object on the table and iterate
through it, writing the field values to the output file.
}
}

 

[eric l]

Thanks John,

Your tip definitely got me going the in the right direction. In case
anyone else is doing something similar, here's some code to help:
-eric

#simple perl script to recursively go through a directory, find all
mdb files, and dump a given table whose headers are known as tab
delimited output

use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

my $d1 = shift;

processDir($d1);

sub processDir
{
my $dir = shift;
opendir (DIR, $dir);
my @dirs;
while (my $d = readdir(DIR))
{
next if $d=~ /^\.\.?$/;
push @dirs, ("$dir/$d") if -d "$dir/$d"; #avoid while recrusion
problem
processMDB ("$dir/$d") if $d =~ /\.mdb$/;
}
foreach my $d (@dirs)
{
processDir($d);
}
}

sub processMDB
{
my $file = shift;
my $Conn = Win32::OLE->new("ADODB.Connection");
my $RS = Win32::OLE->new("ADODB.Recordset");
my$DSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=$file;UID=;PWD=;";
$Conn->Open($DSN);
$file =~ /^(.*?)\.mdb$/;
my $out = $1.".txt"; #outputting files in same place replacing the
..mdb with .txt
open OUT, ">$out";
my @fields = qw(data1 data2 data3); #data headers must be known
for thi script
my $SQL = "SELECT * FROM Objects";
$RS->Open($SQL, $Conn,1,1);
print OUT join ("\t", @fields),"\n"; #print out headers
until ($RS->EOF)
{
my @data;
foreach my $field(@fields)
{
my $obj = $RS->Fields($field);
my $value;
if (defined $obj)
{
$value = $obj->value;
}
else
{
$value = '';
}
push @data, $value;
}
print OUT join ("\t", @data),"\n";
$RS->MoveNext;
}
close OUT;
$RS->Close;
$Conn->Close;

print STDERR $file," Completed\n";
}

 

[John Nurick]

Thanks for the feedback, Eric. It would be easy to modify this to get
the field names from the recordset rather than hard coding them.

 

[david epsom dot com dot au]

Eric, I'm not good at either ADO or perl, but you will
find that the TransferText method from Access maps to
a SQL make-table query:

SELECT Table1.* INTO NewTable
FROM Table1;

Where NewTable is something like:
[Text;DATABASE=c:\].[mytext.txt]

But generally, the only advantage to using direct export
is that it is easier to write: once you have written
code loops to export field values, that gives you greater
control.

(david)