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

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

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

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