PGSQL如何实现类似COPY命令读取CSV效果的XLS文件读取功能

PostgreSQL原生未内置直接读取XLS/XLSX格式文件的功能，以下是几种常用的实现方式，可匹配你需要的类似COPY命令的读取效果：

方案1：中转CSV后用COPY导入（无插件依赖，最稳定） #

先通过Office工具或xls2csv等命令行工具把Excel文件转存为CSV格式，再直接用COPY命令读取，和你读取文本/CSV的逻辑完全一致：

-- 服务端文件导入，对应你原来的10列无表头配置
COPY target_table(f1, f2, f3, f4, f5, f6, f7, f8, f9, f10)
FROM 'Y:/2021.csv'
WITH (
    FORMAT csv,
    HEADER false,
    ENCODING 'utf8'
);

-- 如果是客户端本地文件，用psql元命令\copy
\copy target_table(f1, f2, f3, f4, f5, f6, f7, f8, f9, f10) FROM 'Y:/2021.csv' WITH (FORMAT csv, HEADER false, ENCODING 'utf8');

方案2：PL/Python函数直接读取（无需中转文件） #

如果允许使用Python扩展，可以通过PL/Python封装读取逻辑，调用体验和OPENROWSET接近：

1. 先在PostgreSQL服务器安装Python3，以及pandas、xlrd（读取.xls）、openpyxl（读取.xlsx）依赖包
2. 执行以下SQL配置：

-- 开启PL/Python扩展
CREATE EXTENSION IF NOT EXISTS plpython3u;

-- 创建读取Excel的自定义函数
CREATE OR REPLACE FUNCTION read_excel(file_path text, sheet_name text)
RETURNS TABLE (
    f1 text, f2 text, f3 text, f4 text, f5 text,
    f6 text, f7 text, f8 text, f9 text, f10 text
) AS $$
import pandas as pd
df = pd.read_excel(file_path, sheet_name=sheet_name, header=None, dtype=str)
for row in df.itertuples(index=False, name=None):
    yield row[:10] + (None,) * max(0, 10 - len(row))
$$ LANGUAGE plpython3u;

-- 直接调用函数读取Excel，等价于你原MSSQL语句的效果
SELECT * FROM read_excel('Y:/2021.xlsx', 'Global');

方案3：xlsx_fdw外部表（查询体验和普通表完全一致） #

如果需要更贴近OPENROWSET的使用方式，可以安装multicorn扩展和xlsx_fdw外部数据包装器，配置外部表后直接查询：

-- 安装依赖扩展
CREATE EXTENSION IF NOT EXISTS multicorn;

-- 创建Excel外部服务
CREATE SERVER excel_server FOREIGN DATA WRAPPER multicorn
OPTIONS (wrapper 'xlsx_fdw.XlsxForeignDataWrapper');

-- 创建对应Excel工作表的外部表
CREATE FOREIGN TABLE excel_2021_global (
    f1 text, f2 text, f3 text, f4 text, f5 text,
    f6 text, f7 text, f8 text, f9 text, f10 text
) SERVER excel_server
OPTIONS (
    filename 'Y:/2021.xlsx',
    sheetname 'Global',
    skip_header '0'
);

-- 直接查询外部表即可读取Excel内容
SELECT * FROM excel_2021_global;

注意事项 #

• Windows环境下文件路径可以用正斜杠/或者双反斜杠\\避免转义问题
• 所有直接读取Excel的方案，文件路径默认都是PostgreSQL服务端的本地路径，需要读取客户端文件请先上传到服务端或者使用客户端中转方案

内容的提问来源于stack exchange，提问作者dn2301