http://qs1969.pair.com?node_id=474068

chicago928 has asked for the wisdom of the Perl Monks concerning the following question:

Hello all. I have a very simple question. I have a bunch of MS ACCESS files that have tables in them. If these tables were in .CSV format I would be able to work with them just fine. I'm totally unfamiliar though with DBI or DBD and SQL queries. Can anyone post a quick dirty solution for how to just export the table data?

Thanks

PROBLEM SOLVED THANKS TO THE GENIUS THAT IS BART!!

Here is a link to a set of tools that you can manipulate ms access files from: http://mdbtools.sourceforge.net/install/x53.htm

UPDATE 2:

These tools are really complicated.

Replies are listed 'Best First'.
Re: exporting MS ACCESS tables into a CSV file
by davidrw (Prior) on Jul 11, 2005 at 19:52 UTC
    DBI/DBD are well worth learning and this sounds like a good task to learn on... As for other methods, you could manually export in access, or write a vbscript thing to do it or maybe use Win32::OLE to control access to do it.

    here's a snippet that shows how i set up a Class::DBI connection (you can use the same connection string for DBI):
    our $dbopts = { AutoCommit=>0, LongTruncOk => 1, LongReadLen => 255 }; our $dsn = GBPVR::CDBI::mdb2dsn('C:\foo.mdb'); MyPackage::CDBI->set_db('Main', "dbi:ODBC:$dsn", '', '', $dbopts ); sub mdb2dsn { my $mdb = shift; return 'driver=Microsoft Access Driver +(*.mdb);dbq=' . $mdb; } 1;

    DBI example (see the Tutorials as well):
    use DBI; my $dbh = DBI->connect($dsn,$user,$pass,{ShowErrorStatement=>1,RaiseEr +ror => 1, AutoCommit=>0}); my $aref = $dbh->selectall_arrayref("select * from $table",{Slice=>{}} +); # gives ref to AoH
Re: exporting MS ACCESS tables into a CSV file
by Adrade (Pilgrim) on Jul 11, 2005 at 20:13 UTC
    If you can export to Excel, you should be able to read it in with Spreadsheet::ParseExcel (if its saved as the correct version, apparently)

    Update: I wrote a little dumper for you - hopefully this will help a little. I don't have aparently do have access, so I tested it with it and MySQL, but I think it turns out that DBI's methods are the same for both. All this does is dump all the tables of a database in files of the tables' names, delimited by tabs, but you can easily change that to whatever you want (I just like tabs :-)...
    use DBI; my $dbh = DBI->connect("dbi:ODBC:db_test","Admin","password-here", {Ra +iseError => 1, PrintError => 1, AutoCommit => 1} ); my $sel = $dbh->prepare("SELECT [Name] FROM MSysObjects WHERE [Type] = + 1 and [Name] not like 'MSys%'"); $sel->execute; my @tables; while (my ($tab) = $sel->fetchrow_array) { push(@tables, $tab) } $sel->finish; for (@tables) { my $sel = $dbh->prepare("SELECT * FROM $_;"); $sel->execute(); open(DBF,">$_"); print DBF join("\t", @{$sel->{NAME}}), "\n"; while(my (@r) = $sel->fetchrow_array) { for (0..$#r) { $r[$_] =~ s/[\n\r\t]/\?/sg } print DBF join("\t", @r), "\n"; } close(DBF); $sel->finish; }

    Really hope this helps,
      -Adam

    Update2: The following help is pulled from here:
      After you have installed it, start up a DOS prompt, and type commands as shown below (as lexxwern suggested). PPM is a tool that comes with ActiveState Perl you can use to install Perl modules. Press enter after each command (after installing ActiveState Perl you should have path to ppm.bat).

      ppm
      install DBI
      install DBD::ODBC
      exit

      Then enter control panel, doubleclick on ODBC, click on "System DSN" property sheet, then click button "Add". Select "Microsoft Access Driver (*.mdb), and click "Finish". Type "test" (in this case "db_test") as data source name, click button "Select" and select the Access .mdb file you want to use. The .mdb file should be located below the root of the web server.


    Update 3: I could seriously pull my hair out - OK! The code above now works with Microsucks Access - it appears that Access's version of SQL has been totally butchered. Also - make sure you set your permissions correctly within Access so you can access the MSysObjects hidden table... ARGH- MS... err, Access... {rage boils}

    Search keywods: show tables in Microsoft Access using SQL - Microsoft Access user permissions

    --
    By a scallop's forelocks!

Re: exporting MS ACCESS tables into a CSV file
by sparkyichi (Deacon) on Jul 11, 2005 at 20:57 UTC
    It realy depends on what OS you are working on. If it is Win32 you will probably want to look at ODBC connections. I think (it has been a while) you will want Win32::ODBC. You can get DBI to work but it is somewhat a bear. Do a search on the monistary I think I made a post about it a while ago for oracle. You just need to change the driver. If you have further questions please send me a message.
    Sparky
    FMTEYEWTK
Re: exporting MS ACCESS tables into a CSV file
by shiza (Hermit) on Jul 11, 2005 at 19:54 UTC
    I'm on a linux box right now, but I know MS Access has an export option. You can export to any kind of delimited file, including a CSV.
Re: exporting MS ACCESS tables into a CSV file
by ww (Archbishop) on Jul 11, 2005 at 19:55 UTC
    This becomes a non-perl question if you export the access data to excel which you can then export to csv.

    shiza is correct about direct export from access... but it (vers. 8.0.0.4122) does NOT include csv as a direct option. Hence, the above using excel as an intermediary.

      I would know how to export to .csv for any one of the tables. Unfortunately, I anticipate having several hundred files each with perhaps 15-20 tables.

      So if i had a generic file, "foo.mdb" and wanted to export all of its tables (I won't know these in advance). Is there an easy way to do this in perl?

      Thanks

      I have used perl many times to extract from DB's and put them into excel. Perl is very good at doing this.
      Sparky
      FMTEYEWTK
Re: exporting MS ACCESS tables into a CSV file
by dirac (Beadle) on Jul 12, 2005 at 08:03 UTC
    This is a simple example for a table Clients with fields CompanyName, ClientID, Phone in a file TestAccess.mdb.
    See also Text::CSV.
    use strict; use Win32::OLE; use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; # Change these three variables my $database = "TestAccess.mdb"; my $table = "Clients"; my $field1 = "CompanyName"; my $field2 = "ClientID"; my $field3 = "Phone"; my $Conn = Win32::OLE->new("ADODB.Connection"); my $RS = Win32::OLE->new("ADODB.Recordset"); my $DSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$database;UID=;PWD=;"; $Conn->Open($DSN); my $SQL = "SELECT $field1, $field2, $field3 FROM $table"; $RS->Open($SQL, $Conn, 1, 1); until ($RS->EOF) { my $value1 = $RS->Fields($field1)->value; my $value2 = $RS->Fields($field2)->value; my $value3 = $RS->Fields($field3)->value; print $value1,"\t",$value2,"\t",$value3,"\n"; $RS->MoveNext; } $RS->Close; $Conn->Close;
      what if i don't know the table names or the fields in each table in advance? I will however easily know the filenames.
      Hi All, I have tries the above code but when i'm trying to access the tables and columns which contains white spaces it's giving the following error: (Can't call method "value" on an undefined value) Please help