Обсуждение: Re: Copying data from a CSV file into a table dynamically

Hi all,

I am well versed with the COPY command for copying the contents of a csv
file into a table. I am used to the below mentioned command:

* COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-2018-08-14_000000.csv'
WITH csv;*

But in the location "/tmp/abc/xyz/" daily a file(with header
postgresql-2018-08-14_000000.csv) will be generated. 

So how can I automate this task instead of manually specifying the file
name, it has to look for the filename with current_date at the time of
copying into table.  How can I achieve this?

Any suggestions or modifications are most welcome.

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 08/14/2018 06:10 AM, pavan95 wrote:
> Hi all,
> 
> I am well versed with the COPY command for copying the contents of a csv
> file into a table. I am used to the below mentioned command:
> 
> * COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-2018-08-14_000000.csv'
> WITH csv;*
> 
> But in the location "/tmp/abc/xyz/" daily a file(with header
> postgresql-2018-08-14_000000.csv) will be generated.
> 
> So how can I automate this task instead of manually specifying the file
> name, it has to look for the filename with current_date at the time of
> copying into table.  How can I achieve this?

COPY FROM can do so from a program:

https://www.postgresql.org/docs/10/static/sql-copy.html

"PROGRAM

     A command to execute. In COPY FROM, the input is read from standard 
output of the command, and in COPY TO, the output is written to the 
standard input of the command.

     Note that the command is invoked by the shell, so if you need to 
pass any arguments to shell command that come from an untrusted source, 
you must be careful to strip or escape any special characters that might 
have a special meaning for the shell. For security reasons, it is best 
to use a fixed command string, or at least avoid passing any user input 
in it.
"

So maybe create a program that does the file look up and then sends the 
data to stdout for consumption by COPY.

OR

Just create a program/script that does it all, find the file and invoke 
COPY on the file. I do similar things using Python and psycopg2.

> 
> Any suggestions or modifications are most welcome.
> 
> Regards,
> Pavan
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

I tried to use 
*"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days ago"
+%Y-%m-%d`_*.csv' WITH csv;"*

But it resulted in an error. How to issue such that it is understandable by
psql?

And I am completely unaware of python & psycopg2. Anything which suits my
requirement is enough!!

Thanks in Advance

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 08/14/2018 08:38 AM, pavan95 wrote:
> Hi Adrian,
>
> I tried to use
> *"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days ago"
> +%Y-%m-%d`_*.csv' WITH csv;"*
>
> But it resulted in an error. How to issue such that it is understandable by
> psql?
>
> And I am completely unaware of python & psycopg2. Anything which suits my
> requirement is enough!!ostgresql-archive.org/PostgreSQL-general-f1843780.html

Why not:
cat /tmp/abc/xyz/postgresql-`date --date="0 days ago"+%Y-%m-%d`_*.csv' | \
        psql YOURDB -c "COPY postgres_log1 FROM STDIN WITH csv;"

-- 
Angular momentum makes the world go 'round.

On 08/14/2018 06:38 AM, pavan95 wrote:
> Hi Adrian,
> 
> I tried to use
> *"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days ago"
> +%Y-%m-%d`_*.csv' WITH csv;"*
> 
> But it resulted in an error. How to issue such that it is understandable by
> psql?
> 
> And I am completely unaware of python & psycopg2. Anything which suits my
> requirement is enough!!

Well I was just using that as an example. If you are not familiar with 
Python then you would probably be better off using a language and it's 
associated Postgres adapter you are comfortable with.

> 
> Thanks in Advance
> 
> Regards,
> Pavan
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

CSV file has no limitation of # of rows, excel max row is 2^20. Am I correct? 

Thank you so much Ron.  You saved my efforts after a slight modification of
quotes of the command you said, it started working fine.

The modified command is as below:

cat /tmp/xyz/abc/postgresql-`date --date="0 days ago" +%Y-%m-%d`_*.csv | 
psql -U aaa -d mydb -c "COPY postgres_log1 FROM STDIN WITH csv;"

Team,

Thank you so much for your valuable time spent on this!!

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html