3

I have copied function(from one of the webportal and modified accordingly) to copy data from csv file to table.

create or replace function public.load_csv_file
(
target_table text,
csv_path text,
col_count integer
)

returns void as $$

declare

iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or     spreadsheet

begin
set schema 'public';

create table insert_from_csv ();

-- add just enough number of columns
for iter in 1..col_count
loop
    execute format('alter table insert_from_csv add column col_%s text;', iter);
end loop;

-- copy the data from csv file
execute format('copy insert_from_csv from %L with delimiter '','' quote ''"'' csv ', csv_path);

iter := 1;
col_first := (select col_1 from insert_from_csv limit 1);

-- update the column names based on the first row which has the column names
for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
loop
    execute format('alter table insert_from_csv rename column col_%s to %s', iter, col);
    iter := iter + 1;
end loop;

-- delete the columns row
execute format('delete from insert_from_csv where %s = %L', col_first, col_first);

-- change the temp table name to the name given as parameter, if not blank
if length(target_table) > 0 then
    execute format('alter table insert_from_csv rename to %I', target_table);
end if;

end;

$$ language plpgsql;

And passing parameters as

select load_csv_file('Customer','C:\Insert_postgres.csv' ,4)

but getting error message

ERROR: could not open file "C:\Insert_postgres.csv" for reading: No such file or directory CONTEXT: SQL statement "copy insert_from_csv from E'C:\Insert_postgres.csv' with delimiter ',' quote '"' csv " PL/pgSQL function load_csv_file(text,text,integer) line 21 at EXECUTE

Any help appreciated.

4
  • 3
    Is your file on the client machine or the server machine? I believe the copy statement will only look for files on the server machine. Commented Jun 8, 2017 at 16:06
  • @jmelesky, file is on client machine. i tried putting "\" in copy statement like below execute format('\copy insert_from_csv from %L with delimiter '','' quote ''"'' csv ', csv_path); and got error: ERROR: syntax error at or near "\" SQL state: 42601 Context: PL/pgSQL function load_csv_file(text,text,integer) line 21 at EXECUTE Commented Jun 9, 2017 at 11:58
  • That sounds right. \copy is a feature of the command-line tool psql, not part of the SQL language or the pl/pgsql language. I do not know if there's a way to do what you want. Commented Jun 9, 2017 at 14:47
  • Ok, no worries, thanks for looking into it. Commented Jun 9, 2017 at 14:50

1 Answer 1

2

I tried to moving file to some other location, 'C:\testData\Insert_postgres.csv'

Initially i was trying below

select load_csv_file('Customer','C:\Insert_postgres.csv' ,4)

and received error

After several tries i thought i will create a folder on c drive and put the csv file in that folder and it worked.

select load_csv_file('Customer','C:\testData\Insert_postgres.csv' ,4).

It seems postgres couldnt access file directly from c drive, but if file is put in a folder on c: drive then worked successfully.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.