Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Adrian Klaver]

On 02/17/2017 05:55 AM, Murtuza Zabuawala wrote:

Thank you All for your suggestions, But I was looking for solution
around COPY command only.


Sort of a cheat:

https://www.postgresql.org/docs/9.6/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.





--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com 
The Enterprise PostgreSQL Company

On Fri, Feb 17, 2017 at 3:06 PM, Alexander Shchapov
> wrote:

You might want to look into pgloader: http://pgloader.io/

On Fri, Feb 17, 2017 at 7:26 AM, Murtuza Zabuawala
> wrote:
> Hi,
>
> Is there any way to load multiple CSV files at once using single COPY
> command?
>
> I have scenario where I have to load multiple files,
>
> COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV
HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV
HEADER Y
> ..
> ..
> ..
> ..
> COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV
HEADER Y
>
> 50 files -> 50 COPY command, In my use case I think this is not a
good way
> to load data, Can you suggest any better way to do this?
>
> I can always write external script (eg: shell script) but is there
any other
> way to do this using single COPY command?
>
> --
> Regards,
> Murtuza Zabuawala
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



--
Alexander Shchapov





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Murtuza Zabuawala]

Thank you All for your suggestions, But I was looking for solution around
COPY command only.

--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Feb 17, 2017 at 3:06 PM, Alexander Shchapov 
wrote:

> You might want to look into pgloader: http://pgloader.io/
>
> On Fri, Feb 17, 2017 at 7:26 AM, Murtuza Zabuawala
>  wrote:
> > Hi,
> >
> > Is there any way to load multiple CSV files at once using single COPY
> > command?
> >
> > I have scenario where I have to load multiple files,
> >
> > COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV
> HEADER Y
> > ..
> > ..
> > ..
> > ..
> > COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV
> HEADER Y
> >
> > 50 files -> 50 COPY command, In my use case I think this is not a good
> way
> > to load data, Can you suggest any better way to do this?
> >
> > I can always write external script (eg: shell script) but is there any
> other
> > way to do this using single COPY command?
> >
> > --
> > Regards,
> > Murtuza Zabuawala
> > EnterpriseDB: http://www.enterprisedb.com
> > The Enterprise PostgreSQL Company
>
>
>
> --
> Alexander Shchapov
>

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Alexander Shchapov]

You might want to look into pgloader: http://pgloader.io/

On Fri, Feb 17, 2017 at 7:26 AM, Murtuza Zabuawala
 wrote:
> Hi,
>
> Is there any way to load multiple CSV files at once using single COPY
> command?
>
> I have scenario where I have to load multiple files,
>
> COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV HEADER Y
> ..
> ..
> ..
> ..
> COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV HEADER Y
>
> 50 files -> 50 COPY command, In my use case I think this is not a good way
> to load data, Can you suggest any better way to do this?
>
> I can always write external script (eg: shell script) but is there any other
> way to do this using single COPY command?
>
> --
> Regards,
> Murtuza Zabuawala
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



-- 
Alexander Shchapov


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Magnus Hagander]

On Feb 17, 2017 06:53, "John R Pierce"  wrote:

On 2/16/2017 9:43 PM, Magnus Hagander wrote:


>
Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would work for
you?


he's using HEADER, so cat wouldn't work.he's also using MSDOS/WIndows
style filenames, so cat won't work, anyways..


There are windows equivalents of cat.. But you're right, I didn't think of
the header part.

I'd suggest using something like pgloader, but I don't know if that runs on
MS Windows, either.   its very powerful for doing bulk imports from a wide
range of formats, and even allows data manipulation.


I assume it does if you can figure out how to build it. But I don't think
there are any packages provided for it, so it can be a bit of a challenge.


/Magnus

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[John R Pierce]

On 2/16/2017 9:43 PM, Magnus Hagander wrote:




Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would 
work for you?


he's using HEADER, so cat wouldn't work.he's also using 
MSDOS/WIndows style filenames, so cat won't work, anyways..


I'd suggest using something like pgloader, but I don't know if that runs 
on MS Windows, either.   its very powerful for doing bulk imports from a 
wide range of formats, and even allows data manipulation.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Load multiple CSV file in Postgres using COPY

[Magnus Hagander]

On Fri, Feb 17, 2017 at 6:26 AM, Murtuza Zabuawala <
murtuza.zabuaw...@enterprisedb.com> wrote:

> Hi,
>
> Is there any way to load multiple CSV files at once using single COPY
> command?
>
> I have scenario where I have to load multiple files,
>
> COPY prdxgdat FROM 'Z:/data-2016-04-01.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-02.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-03.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-04.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-05.csv' WITH DELIMITER ',' CSV HEADER Y
> COPY prdxgdat FROM 'Z:/data-2016-04-06.csv' WITH DELIMITER ',' CSV HEADER Y
> ..
> ..
> ..
> ..
> COPY prdxgdat FROM 'Z:/data-2016-04-50.csv' WITH DELIMITER ',' CSV HEADER Y
>
> 50 files -> 50 COPY command, In my use case I think this is not a good way
> to load data, Can you suggest any better way to do this?
>
> I can always write external script (eg: shell script) but is there any
> other way to do this using single COPY command?
>
>
Perhaps something like "COPY FROM PROGRAM 'cat /path/*.csv'" would work for
you?


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/