Re: create batch script to import into postgres tables - Mailing list pgsql-general

On 6/16/20 7:59 AM, Pepe TD Vo wrote:

Just noticed you cross posted to pgsql-admin listed. FYI, That is not a 
good practice.

> I can run \copy in Linux with individual csv file into the table fine 
> and run import using pgadmin into AWS instance. I am trying to run \copy 
> all csv files import into its own table in Linux and in AWS instance. If 
> all csv files into one table is fine but each csv for each table. Should 
> I create one batch job for each imported table?  If each batch file 
> import csv to its table would be fine via \copy table_name(col1, col2, 
> ... coln) from '/path/tablename.csv' delimiter ',' csv header; right?

Yes, you will need to copy each file into its own table.

> 
> Also, the problem is I can't pull/execute psql from window client to 
> pull the psql in aws instance and don't know how to create the batch 
> script for this run.  I tried simple \copy pull from c:\tes.csv and psql 
> is unknown.

There is no good/easy way I know of to install just psql on Windows. You 
are better off copying the CSV files to the AWS instance.

Do you have PuTTY installed?:

https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html

If not I would take a look at above link. PuTTY brings SSH to Windows. 
The relevant part to this issue is:

https://the.earth.li/~sgtatham/putty/0.73/htmldoc/Chapter5.html#pscp

"PSCP, the PuTTY Secure Copy client, is a tool for transferring files 
securely between computers using an SSH connection. "

> 
> 
> **
> *Bach-Nga
> 
> *No one in this world is pure and perfect.  If you avoid people for 
> their mistakes you will be alone. So judge less, love, and forgive 
> more.EmojiEmojiEmoji
> To call him a dog hardly seems to do him justice though in as much as he 
> had four legs, a tail, and barked, I admit he was, to all outward 
> appearances. But to those who knew him well, he was a perfect gentleman 
> (Hermione Gingold)
> 
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
> 
> 
> 
> 
> On Tuesday, June 16, 2020, 10:39:45 AM EDT, Adrian Klaver 
> <adrian.klaver@aklaver.com> wrote:
> 
> 
> On 6/16/20 7:30 AM, Pepe TD Vo wrote:
> Please post to list also.
> Ccing list.
> 
>  > using psql in AWS instance
>  > also psql in Linux.  I can run psql in linux if create a batch file, but
>  > don't know how to pull psql in aws instance if the batch script run in
>  > Window client.  Also, I need help to pull each csv import to its own
>  > table.  Should I create each batch script for each import table?
> 
> So the AWS instance and Linux instance are different?
> 
> To me the simplest solution would be to push the CSV files to the AWS
> instance and work from there. The files will need to be run through a
> Postgres command to be imported into a table.
> 
> Are you familiar with
> COPY(https://www.postgresql.org/docs/12/sql-copy.html) or
> \copy(https://www.postgresql.org/docs/12/app-psql.html)?
> 
> 
>  >
>  > **
>  > *Bach-Nga
>  >
>  > *No one in this world is pure and perfect.  If you avoid people for
>  > their mistakes you will be alone. So judge less, love, and forgive
>  > more.EmojiEmojiEmoji
>  > To call him a dog hardly seems to do him justice though in as much as he
>  > had four legs, a tail, and barked, I admit he was, to all outward
>  > appearances. But to those who knew him well, he was a perfect gentleman
>  > (Hermione Gingold)
>  >
>  > **Live simply **Love generously **Care deeply **Speak kindly.
>  > *** Genuinely rich *** Faithful talent *** Sharing success
>  >
>  >
>  >
>  >
>  > On Tuesday, June 16, 2020, 10:25:03 AM EDT, Adrian Klaver
>  > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>  >
>  >
>  > On 6/16/20 7:20 AM, Pepe TD Vo wrote:
>  >  > good morning experts,
>  >  >
>  >  > I nêd to set up a batch script to import multi csv files to import 
> them
>  >  > to Postgres tables.  Each csv files will be named 
> table1_todaydate.csv,
>  >  > table2_todaydate.csv, etc... tablen_todaydate.csv.  Each csv file will
>  >  > import to its table and how do I execute the script to called psql 
> from
>  >  > AWS?  Do I need to create each batch file for import each table?
>  >
>  > You have psql installed on your local(Windows?) machine?
>  >
>  > Or are you using psql in your AWS instance?
>  >
>  >  >
>  >  > all export file is store in c:\export\files\
>  >  >
>  >  > thank you.
>  >  >
>  >  > Bach-Nga
>  >  >
>  >  >
>  >  >
>  >  >
>  >  >
>  >  >
>  >  > **
>  >  > *Bach-Nga
>  >  >
>  >  > *No one in this world is pure and perfect.  If you avoid people for
>  >  > their mistakes you will be alone. So judge less, love, and forgive
>  >  > more.EmojiEmojiEmoji
>  >
>  >  > To call him a dog hardly seems to do him justice though in as much 
> as he
>  >  > had four legs, a tail, and barked, I admit he was, to all outward
>  >  > appearances. But to those who knew him well, he was a perfect 
> gentleman
>  >  > (Hermione Gingold)
>  >  >
>  >  > **Live simply **Love generously **Care deeply **Speak kindly.
>  >  > *** Genuinely rich *** Faithful talent *** Sharing success
>  >
>  >  >
>  >  >
>  >
>  >
>  > --
>  > Adrian Klaver
>  > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> 
> <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
> 
>  >
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>


-- 
Adrian Klaver
adrian.klaver@aklaver.com