将数据从 Excel 导入 PostgreSQL

Import data from Excel to PostgreSQL

提问by hrskrs

I have seen questions on stackoverflow similar/same as the one I am asking now, however I couldn't manage to solve it in my situation.

我在stackoverflow上看到过与我现在问的问题类似/相同的问题，但是在我的情况下我无法解决它。

Here is the thing: I have an excel spreadsheet(.xlsx) whom i converted in comma seperated value(.CSV) as it is said in some answers:

事情是这样的：我有一个 excel 电子表格（.xlsx），我将其转换为逗号分隔值（.CSV），正如一些答案中所说：

My excel file looks something like this:

我的 excel 文件看起来像这样：

--------------------------------------------------
name  |  surname | voteNo  | VoteA | VoteB | VoteC
--------------------------------------------------
john  |  smith   | 1001    | 30    | 154   | 25
--------------------------------------------------
anothe|  person  | 1002    | 430   | 34    | 234
--------------------------------------------------
other |  one     | 1003    | 35    | 154   | 24
--------------------------------------------------
john  |  smith   | 1004    | 123   | 234   | 53
--------------------------------------------------
john  |  smith   | 1005    | 23    | 233   | 234
--------------------------------------------------

In PostgreSQL I created a table with name allfieldsand created 6 columns 1st and 2nd one as a character[] and last 4 ones as integers with the same name as shown in the excel table (name, surname, voteno, votea, voteb, votec)

在 PostgreSQL 中，我创建了一个带有名称的表，allfields并创建了 6 列第一和第二列作为字符 []，最后 4 列作为与 excel 表中显示的名称相同的整数(name, surname, voteno, votea, voteb, votec)

Now I'm doing this:

现在我这样做：

copy allfields from 'C:\Filepath\filename.csv';

But I'm getting this error:

但我收到此错误：

could not open file "C:\Filepath\filename.csv" for reading: Permission denied
SQL state: 42501

could not open file "C:\Filepath\filename.csv" for reading: Permission denied
SQL state: 42501

My questions are:

我的问题是：

1. Should I create those columns in allfieldstable in PostgreSQL?
2. Do I have to modify anything else in Excel file?
3. And why I get this 'permission denied' error?

1. 我应该allfields在 PostgreSQL 的表中创建这些列吗？
2. 我是否必须修改 Excel 文件中的其他内容？
3. 为什么我会收到这个“权限被拒绝”错误？

采纳答案by hrskrs

Ok the Problem was that i need to change the pathof the Excel file. I inserted it in the public accountwhere all users can access it.

好了，问题是，我需要改变path的Excel file。我将它插入到所有用户都可以访问的公共帐户中。

If you face the same problem move your excel fileto ex C:\\User\Publicfolder (this folder is a public folder without any restrictions), otherwise you have to deal with Windows permission issues.

如果您遇到同样的问题，请将您的文件夹移至excel fileexC:\\User\Public文件夹（此文件夹是公共文件夹，没有任何限制），否则您必须处理Windows permission issues.

回答by yieldsfalsehood

1. Based on your file, neither of the first two columns needs to be an array type (character[]) - unlike C-strings, the "character" type in postgres isa string already. You might want to make things easier and use varcharas the type of those two columns instead.
2. I don't think you do.
3. Check that you don't still have that file open and locked in excel - if you did a "save as" to convert from xlsx to csv from within excel then you'll likely need to close out the file in excel.

1. 根据您的文件，前两列都不需要是数组类型 (character[]) - 与 C 字符串不同，postgres 中的“字符”类型已经是字符串。您可能想让事情变得更简单，varchar而是用作这两列的类型。
2. 我不认为你有。
3. 检查您是否仍然没有打开并在 excel 中锁定该文件 - 如果您执行了“另存为”以从 excel 中从 xlsx 转换为 csv，那么您可能需要在 excel 中关闭该文件。

回答by Alfabravo

SQL state: 42501in PostgreSQL means you don't have permission to perform such operation in the intended schema. This error code listshows that.

SQL 状态：PostgreSQL 中的 42501 表示您无权在预期架构中执行此类操作。此错误代码列表显示了这一点。

Check that you're pointing to the correct schema and your user has enough privileges.

检查您是否指向正确的架构并且您的用户具有足够的权限。

Documentation also statesthat you need select privileges on origin table and insert privileges on the destination table.

文档还指出您需要对原始表的选择权限和对目标表的插入权限。

You must have select privilege on the table whose values are read by COPY TO, and insert privilege on the table into which values are inserted by COPY FROM. It is sufficient to have column privileges on the column(s) listed in the command.

您必须对通过 COPY TO 读取其值的表具有选择权限，并对通过 COPY FROM 插入值的表具有插入权限。对命令中列出的列拥有列权限就足够了。

回答by wonhee

1. Yes I think you can. For COPY command, there is optional HEADER clause. Check http://www.postgresql.org/docs/9.2/static/sql-copy.html
2. I don't think so. With my #1 and #3, it should works.
3. You need superuser permission for that.

1. 是的，我认为你可以。对于 COPY 命令，有可选的 HEADER 子句。检查 http://www.postgresql.org/docs/9.2/static/sql-copy.html
2. 我不这么认为。使用我的 #1 和 #3，它应该可以工作。
3. 你需要超级用户权限。

回答by Erwin Brandstetter

1) Should I create those columns in allfields table in PostgreSQL?

1) 我应该在 PostgreSQL 的 allfields 表中创建那些列吗？

Use textfor the character fields. Not an array in any case, as @yieldsfalsehood pointed out correctly.

使用text的字符字段。无论如何都不是数组，正如@yieldsfalsehood 正确指出的那样。

2) Do I have to modify anything else in Excel file?

2) 我是否必须修改 Excel 文件中的其他内容？

No.

不。

3) And why I get this 'permission denied' error?

3) 为什么我会收到这个“权限被拒绝”错误？

The file needs be accessible to your system user postgres(or what ever user you are running the postgres server with). Per documentation:

该文件需要您的系统用户postgres（或您运行 postgres 服务器的任何用户）可以访问。根据文档：

COPYwith a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible to the server and the name must be specified from the viewpoint of the server.

COPY使用文件名指示 PostgreSQL 服务器直接读取或写入文件。服务器必须可以访问该文件，并且必须从服务器的角度指定名称。

The privileges of the database user are not the cause of the problem. However (quoting the same page):

数据库用户的权限不是问题的原因。但是（引用同一页）：

COPYnaming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

COPY命名文件或命令只允许数据库超级用户，因为它允许读取或写入服务器有权访问的任何文件。

回答by jjanes

Regarding the permission problem, if you are using psql to issue the COPYcommand, try using \copyinstead.

关于权限问题，如果是使用psql下发COPY命令，请尝试使用\copy。

回答by Caleb Rotich

For those who do not wish to move the files they wish to read to a different location(public) for some reason. Here is a clear solution.

对于那些由于某种原因不想将他们希望阅读的文件移动到不同位置（公共）的人。这是一个明确的解决方案。

1. Right click the folder holding the file and select properties.
2. Select the Securitytab under properties.
3. Select Edit
4. Select Add
5. Under the field Enter the object Names to select, Type in Everyone
6. Click OKto all the dialog boxes or Applyif it is activated
7. Try reading the file again.

1. 右键单击保存文件的文件夹并选择属性。
2. 选择属性下的安全选项卡。
3. 选择编辑
4. 选择添加
5. 场下输入对象名称来选择，键入每个人
6. 对所有对话框单击“确定”，如果已激活则单击“应用”
7. 再次尝试读取文件。