Save Output from SQL Function to CSV File (Copy) with Dynamic Filename

COPY with dynamic file name

You need dynamic SQL:

CREATE OR REPLACE FUNCTION loaddata(filepathname text)
RETURNS void AS
$func$
BEGIN
EXECUTE format ('
COPY climatedata(
climatestationid
, date
... -- more columns
, tminsflag)
FROM %L (FORMAT CSV, HEADER)' -- current syntax
-- WITH CSV HEADER' -- tolerated legacy syntax
, $1); -- pass function parameter filepathname to format()
END
$func$ LANGUAGE plpgsql;

format() requires PostgreSQL 9.1+.

Pass the file name without extra set of (escaped) single quotes:

SELECT loaddata('/absolute/path/to/my/file.csv')

format() with %L escapes the file name safely. Would be susceptible to SQL injection without it.


Aside, you have a function name mismatch:

CREATE OR REPLACE FUNCTION public.loaddata(filepathname varchar)
...
ALTER FUNCTION public.filltmaxa(character varying)

export csv with dynamic file name

Try composing the filename like this:

import os
filename = os.path.join('F:', 'Result_Check', 'B933418' + f + '.csv')

Also you do not normally assign result of df.to_csv to a variable.

COPY csv using custom filename path

plpgsql code could work like this:

...
DECLARE
var1 text;
BEGIN
var1 := to_char(current_date - 1, 'YYYY-MM-DD');
EXECUTE $$COPY (SELECT * from myTable)
TO E'C:\\Exports\\export_$$ || var1 || $$.csv' WITH CSV$$;
...

Your quotes got tangled. Using dollar-quoting to simplify. Note that syntax highlighting here on SO is misleading because it does not understand dollar-quoting.

DECLARE is only needed once (not an error though). Plus, BEGIN was missing.

And to_char() makes the text representation of a date independent of the locale.

How to copy a csv file using SQL

You'll have to use dynamic SQL, since COPY doesn't support parameters:

EXECUTE format(
'COPY match_import FROM %L (FORMAT ''csv'', DELIMITER '','', HEADER)',
filename
);

How to dynamically name output of a sql file in sqlcmd

Give this a try:

set timehour=%time:~0,2%
sqlcmd -S INSTANCENAME -i c:\Users\name\Desktop\test.sql -o c:\Users\name\Desktop\name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv

You can try seeing the output by using Echo

set timehour=%time:~0,2%
echo name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv

%date% returns current date in short format. The ":~6,4" part is like a
SUBSTRING function which returns 4 characters starting from position
6, which returns Year. Similarly, retrieving month, day, hour,
minutes using same function and appending all of this together to
generate the file name in format "name-YYYYMMDD-HHMM"

SSIS - How to design output file-name (CSV) as a Dynamic Project Parameter?

User:Jo Douglass gave the logical solution to the problem. This answer to a SO question gives a detailed rundown on why we cannot use Expressions in Project Parameters and an alternative approach to solve the issue.



Related Topics



Leave a reply



Submit