CODEWITHSUNDEEP
postgresqlpostgresql-9.3postgresql-9.2
Geeme
Geeme

Reputation: 415

PostgreSQL - error - could not open file "C:\Insert_postgres.csv" for reading: No such file or directory

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.

Upvotes: 3

Views: 4233

Answers (1)

Geeme
Geeme

Reputation: 415

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.

Upvotes: 2

Related Questions