Export data from Access to Postgres using Data Share

The best method to import Access tables into Postgres is via the ODBC wizard in Access as this transfers the data types correctly. If  ODBC connections in view Access are used then you don't need to read this document, however if a Data share in Studio is used instead then there are some issues as the data types are not correctly transferred across. 

To start to fix the problem we needed to get a report of the fields data types that Access used and load this into Postgres. Once this reference table is available in Postgres we can use it to query the imported table and update the field data types. The following explains how this is done.

1. Produce Access database report using the Database Documentor of table fields exported to excel
   
   
   
2. Manipulate in excel through filter to get columns
   1. TABLE (if multiple tables)
   2. FIELD NAME
   3. DATA TYPE
   4. SIZE
      
      
3. Copy records and fields out into a blank spreadsheet and save as CSV
4. Create blank table in Postgres containing fields contains above

5. Run copy command to import CSV into table
   
   

   copy monitoring.housingfields from 
   E'd:\\Astun\\housingfields.csv' CSV

6. We now need to examine the database tables against these value to see where there is issues.  Likely issues are:
   
   

   • Character varying all are set to a size of 256 the correct lengths are not taken from Access
   • Double precision / numerics are exported as Character
     
     

7. Produce a query on the new imported table to filter just the text field text fields. The query's purpose is generate a further number of queries in the results that can be used to alter the table of interest
   
   

   select
   $$alter table monitoring_ecms.ecms alter column $$ ||fname|| $$ set data type character varying ($$|| size ||$$);$$
   from monitoring.housingfields where ftype='Text'

   
   
   

8. To change Character to Double Precision the query will look like the following:
   
   

   select
   $$update monitoring_ecms.ecms set $$ || fname ||$$= 0 
   where $$||fname||$$= ''; alter table monitoring_ecms.ecms alter column 
   $$|| fname ||$$ set data type double precision using $$|| fname||$$:: numeric;$$ 
   from monitoring.housingfields where ftype like 'Character'

   This also needs to set all null values to 0 so that it can recognised by the numeric field type

9. The results of each query can be saved as text file and open in a new query window.  The results will look like a list of queries, one for each field. These then need to be ran to make the changes to the database table.
   
   The field datatypes in the table should now have been corrected to reflect the intended type from Access.  This does not cover all types and other may also have issues, where that is the case a similar method can be used.