CODEWITHSUNDEEP
db2export-to-excel
yashbinani
yashbinani

Reputation: 387

Export data from db2 with column names

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;

But with this i get data like 

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

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 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

when opened in excel.

Thanks

Upvotes: 4

Views: 31423

Answers (3)

kkuduk
kkuduk

Reputation: 601

Quite old question, but I've encountered recently the a similar one realized this can be achieved much easier in 11.5 release with EXTERNAL TABLE feature, see the answer here: https://stackoverflow.com/a/57584730/11946299

Example:

$ 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

Upvotes: 1

Benvorth
Benvorth

Reputation: 7722

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

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.

Upvotes: 8

Michael Sharek
Michael Sharek

Reputation: 5069

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

Use order by to make sure that the row with the column names comes out first.

Upvotes: -1

Related Questions