pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Pepe TD Vo <pepevo@yahoo.com>
To: Pgsql-general <pgsql-general@postgresql.org>
To: Pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: create batch script to import into postgres tables
Date: Tue, 16 Jun 2020 08:11:03 -0700
Message-ID: <717a7347-9eba-f850-ddf4-91bf4da174a5@aklaver.com> (raw)
In-Reply-To: <1718146430.1151531.1592319564343@mail.yahoo.com>
References: <866581879.1147164.1592317256828.ref@mail.yahoo.com>
	<866581879.1147164.1592317256828@mail.yahoo.com>
	<1f5f4fd6-ad22-4823-c922-17a021c2c0d7@aklaver.com>
	<74046808.1129536.1592317823808@mail.yahoo.com>
	<3625e291-8456-3831-6205-2d73232bcd1e@aklaver.com>
	<1718146430.1151531.1592319564343@mail.yahoo.com>

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





view thread (32+ messages)  latest in thread

Message-ID: <717a7347-9eba-f850-ddf4-91bf4da174a5@aklaver.com>
Permalink:  ../717a7347-9eba-f850-ddf4-91bf4da174a5@aklaver.com/
Also on:    postgresql.org/message-id/717a7347-9eba-f850-ddf4-91bf4da174a5@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: adrian.klaver@aklaver.com, pepevo@yahoo.com, pgsql-general@postgresql.org
  Subject: Re: create batch script to import into postgres tables
  In-Reply-To: <717a7347-9eba-f850-ddf4-91bf4da174a5@aklaver.com>
  References: <866581879.1147164.1592317256828.ref@mail.yahoo.com> <866581879.1147164.1592317256828@mail.yahoo.com> <1f5f4fd6-ad22-4823-c922-17a021c2c0d7@aklaver.com> <74046808.1129536.1592317823808@mail.yahoo.com> <3625e291-8456-3831-6205-2d73232bcd1e@aklaver.com> <1718146430.1151531.1592319564343@mail.yahoo.com> <717a7347-9eba-f850-ddf4-91bf4da174a5@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox