Re: Copying data from a CSV file into a table dynamically
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
Re: Copying data from a CSV file into a table dynamically
On Tuesday, August 14, 2018, Ruiqiang Chen wrote: > CSV file has no limitation of # of rows, excel max row is 2^20. Am I > correct? > It's considered bad form to hijack threads in this manner. Text files have no inherent limits. You can check the Excel documentation for the version you care about to learn it's limits. David J.
Re: Copying data from a CSV file into a table dynamically
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.kla...@aklaver.com
Re: Copying data from a CSV file into a table dynamically
CSV file has no limitation of # of rows, excel max row is 2^20. Am I correct? On Tue, Aug 14, 2018 at 9:46 AM, Ron wrote: > 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. > >
Re: Copying data from a CSV file into a table dynamically
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.
Re: Copying data from a CSV file into a table dynamically
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
Re: Copying data from a CSV file into a table dynamically
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_00.csv' WITH csv;* But in the location "/tmp/abc/xyz/" daily a file(with header postgresql-2018-08-14_00.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.kla...@aklaver.com
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_00.csv' WITH csv;* But in the location "/tmp/abc/xyz/" daily a file(with header postgresql-2018-08-14_00.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