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....

Answer 2

You can also use psql console to execute \copy without need to send file to Postgresql server machine. The command is the same:

\copy mytable [ ( column_list ) ] FROM '/path/to/csv/file' WITH CSV HEADER

Answer 3

For python you could use openpyxl for all 2010 and newer file formats (xlsx).

Al Sweigart has a full tutorial from automate the boring parts on working with excel spreadsheets its very indepth and the whole book and accompanying Udemy course are great resources.

From his example

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb.get_sheet_by_name('Sheet3')
>>> sheet
<Worksheet "Sheet3">

Understandably once you have this access you can now use psycopg to parse the data to postgres as you normally would do.

This is a link to a list of python resources at python-excel also xlwings provides a large array of features for using python in place of vba in excel.