Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
What version of access are you using? –  JNK Sep 1 '10 at 13:57
    
access 2007. but I am unsure what version the files were created with. –  Richard Sep 1 '10 at 14:29
add comment

6 Answers

up vote 3 down vote accepted

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

share|improve this answer
    
Cool, there's a ready solution to this already. Thanks for the link, I wasn't aware of that yet. –  Olfan Sep 2 '10 at 10:21
    
@Olfan You're welcome! –  Sathya Sep 2 '10 at 13:14
add comment

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();

share|improve this answer
    
ummm, I never used access in the past, but I use sql a fair bit. I normally use Python as my scripting language. I wonder if I can connect to the db in a similar fashion with python –  Richard Sep 1 '10 at 14:36
    
Python is quite powerful, while I don't use it myself I know it's capable of connecting to databases. ODBC is a fairly common standard, too. –  Olfan Sep 1 '10 at 14:42
add comment

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
share|improve this answer
    
hmmm, I need to convert a large number of files... I should have but that in my question. Editing now. –  Richard Sep 1 '10 at 14:26
add comment

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

share|improve this answer
    
Er, if you have A2007, why not just export directly to CSV, which is fully supported from Access? –  David W. Fenton Sep 3 '10 at 22:20
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.