pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedimport CSV file
11+ messages / 6 participants
[nested] [flat]
* import CSV file
@ 2007-03-13 20:38 T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:27 ` Re: import CSV file Alvaro Herrera <alvherre@commandprompt.com>
2007-03-13 22:27 ` Re: import CSV file D'Arcy J.M. Cain <darcy@druid.net>
2007-03-13 22:35 ` Re: import CSV file Jure Kodzoman <jure@plsavez.hr>
0 siblings, 3 replies; 11+ messages in thread
From: T E Schmitz @ 2007-03-13 20:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
@ 2007-03-13 22:27 ` Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 0 replies; 11+ messages in thread
From: Alvaro Herrera @ 2007-03-13 22:27 UTC (permalink / raw)
To: T E Schmitz <mailreg@numerixtechnology.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
@ 2007-03-13 22:27 ` D'Arcy J.M. Cain <darcy@druid.net>
2007-03-13 23:44 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 23:52 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2 siblings, 2 replies; 11+ messages in thread
From: D'Arcy J.M. Cain @ 2007-03-13 22:27 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:27 ` Re: import CSV file D'Arcy J.M. Cain <darcy@druid.net>
@ 2007-03-13 23:44 ` T E Schmitz <mailreg@numerixtechnology.de>
2007-03-14 01:48 ` Re: import CSV file Rodrigo De León <rdeleonp@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: T E Schmitz @ 2007-03-13 23:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:27 ` Re: import CSV file D'Arcy J.M. Cain <darcy@druid.net>
2007-03-13 23:44 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
@ 2007-03-14 01:48 ` Rodrigo De León <rdeleonp@gmail.com>
2007-03-14 14:45 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 11+ messages in thread
From: Rodrigo De León @ 2007-03-14 01:48 UTC (permalink / raw)
To: pgsql-sql; +Cc: mailreg@numerixtechnology.de
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:27 ` Re: import CSV file D'Arcy J.M. Cain <darcy@druid.net>
2007-03-13 23:44 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-14 01:48 ` Re: import CSV file Rodrigo De León <rdeleonp@gmail.com>
@ 2007-03-14 14:45 ` T E Schmitz <mailreg@numerixtechnology.de>
2007-03-14 15:09 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 11+ messages in thread
From: T E Schmitz @ 2007-03-14 14:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:27 ` Re: import CSV file D'Arcy J.M. Cain <darcy@druid.net>
2007-03-13 23:44 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-14 01:48 ` Re: import CSV file Rodrigo De León <rdeleonp@gmail.com>
2007-03-14 14:45 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
@ 2007-03-14 15:09 ` T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 0 replies; 11+ messages in thread
From: T E Schmitz @ 2007-03-14 15:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:27 ` Re: import CSV file D'Arcy J.M. Cain <darcy@druid.net>
@ 2007-03-13 23:52 ` T E Schmitz <mailreg@numerixtechnology.de>
2007-03-14 15:58 ` Re: import CSV file Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 11+ messages in thread
From: T E Schmitz @ 2007-03-13 23:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:27 ` Re: import CSV file D'Arcy J.M. Cain <darcy@druid.net>
2007-03-13 23:52 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
@ 2007-03-14 15:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 11+ messages in thread
From: Andrew Sullivan @ 2007-03-14 15:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
@ 2007-03-13 22:35 ` Jure Kodzoman <jure@plsavez.hr>
2007-03-13 23:48 ` Re: import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2 siblings, 1 reply; 11+ messages in thread
From: Jure Kodzoman @ 2007-03-13 22:35 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: import CSV file
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:35 ` Re: import CSV file Jure Kodzoman <jure@plsavez.hr>
@ 2007-03-13 23:48 ` T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 0 replies; 11+ messages in thread
From: T E Schmitz @ 2007-03-13 23:48 UTC (permalink / raw)
To: Jure Kodzoman <jure@plsavez.hr>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-03-14 15:58 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-13 20:38 import CSV file T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 22:27 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-03-13 22:27 ` D'Arcy J.M. Cain <darcy@druid.net>
2007-03-13 23:44 ` T E Schmitz <mailreg@numerixtechnology.de>
2007-03-14 01:48 ` Rodrigo De León <rdeleonp@gmail.com>
2007-03-14 14:45 ` T E Schmitz <mailreg@numerixtechnology.de>
2007-03-14 15:09 ` T E Schmitz <mailreg@numerixtechnology.de>
2007-03-13 23:52 ` T E Schmitz <mailreg@numerixtechnology.de>
2007-03-14 15:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-03-13 22:35 ` Jure Kodzoman <jure@plsavez.hr>
2007-03-13 23:48 ` T E Schmitz <mailreg@numerixtechnology.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox