Exporting data for import into Postgres
"Paul Schulz" <[email protected]>
| Newsgroups | gmane.comp.db.mdb-tools.devel |
|---|---|
| Message-ID | <[email protected]> |
Greetings,
Please find attached a patch which provides a '-p' option to
mdb-export, which when used with the -I and -S, allows data to be
directly imported 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 patch add this.
There is another issue with '\'s in the data still being passed
directly without being escaped. This is a problem where I have
'windows' pathnames stored, and a training '\' will inadvertently
escape the single quote delimiter.
Cheers,
Paul Schulz
(Please cc. me on any reply to the list as I am not subscribed.)
patch-mdbtools-0.5.99.0.6pre1.0.20051109-ps1.diff
(text/x-patch, 1.5 KB)
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++) {