sql - Import Excel Data into PostgreSQL 9.3

Question

I've developed a huge table in excel and now facing problem in transferring it into the postgresql database. I've downloaded the odbc software and I'm able to open table created in postgresql with excel. However, I'm not able to do it in a reverse manner which is creating a table in excel and open it in the postgresql. So I would like to know it is can be done in this way or is there any alternative ways that can create a large table with pgAdmin III cause inserting the data raw by raw is quite tedious.

Thanks in advance for all the helps!

Answer 1

The typical answer is this:

1. In Excel, File/Save As, select CSV, save your current sheet.

2. transfer to a holding directory on the Pg server the postgres user can access

3. in PostgreSQL:

   COPY mytable FROM '/path/to/csv/file' WITH CSV HEADER; -- must be superuser
   

But there are other ways to do this too. PostgreSQL is an amazingly programmable database. These include:

1. Write a module in pl/javaU, pl/perlU, or other untrusted language to access file, parse it, and manage the structure.

2. Use CSV and the fdw_file to access it as a pseudo-table

3. Use DBILink and DBD::Excel

4. Write your own foreign data wrapper for reading Excel files.

The possibilities are literally endless....