Re: Howto import regularly some CSV files with variing names?

To be very straightforward :
your bash script will dynamically create the sql query in a string, then
send it to database using psql.
You can also use pipes.

For example :

$4 -c "COPY $1 FROM '/tmp/pipe_ply_binaire_vers_ply_ascii_"$1"' WITH
CSV DELIMITER AS ' ';";
where $4 is the psql command to connect to db, $1 the number in the
name of the file we are working in, etc

Cheers,
Rémi-C

2013/9/24 Steve Crawford <scrawford(at)pinpointresearch(dot)com>

> On 09/23/2013 05:47 PM, Andreas wrote:
>
>> Am 24.09.2013 02:25, schrieb Adrian Klaver:
>>
>>> On 09/23/2013 05:19 PM, Andreas wrote:
>>>
>>>>
>>>> I need to import some log-files of an application [...]
>>>> The import would be easy if the files had a constant name but the app
>>>> creates csv files with names like "ExportYYYYMMDD".
>>>>
>>>>
>>>> So how would I get the filenames into the sql-script?
>>>>
>>>
>>> Do man on find and look for -exec.
>>>
>>>
>> I could find the files and exec a shell script but how can I have a SQL
>> script take the found filenames as parameter?
>>
>> The SQL script needs to create a temp table
>> then COPY the file with the filename it got as parameter into the temp
>> table
>> then insert from there into the log-table
>>
>> How would I get the filenames into the SQL script?
>>
>>
>>
> Assuming your main script - the one that mounts the directory and finds
> the file name - is in bash you can easily put a small script into a heredoc
> block with variable substitution:
>
> some script stuff that mounts remote directory and sets variable
> logfilename
> ...
> psql -your -connection -parameters <<EOS
> some preliminary setup statements
> \copy .... from $logfilename ...
> some processing statements
> EOS
>
> The disadvantage of this approach is that it is difficult-to-impossible to
> detect and handle statement-level errors. But for short scripts like simple
> imports this may not be an issue or may be easily solved by wrapping things
> in a begin;...commit; block.
>
> Cheers,
> Steve
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>