CSV-Import in postgres

To move data from one dbs to another would be done via a sqldump, but if you are moving to another dbms you will need a csv file, because the sql syntax varies from system to system. So if you need to move from a MS Access database to a postgres database you can use the export function for your MS db and put it into your postgres database via the copy command. you can issue it with psql or any other tool you use to manage your postgres instance (e.g. pgadmin).

the command is like that:

COPY FROM ‘filename on the system where the csv-file is’ (coloumn names in the order in which they appear in the csv-file, ..) WITH DELIMITER ‘e.g. ;’;

You can use the csv option as well for the definition of strings in your csv data. If you are getting problems with the encoding of your text file, you can easily convert it on a linux system with iconv.

# iconv -f -t -o

There is no need for a filename, if the text file comes from stdin. To find out what type of encoding your file already has you can use the command file.

# file

It gives you a hint how the text file is encoded.

sources:

http://www.postgresql.org/docs/8.2/static/sql-copy.html
http://www.jumpingbean.co.za/blogs/mark/import_export_csv_files_into_postgresql_database
http://stackoverflow.com/questions/4867272/invalid-byte-sequence-for-encoding-utf8

Advertisements

~ by frankooh on 2011-03-25T17:55:40+00:00.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: