Thread: create batch script to import into postgres tables

create batch script to import into postgres tables

From
Pepe TD Vo
Date:
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?

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


Re: create batch script to import into postgres tables

From
Adrian Klaver
Date:
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



Re: create batch script to import into postgres tables

From
Adrian Klaver
Date:
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



Re: create batch script to import into postgres tables

From
Pepe TD Vo
Date:
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?

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.


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> 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>

>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: create batch script to import into postgres tables

From
Pepe TD Vo
Date:
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?

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.


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> 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>

>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: create batch script to import into postgres tables

From
Adrian Klaver
Date:
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



Re: create batch script to import into postgres tables

From
Adrian Klaver
Date:
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