Re: create batch script to import into postgres tables

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com