mdb-export - SQL not correct for import into postgres.

[PaulSchulz]

Attempting to import a MSAccess database into PostgreSQL.

Using 'mdb-export' with the -I and -S options produces INSERT statements that almost work except for:
- " (double quotes) are used to delimiter data, instead of single quotes.
- The is no semicolon (';') at the end of each line.

The attached patch allows a '-p' option to be used (with -I and -S and) which willfix these issues.

[PaulSchulz (paulschulz)]

(How do I attach a patch file?)

[PaulSchulz (paulschulz)]

diff --git a/src/util/mdb-export.c b/src/util/mdb-export.c
index 8c61e9b..4699ef5 100644
--- a/src/util/mdb-export.c
+++ b/src/util/mdb-export.c
@@ -71,9 +71,10 @@ main(int argc, char **argv)
        char quote_text = 1;
        char insert_statements = 0;
        char sanitize = 0;
+ char postgres = 0;
        int opt;

- while ((opt=getopt(argc, argv, "HQq:X:d:D:R:IS"))!=-1) {
+ while ((opt=getopt(argc, argv, "HQq:X:d:D:R:ISp"))!=-1) {
                switch (opt) {
                case 'H':
                        header_row = 0;
@@ -103,6 +104,10 @@ main(int argc, char **argv)
                case 'X':
                        escape_char = (char *) g_strdup(optarg);
                break;
+ case 'p':
+ postgres = 1;
+ quote_char = (char *) g_strdup("'");
+ break;
                default:
                break;
                }
@@ -129,6 +134,7 @@ main(int argc, char **argv)
                fprintf(stderr," -d <delimiter> specify a column delimiter\n"); fprintf(stderr," -R <delimiter> specify a row delimiter\n");
                fprintf(stderr," -I INSERT statements (instead of CSV)\n");
+ fprintf(stderr," -p Formated for 'postgres' (use with -I and -S)\n");
                fprintf(stderr," -D <format> set the date format (see strftime(3) for details)\n");
                fprintf(stderr," -S Sanitize names (replace spaces
etc. with underscore)\n");
                fprintf(stderr," -Q <char> Use <char> to wrap text-like fields. Default is \".\n");
@@ -212,6 +218,7 @@ main(int argc, char **argv)
                        }
                }
                if (insert_statements) fprintf(stdout,")");
+ if (postgres) fprintf(stdout,";");
                fprintf(stdout, row_delimiter);
        }
        for (j=0;j<table->num_cols;j++) {

[PaulSchulz (paulschulz)]

This patch allows data to be exported from mdb-tools in a format which can be read directly into 'postgres'.