Thread: import CSV file
I am trying to batch-load a tab-separated CSV file: psql -h lolek -U tes -d stockmarket -c "copy history from '/tmp/FTSE.csv' CSV"; ERROR: could not open file "/tmp/FTSE.csv" for reading: No such file or directory The file exists. Do I need to escape the quotes? set-up: Postgres 8.1, Debian -- Best Regards, Tarlika Elisabeth Schmitz
On Tue, 13 Mar 2007 20:38:33 +0000 T E Schmitz <mailreg@numerixtechnology.de> wrote: > I am trying to batch-load a tab-separated CSV file: > > psql -h lolek -U tes -d stockmarket -c "copy history from > '/tmp/FTSE.csv' CSV"; > > ERROR: could not open file "/tmp/FTSE.csv" for reading: No such file or > directory > > The file exists. Do I need to escape the quotes? From the "-h" option I assume that the server is not on the local machine and hence the file is not available to the server. You need to do something like this: psql -h lolek -U tes -d stockmarket -c "copy history from STDIN CSV" < /tmp/FTSE.csv -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
T E Schmitz wrote: > I am trying to batch-load a tab-separated CSV file: > > psql -h lolek -U tes -d stockmarket -c "copy history from > '/tmp/FTSE.csv' CSV"; > > ERROR: could not open file "/tmp/FTSE.csv" for reading: No such file or > directory > > The file exists. Do I need to escape the quotes? Is the server on the same machine that's running psql? If not, then this fails because it tries to open the file server-side. The suggested workaround is to use psql's \copy. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Tue, 2007-03-13 at 20:38 +0000, T E Schmitz wrote: > I am trying to batch-load a tab-separated CSV file: > > psql -h lolek -U tes -d stockmarket -c "copy history from > '/tmp/FTSE.csv' CSV"; > > ERROR: could not open file "/tmp/FTSE.csv" for reading: No such file or > directory > > The file exists. Do I need to escape the quotes? > Try setting the permissions chmod a+r /tmp/FTSE.csv Best regards, Jure Kodzoman
D'Arcy J.M. Cain wrote: > On Tue, 13 Mar 2007 20:38:33 +0000 > T E Schmitz <mailreg@numerixtechnology.de> wrote: > >>I am trying to batch-load a tab-separated CSV file: >> >>psql -h lolek -U tes -d stockmarket -c "copy history from >>'/tmp/FTSE.csv' CSV"; >> >>ERROR: could not open file "/tmp/FTSE.csv" for reading: No such file or >>directory >> >>The file exists. Do I need to escape the quotes? > > > From the "-h" option I assume that the server is not on the local > machine and hence the file is not available to the server. You need to > do something like this: > > psql -h lolek -U tes -d stockmarket -c "copy history from STDIN CSV" < /tmp/FTSE.csv > This is pretty ingenious! Just a few minor problems: - how does COPY know which column is which? - how do I specify DELIMITER as TAB? -- Regards/Gruß, Tarlika Elisabeth Schmitz
Jure Kodzoman wrote: > On Tue, 2007-03-13 at 20:38 +0000, T E Schmitz wrote: > >>I am trying to batch-load a tab-separated CSV file: >> >>psql -h lolek -U tes -d stockmarket -c "copy history from >>'/tmp/FTSE.csv' CSV"; >> >>ERROR: could not open file "/tmp/FTSE.csv" for reading: No such file or >>directory >> >>The file exists. Do I need to escape the quotes? >> > > > Try setting the permissions chmod a+r /tmp/FTSE.csv > The problem was indeed that COPY was expecting the file on the server. -- Regards, Tarlika Elisabeth Schmitz
D'Arcy J.M. Cain wrote: > On Tue, 13 Mar 2007 20:38:33 +0000 > T E Schmitz <mailreg@numerixtechnology.de> wrote: > > > psql -h lolek -U tes -d stockmarket -c "copy history from STDIN CSV" < /tmp/FTSE.csv > Also, it's nopt happy about the date format : 2007/02/09 -- Regards, Tarlika Elisabeth Schmitz
On 3/13/07, T E Schmitz <mailreg@numerixtechnology.de> wrote: > This is pretty ingenious! > Just a few minor problems: > - how does COPY know which column is which? > - how do I specify DELIMITER as TAB? See: http://www.postgresql.org/docs/8.2/static/sql-copy.html > Also, it's nopt happy about the date format : 2007/02/09 See: http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE
Rodrigo De León wrote: > On 3/13/07, T E Schmitz <mailreg@numerixtechnology.de> wrote: > >> This is pretty ingenious! >> Just a few minor problems: >> - how does COPY know which column is which? >> - how do I specify DELIMITER as TAB? > > > See: > http://www.postgresql.org/docs/8.2/static/sql-copy.html Thank you for the links. I had read of the manual entry for the COPY command and I know that I need something like psql -h lolek -U tes -d stockmarket -c "copy history from STDIN DELIMITER AS TAB CSV" < /tmp/FTSE.csv My problem is how to specify the tab character on the command line seeing as the COPY command is already enclosed in quotes. When creating CSV output I use -F $'\t' on Unix. However, DELIMITER AS $'\t' doesn't work. >> Also, it's nopt happy about the date format : 2007/02/09 > > See: > http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE -- Regards, Tarlika Elisabeth Schmitz
T E Schmitz wrote: > Rodrigo De León wrote: > >> On 3/13/07, T E Schmitz <mailreg@numerixtechnology.de> wrote: >> >>> This is pretty ingenious! >>> Just a few minor problems: >>> - how does COPY know which column is which? >>> - how do I specify DELIMITER as TAB? >> >> >> >> See: >> http://www.postgresql.org/docs/8.2/static/sql-copy.html > > > Thank you for the links. I had read of the manual entry for the COPY > command and I know that I need something like > > psql -h lolek -U tes -d stockmarket -c "copy history from STDIN > DELIMITER AS TAB CSV" < /tmp/FTSE.csv > > My problem is how to specify the tab character on the command line "copy history from STDIN DELIMITER AS '\t' CSV" is the answer >>> Also, it's nopt happy about the date format : 2007/02/09 The correct delimiter solved my date problem, too. -- Regards, Tarlika Elisabeth Schmitz
On Tue, Mar 13, 2007 at 11:52:17PM +0000, T E Schmitz wrote: > Also, it's nopt happy about the date format : 2007/02/09 You may need to fiddle with your date style. It works for me on 8.1: SELECT '2007/02/09'::date; date ------------2007-02-09 (1 row) A -- Andrew Sullivan | ajs@crankycanuck.ca When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes