convert from access to a comma delimited file?

Question

I am looking for a free way of changing a .mdb(access) file to a .csv(comma delimited file). any ideas out there?

EDIT:

I need to be able to convert a large amount of .mdb files... So something automated would be nice.

Answer 1

You can use mdbtools to export the Access file to CSV. This blogpost covers how you can automate them using Python

Answer 2

MS Access database files are containers of complete databases including schema, indexes and table data. A .csv file should be a dump of one single database table to be of any sane use. You usually shouldn't want sort of a filetype-to-filetype converter but rather a way to extract the information from the Access database that you want in a .csv file.

If you have MS Access available you can just open the database in Access and right-click each table and choose "Export". In the file dialog you can choose the format to export to, which will include plain-text formats such as .csv. In case your installation or version of Access doesn't directly support exporting to plain-text formats, you can follow rjmccann101's advice and export to an MS Excel workbook first which will work even with the oldest versions of Access. Alternatively, you may specify queries on your tables and export their results the same way.

Apart from that, Access data files are self-contained databases. If you don't have a copy of MS Access at hand but only the .mdb file, you can connect to it and query the table data using SQL, for example using a simple Perl script:

#/usr/bin/perl

use strict;
use warnings;

my $dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb); dbq=path_and_name_of_your.mdb');
my $sql = "select * from table_name";
my $sth = $dbh->prepare( $sth );
while( my @row = $sth->fetchrow_array()) {
    print( join( ',', @row ) . "\n" );
}
$sth->finish();
$dbh->disconnect();

Answer 3

Check out this StackOverflow thread:

1. Make a query on the table with only the columns you want, and save it.
2. Select the query, and then got to File->Export (Select Save As "text *.txt, *cvs, etc.") and follow the prompts

Answer 4

I've only got Access 2007 available here but Export to Excel and save as CSV should work.

Answer 5

For mass-processing I'd recommend a script like this (example in Perl):

#/usr/bin/perl  

use strict;  
use warnings;  
use DBI;  

my $dir = "/your/path/to/mdb/files";  
opendir( DIR, "$dir" );  
while( readdir( DIR )) {  
    my $filename = $_;  
    next unless( $filename =~ /.mdb$/ ); # skip non-Access entries  
    my $dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb); dbq=$dir/$filename');  
    # This select will retrieve all Access objects that are Tables and not system tables
    my $sql_outer = 'select name from MSysObjects where type = 1 and name not like "MSys*"';  
    my $sth_outer = $dbh->prepare( $sql_outer );  
    while( my @table_name = fetchrow_array( $sth_outer )) {  
        open( OUT, ">", "$dir/$filename.$table_name.csv" );  
        $sql_inner = "select * from $table_name";  
        $sth_inner = $dbh->prepare( $sql_inner );  
        while( my @row = fetchrow_array( $sth_inner )) {  
            print( OUT join( ',', @row ) . "\n" );  
        }  
        sth_inner->finish();  
        close( OUT );  
    }  
    $sth->outer->finish();  
    $dbh->disconnect();  
}  
closedir( DIR )  

This will "convert" each .mdb file into a group of .csv files being named after their database container (the .mdb file) and the table name. If you don't need actual comma separated values but rather pipe, semicolon or whatever separated data, just edit the print statement accordingly.

The example requires all your .mdb files to reside in the same directory which is hard coded in the $dir variable, but this can easily be changed so that you can hand over directory names or individual .mdb file names on the command line.

Note that the example is not robust or failsafe in any way, I left out any error detection and correction for the sake of readability.
Note further that there are Perl implementations on Windows, too (ActiveState Perl for example) and that the example should work in a Windows environment with only slight adaptions to the path notation.

Answer 6

You might want to check out a tool I wrote called PlaneDisaster.NET. It won't export the files en mass, but its open source, and if you know C# it would be a good starting point for a program that does such.