Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Re: Module for intelligently analyzing and merging spreadsheet data

by erix (Parson)
on Feb 10, 2019 at 21:39 UTC ( #1229745=note: print w/replies, xml ) Need Help??

in reply to Module for intelligently analyzing and merging spreadsheet data

PostgreSQL has (as a contrib module) file_fdw (fdw=Foreign Data Wrapper) which reads csv (readonly; it cannot write to the csv file). It offers the postgres SQL-interface to address the data in the text files.

Steps are:

1) Installing the file_fdw module ('create extension file_fdw') (once)

2) Creating a server [1] ('create server if not exists csvserver foreign data wrapper file_fdw') (once)

3) Construct a CREATE FOREIGN TABLE statement [2] to reflect the structure of the csv file. The csv-file header line can often be used to convert into such an CREATE statement (possibly even adding the data type to columns).

SQL calls to such a 'Foreign Table' now read the underlying csv file(s) (select * from mytable).

Advantages: SQL access to csv data.

Disadvantages: Needs Postgres. Readonly. No indexing possible, so that huge csv-files can make it slow (alhough 'copying' onto a materialized view [3] on the foreign table makes indexing possible). Filesystem access for the postgres superuser is necessary .

[1] Create Server

[2] Create Foreign Table

[3] Create Materialized View

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1229745]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (6)
As of 2020-07-02 17:18 GMT
Find Nodes?
    Voting Booth?

    No recent polls found