使用列名从 db2 导出数据 [英] Export data from db2 with column names
问题描述
我想将 db2 表中的数据导出为 csv 格式.我还需要第一行应该是所有列名.
I want to export data from db2 tables to csv format.I also need that first row should be all the column names.
我使用以下命令收效甚微
I have little success by using the following comand
EXPORT TO "TEST.csv"
OF DEL
MODIFIED BY NOCHARDEL coldel: ,
SELECT col1,'COL1',x'0A',col2,'COL2',x'0A'
FROM TEST_TABLE;
但是有了这个我得到像
Row1 Value:COL1:
Row1 Value:COL2:
Row2 Value:COL1:
Row2 Value:COL2:
等等
我也尝试了以下查询
EXPORT TO "TEST.csv"
OF DEL
MODIFIED BY NOCHARDEL
SELECT 'COL1',col1,'COL2',col2
FROM ADMIN_EXPORT;
但是当使用 excel 打开时,这会列出每行数据的列名.
But this lists column name with each row data when opened with excel.
有没有办法获取以下格式的数据
Is there a way i can get data in the format below
COL1 COL2
value value
value value
在 excel 中打开时.
when opened in excel.
谢谢
推荐答案
经过几天的搜索,我这样解决了这个问题:
After days of searching I solved this problem that way:
EXPORT TO ...
SELECT 1 as id, 'COL1', 'COL2', 'COL3' FROM sysibm.sysdummy1
UNION ALL
(SELECT 2 as id, COL1, COL2, COL3 FROM myTable)
ORDER BY id
你不能从无中选择 db2 中的常量字符串,所以你必须从 sysibm.sysdummy1 中选择.要在第一行中手动添加列,您必须添加一个伪 ID 并按该 ID 对 UNION 结果进行排序.否则,标题可能位于结果文件的底部.
You can't select a constant string in db2 from nothing, so you have to select from sysibm.sysdummy1. To have the manually added columns in first row you have to add a pseudo-id and sort the UNION result by that id. Otherwise the header can be at the bottom of the resulting file.
这篇关于使用列名从 db2 导出数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!