无法将CSV文件从本地计算机复制到远程服务器 [英] Cannot COPY a CSV file from local machine to remote server

问题描述

我正在使用以下命令将放置在本地系统中的CSV文件导入到远程服务器中的表中。

I'm using the below command to import the CSV file which is placed in my local system to a table in remote server.

但是我得到了

NOTICE:  table "temp_table" does not exist, skipping

ERROR:  could not open file "/Users/linu/downloads/numb.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
CONTEXT:  SQL statement "copy temp_table from '/Users/linu/downloads/numb.csv' with delimiter ';' quote '"' csv "
PL/pgSQL function staging.load_csv_file(text,text,integer) line 22 at EXECUTE
SQL state: 58P01

下面是函数详细信息，有人可以帮我找到解决方案，而无需在服务器端复制文件并建议对以下功能进行必要的更改以使其正常工作吗？

Below is the function details,Can anyone please help me to find solution for this without copying the file at server side and suggesting necessary changes to the below function to make it work?

我了解该错误是因为服务器无法识别我的本地路径，已经读了几篇文章，但对如何解决却不了解。任何提示或建议都会有很大帮助。

I understand that the error is because the server cannot identify my local path,Have gone through few posts but didn't understand on how to resolve it.Any hint or suggestions will be of great help.

**Function call**
select staging.load_csv_file('numb','/Users/linu/downloads/numb.csv',73)

**Function Body**
create or replace function staging.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 'staging';

    DROP TABLE IF EXISTS temp_table;
    create  table temp_table ();

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

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

    iter := 1;
    col_first := (select col_1 from temp_table limit 1);
  -- raise notice 'path', csv_path;
    -- 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 temp_table rename column col_%s to %s', iter, col);
        iter := iter + 1;
    end loop;

    -- delete the columns row
    execute format('delete from temp_table 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 temp_table rename to %I', target_table);
    end if;

end;

$$ language plpgsql;

注意：我使用的是PostgreSQL

Note: I'm using PostgreSQL

推荐答案

您无法在PostgreSQL服务器代码中执行此操作，因为数据库服务器无法访问客户端上的文件。

You cannot do that in PostgreSQL server code, since the database server has no access to files on your client.

您拥有用支持从STDIN复制的所选语言编写客户端代码。

You have to write client code in a language of your choice that supports COPY FROM STDIN.

这篇关于无法将CSV文件从本地计算机复制到远程服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！