Import data from Excel to PostgreSQL automatically everyday

Question

I have a single Excel file that is updated every morning with the current inventory of a few items. I would like to know if it's possible to connect that Excel file to PostgreSQL and automate a query that will add new rows on the same table on a daily basis. What I would like to get is something like this:

   -------------------------------     
    col1          col2       col 3
   -------------------------------
    09/10/2019    boxes      5
    09/11/2019    boxes      3
    09/12/2019    boxes      2

Matvei Antipov · Accepted Answer · 2019-09-12 06:42:28Z

1

In addition to Dieter's answer. You also need to convert your Excel file to CSV format. You can automate it using xls2csv command-line tool

sudo apt-get update
sudo apt-get install catdoc
xls2csv yourExcelFile.xls > converted.csv

And just add before COPY in crontab:

xls2csv yourExcelFile.xls > converted.csv && psql -c "COPY tbname(col1, col2, col3) FROM 'converted.csv' DELIMITER ';' CSV;"

edited Sep 12, 2019 at 6:42

answered Sep 12, 2019 at 6:31

Matvei Antipov

762 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dieter Kräutl · Accepted Answer · 2019-09-12 06:22:40Z

1

You can setup a cronjob to call a simple command to do this:

psql -c "COPY tbname FROM '/tmp/the_file.csv' delimiter ';' csv;"

as described here:

https://www.postgresql.org/docs/current/app-psql.html

And cronjob some like this:

crontab -e then add 6   2   *   *   *   {command as described}

answered Sep 12, 2019 at 6:22

Dieter Kräutl

6774 silver badges8 bronze badges

Collectives™ on Stack Overflow

Import data from Excel to PostgreSQL automatically everyday

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related