将Excel数据导入PostgreSQL 9.3 [英] Import Excel Data into PostgreSQL 9.3
问题描述
提前感谢所有的帮助!
典型的答案是这样的:
-
在Excel中,文件/另存为,选择CSV,保存当前工作表。
-
转到Pg服务器上的postgres用户可以访问的保存目录
-
在PostgreSQL中:
COPY mytable FROM'/ path / to / csv / file'WITH CSV HEADER;- 必须是超级用户
但还有其他这样做也是如此。 PostgreSQL是一个令人惊讶的可编程数据库。这些包括:
-
在pl / javaU,pl / perlU或其他不受信任的语言中编写一个模块来访问文件,解析它,并管理结构。
-
使用CSV和fdw_file作为伪表访问它
-
使用DBILink和DBD :: Excel
-
编写您自己的外部数据包装器以阅读Excel文件。
可能性是无止境的....
I've developed a huge table in excel and now facing problem in transferring it into the postgresql database. I've downloaded the odbc software and I'm able to open table created in postgresql with excel. However, I'm not able to do it in a reverse manner which is creating a table in excel and open it in the postgresql. So I would like to know it is can be done in this way or is there any alternative ways that can create a large table with pgAdmin III cause inserting the data raw by raw is quite tedious.
Thanks in advance for all the helps!
the typical answer is this:
In Excel, File/Save As, select CSV, save your current sheet.
transfer to a holding directory on the Pg server the postgres user can access
in PostgreSQL:
COPY mytable FROM '/path/to/csv/file' WITH CSV HEADER; -- must be superuser
But there are other ways to do this too. PostgreSQL is an amazingly programmable database. These include:
Write a module in pl/javaU, pl/perlU, or other untrusted language to access file, parse it, and manage the structure.
Use CSV and the fdw_file to access it as a pseudo-table
Use DBILink and DBD::Excel
Write your own foreign data wrapper for reading Excel files.
The possibilities are literally endless....
这篇关于将Excel数据导入PostgreSQL 9.3的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!