27

Is there any way by which we can export the result of a select statment to CSV file, just like in MySQL.

MySQL Command;

SELECT col1,col2,coln into OUTFILE  'result.csv' 
FIELDS TERMINATED BY ',' FROM testtable t;
AngocA
  • 7,655
  • 6
  • 39
  • 55
Rakesh Juyal
  • 35,919
  • 68
  • 173
  • 214

6 Answers6

44

You can run this command from the DB2 command line processor (CLP) or from inside a SQL application by calling the ADMIN_CMD stored procedure

EXPORT TO result.csv OF DEL MODIFIED BY NOCHARDEL 
SELECT col1, col2, coln FROM testtable;

There are lots of options for IMPORT and EXPORT that you can use to create a data file that meets your needs. The NOCHARDEL qualifier will suppress double quote characters that would otherwise appear around each character column.

Keep in mind that any SELECT statement can be used as the source for your export, including joins or even recursive SQL. The export utility will also honor the sort order if you specify an ORDER BY in your SELECT statement.

AngocA
  • 7,655
  • 6
  • 39
  • 55
Fred Sobotka
  • 5,252
  • 22
  • 32
  • `DEL` is not supported on zOS DB2. – Stavr00 Nov 17 '16 at 18:37
  • 1
    is there a way to EXPORT to csv and include the headers in the output file? – Elias Ghali Oct 04 '19 at 06:41
  • The EXPORT utility does not produce a header row itself, and it won't append its results to the output file, so anything that was previously in the file (including a header row) will be overwritten. If all of the columns in your result set are CHAR or VARCHAR, your query could use UNION ALL to include a row of column headings, along with an ORDER BY that forces the header row to appear first. Alternatively, if you're using the CLP to run the EXPORT command, you could follow up with a call to an external program such as sed -i to prepend a line of text at the beginning of the output file. – Fred Sobotka Oct 06 '19 at 23:36
  • 1
    How If I want to append instead of override the content inside the file? – Panadol Chong Nov 06 '19 at 09:27
  • Db2's EXPORT utility will always overwrite the output file if it exists. There is no option for EXPORT to append to a file. – Fred Sobotka Dec 07 '19 at 05:29
  • @FredSobotka I have squirrel client and I want to export data as insert statement. How can i do that? – Aakash Patel Dec 18 '19 at 07:41
  • @AakashPatel Please open a new question for that if you're still looking for an answer – Fred Sobotka Jan 14 '20 at 07:39
2

This is how you can do it from DB2 client.

  1. Open the Command Editor and Run the select Query in the Commands Tab.

  2. Open the corresponding Query Results Tab

  3. Then from Menu --> Selected --> Export

padma057
  • 37
  • 4
2

DBeaver allows you connect to a DB2 database, run a query, and export the result-set to a CSV file that can be opened and fine-tuned in MS Excel or LibreOffice Calc.

To do this, all you have to do (in DBeaver) is right-click on the results grid (after running the query) and select "Export Resultset" from the context-menu.

This produces the dialog below, where you can ultimately save the result-set to a file as CSV, XML, or HTML:

enter image description here

Lonnie Best
  • 9,936
  • 10
  • 57
  • 97
1

According to the docs, you want to export of type del (the default delimiter looks like a comma, which is what you want). See the doc page for more information on the EXPORT command.

Tanktalus
  • 21,664
  • 5
  • 41
  • 68
1

I tried this and got a ';'-delimited csv file:

--#SET TERMINATOR % 
EXPORT TO result.csv OF DEL MODIFIED BY CHARDEL;
SELECT * FROM A
Fred Sobotka
  • 5,252
  • 22
  • 32
Taka
  • 19
  • 1
  • The `CHARDEL` modifier specifies the character that encloses any string values in the row. Its default is `"`. To change the delimiter that goes between columns, use the `COLDEL` modifier. – Fred Sobotka Dec 30 '18 at 22:44
0

I'm using IBM Data Studio v 3.1.1.0 with an underlying DB2 for z/OS and the accepted answer didn't work for me. If you're using IBM Data Studio (v3.1.1.0) you can:

  1. Expand your server connection in "Administration Explorer" view;
  2. Select tables or views;
  3. On the right panel, right click your table or view;
  4. There should be an option to extract/download data, in portuguese it says: "Descarregar -> Com sql" - something like "Download -> with sql;"
HFSDev
  • 417
  • 8
  • 15