How to do it...

Here, we will show you an example where data in a spreadsheet is loaded into a database:

  1. If your spreadsheet data is neatly laid out in a single worksheet, as shown in the following screenshot, then you can go to File | Save As and then select CSV as the file type to be saved:
  1. This will export the current worksheet to a file, like the following:
"Key","Value" 
1,"c" 
2,"d"
  1. We can then load it into an existing PostgreSQL table, using the following psql command:
postgres=# \COPY sample FROM sample.csv CSV HEADER
postgres=# SELECT * FROM sample;
 key | value
-----+-------
   1 | c
   2 | d
  1. Alternatively, from the command line, this would be as follows:
psql -c '\COPY sample FROM sample.csv CSV HEADER'
  1. Note that the file can include a full file path if the data is in a different directory. The psql \COPY command transfers data from the client system where you run the command through to the database server, so the file is on the client.
  2. If you are submitting SQL through another type of connection, then you should use the following SQL statement:
COPY sample FROM '/mydatafiledirectory/sample.csv' CSV HEADER;

Note that the preceding SQL statement runs on the database server and can only be executed by a super user. So you need to ensure that the server process is allowed to read that file, then transfer the data yourself to the server, and finally load the file. The COPY statement shown in the preceding SQL statement uses an absolute path to identify data files, which is required.

The COPY (or \COPY) command does not create the table for you; that must be done beforehand. Note also that the HEADER option does nothing but ignore the first line of the input file, so the names of the columns from the .csv file don't need to match those of the Postgres table. If it hasn't occurred to you yet, this is also a problem. If you say HEADER and the file does not have a header line, then all it does is ignore the first data row. Unfortunately, there's no way for PostgreSQL to tell whether the first line of the file is truly a header or not. Be careful!

There isn't a standard tool to load data directly from the spreadsheet to the database. It's fairly simple to write a spreadsheet macro to automate the aforementioned tasks, but that's not a topic for this book.