Re: MS ACCESS -> PGSQL

> importing schema will be more painful than data, i'd guess. > > data is easy: > * save each table as .csv format from access > * import them into excel and save as tab delimited > * make sure the file has unix linebreaks > * delete the first row (containing field names) > * possibly empty the existing table in pg - DELETE FROM tablename; > * import into pg - COPY tablename FROM '/tmp/foo.tab'; > > this is harder i guess if you don't have excel... the versions of access > we've dealt with here didnt seem to have a 'save as tab delimited' > feature. if newer versions do i guess you've got it even easier. > You can save yourself this extra-conversion. PGSQL can directly import csv values, or "whatdammityouprefer-separated-value", you just have to specify the separator you'd wish to use with the following sintax, for example, for the comma: pgsql> COPY tablename FROM filename USING SEPARATOR ','; However, I can assure you that thing will NOT going smooth if the Access database is complex. This is because the data dump made by Access use notations that COPY command don't like at all; just for naming some, the way it treats NULL data and the way it writes datetime fields. I had to do a similar task and I ended in writing a personalized dump procedure, using visual basic, that was able to build, for each table to be extracted, a correct CREATE TABLE statement and then dumped all the data in a format PGSQL's COPY would like. This way the process of extraction was painfully slow, but at least the import of the data was straighforward. Just to make a comparison: VB exporting about 6 meg of ASCII dump, on a dedicated Win NT workstation with a PII-350, 30-40 minutes; PG importing, indexing and vacuuming the same data, on a old pentium I -200 with 64 Mb (that was also acting as a webserver in the meantime, just to avoid getting bored), 3 min. :-) You could also consider using ODBC to export tables to postgres directly from MS Access. You will still encounter some pain, but most of the work will be handled by the ODBC driver. HTH, bye /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 faermini@tin.it loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) faermini@sms.tin.it