Geek asks and answers

Export data from db2 with column names

I want to export data from db2 tables to csv format. I also need the first row to be all column names.

I have little success using the following command

EXPORT TO "TEST.csv" 
OF DEL 
MODIFIED BY NOCHARDEL coldel: ,
SELECT col1,'COL1',x'0A',col2,'COL2',x'0A' 
FROM TEST_TABLE;

But with this I get data like

Row1 Value:COL1:
Row1 Value:COL2:
Row2 Value:COL1:
Row2 Value:COL2:

and etc.

I also tried the following query

EXPORT TO "TEST.csv" 
OF DEL 
MODIFIED BY NOCHARDEL 
SELECT 'COL1',col1,'COL2',col2 
FROM ADMIN_EXPORT;

But this indicates the column name with each row when opened with excel.

Is there any way to get the data in the format below

COL1   COL2
value  value
value  value

when opening in excel.

thank

+3
source share
3 answers

After several days of searching, I solved this problem as follows:

 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. , UNION . .

+6

, , , 11.5 EXTERNAL TABLE, . : fooobar.com/questions/18278697/...

:

$ db2 "create external table '/home/db2v115/staff.csv' using (delimiter ',' includeheader on) as select * from staff"
DB20000I  The SQL command completed successfully.
$ head /home/db2v115/staff.csv | column -t -s ',' 
ID  NAME      DEPT  JOB    YEARS  SALARY    COMM
10  Sanders   20    Mgr    7      98357.50  
20  Pernal    20    Sales  8      78171.25  612.45
30  Marenghi  38    Mgr    5      77506.75  
40  O'Brien   38    Sales  6      78006.00  846.55
50  Hanes     15    Mgr    10     80659.80  
60  Quigley   38    Sales         66808.30  650.25
70  Rothman   15    Sales  7      76502.83  1152.00
80  James     20    Clerk         43504.60  128.20
90  Koonitz   42    Sales  6      38001.75  1386.70
0

Insert the column names as the first row in the table.

Use order to ensure that the row with the column names comes out first.

-2
source

More articles:


All Articles