MDB to CSV conversion

[DeltaRocked]

mdbcon.au3 This is to assist migration of of MDB database to MSSQL / SQL.

Many methods exist but this is the fastest except for the Disk IO.

Once the csv files are created, use Bulk Insert T-SQL to transfer the data from csv to the actual DB.

This was created cause I was frustrated with the available solutions. This is the simplest approach to assist you in migrating DB from MDB to SQL.

usage:

mdbcon.exe path_to.mdb password

In case password for the specific MDB doesn’t exist then

mdbcon.exe path_to.mdb

After completion csv files will be created in the path containing the mdb files.

What this utility does :

This will enumerate all the tables and create individual csv files in this format, with the tablename being the identifier.

Filename.mdb.table_name.csv

Errors:

In case of any errors, proper messages will be directed to the STD_Console.

Eg.

1: File does not exist, when the provided filename does not exist.

2: MDB requires a password, when password is not provided or password is incorrect and the mdb is password protected.

3: Incorrect parameters are passed, then help will be shown.

The said CSV files are to be used by the “bulk insert” t-sql.

Flaws:

There exists a flaw in this code, when the number of records increases the speed slows down and this is due to the inhenrent flaw of MS Windows DISK IO.

How to over come the flaw:

Write data into the file after 1000 (or as per the limit of the characters stored by a variable) records . This will ensure lesser disk IOs and it will be very very fast.

I was bored so didnt write the code for this, but its simple and not at all complicated.

regards

Deltarocked.

[ptrex]

@deltarocked

?? What about this ?? http://www.microsoft.com/sqlserver/2005/en/us/migration.aspx

Rgds,

ptrex

[DeltaRocked]

  On 10/12/2010 at 9:53 AM, 'ptrex said:

@deltarocked

?? What about this ?? http://www.microsoft.com/sqlserver/2005/en/us/migration.aspx

Rgds,

ptrex

yup gone through SSMA but doesnt suit my needs.

We have over a million deployments of MSAccess mdb and we are migrating the main application's database from MS Access to MSSQL, so cant expect my end-user (A System Admin - which again is a huge number) to download SSMA and do a click through for all the deployments. Plus data retention is important.

So that answers my *frustration* and the need for a command line tool. Though this tools doesnt do the complete migration but atleast it breaks the ice.

What can be done to this code :

add additional routine to identify data types and stored procedures and create a replica of the same on MSSQL etc etc etc.

For me this is not an important part, as the initial script which was used to create the mdb is already present and the same is being used for MSSQL.

Regards

Deltarocked

Edited October 12, 2010 by deltarocked