Load multiple CSV file in Postgres using COPY

On Fri, Feb 17, 2017 at 6:26 AM, Murtuza Zabuawala <
murtuza.zabuawala@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/

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

On Feb 17, 2017 06:53, "John R Pierce" <pierce@hogranch.com> 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

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

On Fri, Feb 17, 2017 at 7:26 AM, Murtuza Zabuawala
<murtuza.zabuawala@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?

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

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 <alexanderad@gmail.com>
wrote:

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 <http://www.enterprisedb.com/&gt;
The Enterprise PostgreSQL Company

On Fri, Feb 17, 2017 at 3:06 PM, Alexander Shchapov
<alexanderad@gmail.com <mailto:alexanderad@gmail.com>> wrote:

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

On Fri, Feb 17, 2017 at 7:26 AM, Murtuza Zabuawala
<murtuza.zabuawala@enterprisedb.com
<mailto:murtuza.zabuawala@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?

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

--
Alexander Shchapov

--
Adrian Klaver
adrian.klaver@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