将数据从Excel导入PostgreSQL [英] Import data from Excel to PostgreSQL

问题描述

这里是我的问题，我在这里事情：
我有一个excel电子表格（.xlsx），我转换为逗号分隔值（.CSV），因为它在一些答案说：

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

  ------------------ -------------------------------- 
 name |姓| voteNo | VoteA | VoteB | VoteC 
 --------------------------------------------- ----- 
 john |史密斯| 1001 | 30 | 154 | 25 
 --------------------------------------------- ----- 
 anothe |人| 1002 | 430 | 34 | 234 
 --------------------------------------------- ----- 
 other |一| 1003 | 35 | 154 | 24 
 --------------------------------------------- ----- 
 john |史密斯| 1004 | 123 | 234 | 53 
 --------------------------------------------- ----- 
 john |史密斯| 1005 | 23 | 233 | 234 
 ---------------------------------------------在PostgreSQL中，我创建了一个名为 allfields  
 
 
 
 并创建了6列
第一和第二个作为字符[]，最后4个作为整数与excel表中显示的相同名称（姓名，现在我这样做：
 pre> 从'C：\ Filepath\filename.csv'复制allfields; 
  

但我收到此错误：

< blockquote>

 无法打开文件C：\Filepath\filename.csv以供阅读：权限被拒绝
 SQL状态：42501 
  

我的问题是：

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



>解决方案

确定问题是，我需要更改 Excel文件的路径 c>。我将其插入公共帐户，所有用户都可以访问 。

如果您遇到同样的问题，请将 excel文件移至ex C：\ \User\Public 文件夹（此文件夹是一个公共文件夹没有任何限制），否则你必须处理 Windows权限问题。

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.

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:

My excel file looks something like this:

--------------------------------------------------
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 allfields and 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)

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

My questions are:

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

解决方案

Ok the Problem was that i need to change the path of the Excel file. I inserted it in the public account where all users can access it.

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

这篇关于将数据从Excel导入PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！