Database Reference
In-Depth Information
We can easily handle spreadsheet data if that spreadsheet's layout follows a very specific
form, as follows:
F Each spreadsheet column becomes one column in one table.
F Each row of the spreadsheet becomes one row in one table.
F Data is only in one worksheet of the spreadsheet.
F Optionally, the first row is a list of column descriptions/titles.
This is a very simple layout and more often there will be other things in the spreadsheet,
such as titles, comments, constants for use in formulas, summary lines, macros, images,
and so on. If you're in this position, the best thing to do is to create a new worksheet within
the spreadsheet in the pristine form described earlier, and then set up cross-worksheet
references to bring in the data. An example of a cross-worksheet reference would be
"=Sheet2.A1". You'll need a separate worksheet for each set of data that will become one
table on PostgreSQL. You can load multiple worksheets into one table though.
Some spreadsheet users will say that this is all unnecessary, and is evidence of the problems
of databases. The real spreadsheet gurus do actually advocate this type of layout: data in one
worksheet, calculation and presentation in other worksheets. So it is actually best practice to
design spreadsheets in this way; however, we must work with the world the way it is.
How to do it...
If your spreadsheet data is neatly laid out in a single worksheet as shown in the following
screenshot, then you can do File | Save As and then select CSV as the file type to be saved.
This will export the current worksheet into a file like the following:
"Key","Value"
1,"c"
2,"d"
We can then load it into an existing PostgreSQL table, using the psql command
postgres=# \COPY sample FROM sample.csv CSV HEADER
postgres=# SELECT * FROM sample;
key | value
-----+-------
1 | c
2 | d
 
Search WWH ::




Custom Search