0x8
0x8

DuckDB导入导出Excel数据

DuckDB导入Excel数据

DuckDB支持从Excel导入数据，但只支持.xlsx，不支持.xls。如果原始数据是.xls就需要用excel2007及以上版本打开重新保存为.xlsx格式。

DuckDB导入excel工作表

在select语句中使用read_xlsx函数读取excel文件，默认读取第一个sheet。

1

SELECT * FROM read_xlsx('test.xlsx');

方便的是，可以省略read_xlsx函数，DuckDB也会自适应判断文件类型进行读取。

1

SELECT * FROM 'test.xlsx';

read_xlsx函数有许多参数可自定义，如sheet参数可指定工作表名称。

1

SELECT * FROM read_xlsx('test.xlsx', sheet = 'Sheet2');

DuckDB导入excel指定范围数据

下面实例使用range参数，指定A1:B2范围数据，A1为左上角单元格，B2为右下角单元格。

1

SELECT * FROM read_xlsx('test.xlsx', range = 'A1:B2');

下面实例表示跳过前5行

1

SELECT * FROM read_xlsx('test.xlsx', range = 'A5:Z');

下面实例表示跳过前5列

1

SELECT * FROM read_xlsx('test.xlsx', range = 'E:Z');

默认情况下，如果没有指定范围，当遇到空行时，DuckDB将停止读取Excel文件。但是当提供了一个范围时，默认是读取到范围的末尾。

以上行为可以通过stop_at_empty参数来控制：

1
2
3
4
5

-- 读取前100行，或直到遇到第一个空行，以先到者为准
SELECT * FROM read_xlsx('test.xlsx', range = '1:100', stop_at_empty = true);

-- 始终读取整个工作表即使它包含空行
SELECT * FROM read_xlsx('test.xlsx', stop_at_empty = false);

DuckDB读取excel并创建新表

以下实例表示读取test.xlsx文件中的Sheet2工作表，并创建名为newtb的新表。

1
2

CREATE TABLE newtb AS 
  SELECT * FROM read_xlsx('test.xlsx', sheet = 'Sheet2');

DuckDB读取excel并插入已有表

以下表示读取test.xlsx中的Sheet2并将数据插入oldtb表。

1
2

INSERT INTO oldtb
  SELECT * FROM read_xlsx('test.xlsx', sheet = 'Sheet2');

或者使用copy也可以插入数据

1

COPY oldtb FROM 'test.xlsx' (FORMAT xlsx, SHEET 'Sheet2');

使用COPY语句将Excel文件插入到现有表中时，目标表中列的类型将强制为Excel工作表中单元格的类型。

导入数据时设置是否包含header 

1

SELECT * FROM read_xlsx('test.xlsx', header = true);

默认情况下，如果第一行中的所有单元格都是非空字符，则第一行将被视为header。要禁用此行为，请将header设置为false。

数据类型检测

导入数据时，DuckDB会自动检测导入列数据类型。

  • 单元格如果是数字的话，可能匹配为TIMESTAMP , TIME , DATE 和 BOOLEAN 类型。
  • 包含TRUE 和 FALSE 的文本单元格可能匹配为 BOOLEAN 类型。
  • 默认情况下，空单元格的类型为DOUBLE 。
  • 其他情况，根据单元格的内容推断为 VARCHAR 或 DOUBLE 。

还可以通过设置 empty_as_varchar参数来控制空单元格类型检测方式。

将所有空单元格处理为 VARCHAR 而不是 DOUBLE ，将 empty_as_varchar 设置为 true ：

1

SELECT * FROM read_xlsx('test.xlsx', empty_as_varchar = true);

禁用类型推断并将所有单元格设置为 VARCHAR，将 all_varchar设置为 true ：

1

SELECT * FROM read_xlsx('test.xlsx', all_varchar = true);

将 ignore_errors 参数设置为 true ， DuckDB会将不能断列类型的单元格替换为 NULL。

1

SELECT * FROM read_xlsx('test.xlsx', ignore_errors = true);

DuckDB导出Excel

同数据导入，DuckDB只支持将数据导出为.xlsx文件。

将数据表导出为excel文件

1

COPY tb TO 'output.xlsx' WITH (FORMAT xlsx);

查询结果也可以直接导出为Excel文件：

1

COPY (SELECT * FROM tb) TO 'output.xlsx' WITH (FORMAT xlsx);

使用 HEADER 选项将表头写入Excel文件的第一行，：

1

COPY tb TO 'output.xlsx' WITH (FORMAT xlsx, HEADER true);

使用 SHEET 选项设置工作表名称：

1

COPY tb TO 'output.xlsx' WITH (FORMAT xlsx, SHEET 'newSheet');

导出数据类型转换

Excel实际上只支持存储数字或字符串——相当于 VARCHAR 和 DOUBLE ，当导出XLSX文件时会按以下规则进行类型转换：

  • 数值类型被强制转换为 DOUBLE
  • 时间类型（ TIMESTAMP , DATE , TIME 等）被转换为excel“序列号”，即日期为1900-01-01以来的天数，时间为一天的小数。然后用“数字格式”进行样式设置，以便在Excel中打开时显示为日期或时间。
  • TIMESTAMP_TZ 和 TIME_TZ 分别转换为UTC TIMESTAMP 和 TIME ，时区信息丢失。
  • BOOLEAN转换为 1 和 0 ，并应用“数字格式”使它们在Excel中显示为 TRUE 和 FALSE 。
  • 所有其他类型都被强制转换为 VARCHAR ，然后写入文本单元格

除了以上规则，也可以在导出Excel之前自定义将列转换为不同的类型：

1

COPY (SELECT CAST(a AS VARCHAR), b FROM tb) TO 'output.xlsx' WITH (FORMAT xlsx);

参考

0%