Data Migration from MS-Access to PostgreSQL - Problems and Solution

I faced tons of problems while migrating my database from Access-97 to PostgreSQL. After trying with few data migration tools, I found that it's good to use PostgreSQL and Access ODBC driver for data migration.

To check with how to setup and use this ODBC driver please go through,
Setup Access ODBC Driver
Install & Setup PostgreSQL ODBC Driver
Migrate Data from Access 97 to PostgreSQL

Using the steps mentioned in Migrate Data from Access 97 to PostgreSQL post, I was able to migrate almost all the tables, still with few tables PostgreSQL ODBC driver fails to transfer the data. Here are some of those errors, which makes PostgreSQL ODBC driver fail,

• "ERROR : character 0x9d of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR : character 0xb7 of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR : character 0xbc of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR : character 0x92 of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR : character 0x96 of encoding "WIN1252" has no equivalent in "UTF8"; Error while executing the query(#7)"
• "ERROR: syntax error at or near :,";
  Error while execution the query (#7)
• The Microsoft Jet engine stopped the process because you and another user are attempting to change the same data at the same time.

So to get rid of all this error, I developed a PHP script, which will read data from Access table using Access-ODBC driver, and insert it into PostgreSQL server using connection string.This script will solve all character encoding related issue. But for the error like "The Microsoft Jet engine stopped the process because you and another user are attempting to change the same data at the same time." which means the database is corrupted or having some bad sector, it will not allow to copy those records, but for that you can use some debug variable in this script to find out id of those record, then open your access database table, and go to that particular record, move your cursor to each column of that record, and you will come to know which field is creating problem. Once you know the corrupted data, you can move all the data except those problem data
____________________________________________________
 <?php
set_time_limit (0);
$conn=odbc_connect("AccessCon", "" , "");
$pgConn = @pg_connect("host=localhost user=ekta password=ekta dbname=Test");
$rslt = pg_query($pgConn, 'SET CLIENT_ENCODING TO \'WIN1252\';');
if($conn){
  $sql="SELECT * FROM myAccessTable";
  $row=odbc_exec($conn, $sql);
  while(odbc_fetch_row($row)) {
     $field1 = odbc_result($row,1);
     $field2 = odbc_result($row,2);
     $field3 = odbc_result($row,3);
     $strQry = "INSERT INTO myPostgresTable (field1, field2, field3)
                     VALUES('" . addslashes($field1) . "',
                                     '" . addslashes($field2) . "',
                                     '" . addslashes($field3) . "')";
     $rslt1 = pg_query($pgConn, $strQry);
  }
}
$rslt2 = pg_query($pgConn, 'RESET CLIENT_ENCODING;');
odbc_close_all();
pg_close($pgConn);
?>
____________________________________________________

How to insert NULL value in PostgreSQL date column?

The simple query is like,
INSERT INTO table (date_field) VALUES (NULL);

But when we try to run this insert query through PHP application, where we don't know whether the variable is NULL or contain some value, it starts giving error. Here we need to do some programming and query manipulation as shown below,

<?
if(empty($dbDate)) $dbDate = '0000-00-00';
$myQry = "INSERT INTO table (date_field) VALUES (CASE $dbDate WHEN 0 THEN NULL ELSE TO_DATE('{$dbDate}', 'YYYY-MM-DD') END)";

/*Remaining code comes here */
?>

CONCAT_WS for PostgreSQL

PostgreSQL do not have function like CONCAT_WS of MySQL, but writing the query as given below we can do much more...


SELECT ARRAY_TO_STRING(ARRAY[initial, firstname, lastname] , ' ') AS user
FROM users

Same as CONCAT_WS function, the above query will ignore fields which are having NULL values, and return the concatenation of remaining fields. But if your fields contains blank value, modify the above query as given below, to get the proper result

SELECT ARRAY_TO_STRING(ARRAY[
CASE title WHEN '' THEN NULL ELSE title END,
CASE firstname WHEN '' THEN NULL ELSE firstname END,
CASE lastname WHEN '' THEN NULL ELSE lastname END], ' ') AS user
FROM users

Migrate Data from Access 97 to PostgreSQL

To migrate data from Access to PostgreSQL, so many tools are available online. I did try to migrate data with few tools, but no success. I encounter issues with the tables which has boolean fields and with the tables in which access date() and time() function are being used to set default value of a column.

The best way to migrate data from Access to PostgreSQL database, I found is through PostgreSQL ODBC driver. To check how to install and setup PostgreSQL ODBC driver go to Install & Setup PostgreSQL ODBC Driver . Once finished with installation and setup of PostgreSQL ODBC driver, you are ready to migrate the data.

How to migrate data from Access to PostgreSQL using PostgreSQL ODBC?

• Open the access database, select the table, then click on file, Save As/Export, a “Save As” popup will appear, select “To an external file or database” radio button and click ok.
• Then in the new popup for “Save as type” select “ODBC database”.
• An Export window will appear, change the table name if required, or just click ok.
• “Select Data Source” window will appear, select your PostgreSQL DSN, that you have created as mentioned above. And click ok.
• Selected table will get exported to your postgres database.

Install & Setup PostgreSQL ODBC Driver

Install PostgreSQL ODBC Driver

• Download the latest PostgreSQL 32-bit ODBC Driver by choosing a mirror from http://wwwmaster.postgresql.org/download/mirrors-ftpand then navigating to the odbc/versions/msi/ folder. The current version is psqlodbc_08_03_0100.zip which was released Jan-22-2008.
• Unzip psqlodbc_08_03_0100.zip
• Run the psqlodbc.msi file

Setup PostgreSQL ODBC Driver

• Click Start, Control Panel, then switch the control panel to “Classical View”, if it's already not in classical view or select "System and Security" category.
• ODBC located inside Administrative Tools folder. Double click ODBC Data Sources. ODBC Data Source Administrator window displays.
• Select File DSN tab and click Add button, select “PostgreSQL Unicode” from the popup opened.
• Type in a name “pgCon” for "Create New Data Source” and Click Next
• Then Click Finish and fill in relevant server, db, username and password.
• Click the Connection button and uncheck Bools as char as shown in Manager Connection Page 1
• Click the Page 2 button and check True is -1, and uncheck updateable cursors as shown ODBC Manager Connection Pg 2 and then click OK
• A DSN (Data Source) with name “ pgCon ” is created and ready to use...

Important PSql commands

1. Command to connect to postgres

• psql -h localhost -U postgres

2. Few general command that required when connected to Postges though shell

• \h for help with SQL commands
• \? for help on internal slash commands
• \g or terminate with semicolon to execute query
• \q to quit

3. Command to view the database assign to this user

• \l to List databases
• SELECT datname FROM pg_database;

4. Command to connect to a database

• \c database-name  

5. Command to list tables in current database

• \d to List tables in database
• SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'

6. Command to describe a table structure

• \d table-name

Important Linux commands for PostgreSQL

• createdb
  createdb tmpdb -h localhost -U postgres
  Create a new PostgreSQL database 
• createuser
  createuser ekta –s -h localhost -U postgres –P
  Create a new PostgreSQL user. Above command will create a new user ekta with the permission of superuser (-s) . And –P will prompt you for the password for new user. If we write this command without –P, it will create used which will not require password authentication
  
• dropdb
  dropdb tmpdb -h localhost -U postgres
  Remove an existing PostgreSQL database
  
• dropuser
  dropuser ekta -h localhost -U postgres
  Drops (removes) a PostgreSQL user
  
• pg_dump
  pg_dump -h localhost -U postgres tmpdb > tmpdb.sql
  pg_dump --insert -h localhost -U postgres tmpdb > tmpdb.sql
  All above command are used to take database dump. It will extract a PostgreSQL database into a script file. --insert option is used to dump data as insert command.
  
• pg_dumpall
  pg_ dumpall -h localhost -U postgres > tmpdb.sql
  Extract all PostgreSQL databases into a script file
  
• psql
  psql -h localhost -U postgres
  Open PostgreSQL interactive terminal
  
• vacuumdb
  vacuumdb tmpdb -h localhost -U postgres
  Clean and analyze a PostgreSQL database

Note : For information on above command check Linux help using man command, for example man pg_dump

Windows Apache PHP and Postgres (WAPP Server)

BitNami WAPPStack includes Apache 2.2.16, PostgreSQL 9.0.0-1, PHP 5.2.14 and phpPgAdmin 4.2.2. phpPgAdmin is a web interface for administration of PostgreSQL. Steps to install and configure WAPP Server are given below,

Installation

•  Download free copy of WAPP Server from http://bitnami.org/stack/wappstack
•  Locate the downloaded file and double click the file to launch the installation.
• The WAPP Setup Wizard will appear. Click Next.
• At the Select Destination Location you can change the install directory. The default is C:\Program Files\BitNami WAPPStack. We will use the default directory. Click Next.
• Then you will be asked for PostgreSQL postgres user password. Enter the Password. A Postgres user account will be created with user name “postgres” and the password you have entered. This information will be then used to connect with PostgreSQL server, through command prompt or through phpPgAdmin. Click Next
• The default listening port for Apache is 80 and for PostgreSQL is 5432. If those ports are already in use by other applications, you will be prompted for alternate ports to use. Enter the alternate port if prompted. Click next
• You are now ready to begin the installation, screen will appear. Click next
• When installation is complete, “Completing the BitNami WAPP Stack Setup Wizard” page will appear. Click on Finish
• You can now launch your browser with http://localhost/

Folder to create PHP files

• The installation process will create several subfolders under the main installation directory i.e. C:\Program Files\BitNami WAPPStack
• Create your PHP files in C:\Program Files\BitNami WAPPStack\apache2\htdocs

How to access phpPgAdmin

• You can access your phpPgAdmin by opening a browser and typing http://127.0.0.1/phppgadmin. You will be asked for a username and password. As user name, use "administrator" and as password use the value specified during installation
• Once you are connected with phpPgAdmin, to connect with default Postgres database, click on the Postgres on left bar, it will again ask for database user name and password. As the user name, use “postgres” and as password use the value specified during installation