3
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.
3
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.
4
You can use mdbtools to export the Access file to CSV. This blogpost covers how you can automate them using Python
Cool, there's a ready solution to this already. Thanks for the link, I wasn't aware of that yet. – Olfan – 2010-09-02T10:21:27.253
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()
;
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 – 2010-09-01T14:36:07.717
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 – 2010-09-01T14:42:34.570
1
I've only got Access 2007 available here but Export to Excel and save as CSV should work.
Er, if you have A2007, why not just export directly to CSV, which is fully supported from Access? – David W. Fenton – 2010-09-03T22:20:19.337
1
Check out this StackOverflow thread:
- Make a query on the table with only the columns you want, and save it.
- Select the query, and then got to File->Export (Select Save As "text *.txt, *cvs, etc.") and follow the prompts
hmmm, I need to convert a large number of files... I should have but that in my question. Editing now. – Richard – 2010-09-01T14:26:39.667
1
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.
0
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.
What version of access are you using? – JNK – 2010-09-01T13:57:27.590
access 2007. but I am unsure what version the files were created with. – Richard – 2010-09-01T14:29:58.220