Exporting result of select statement to CSV format in DB2

csv db2 export
Rakesh Juyal picture Rakesh Juyal · Jun 29, 2009 · Viewed 132.3k times · Source

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;

Answer

Fred Sobotka picture Fred Sobotka · Jun 29, 2009

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.

Related questions

How to concatenate text from multiple rows into a single text string in SQL server?

Consider a database table holding names, with three rows: Peter Paul Mary Is there an easy way to turn this into a single string of Peter, Paul, Mary?

Read More
Writing a pandas DataFrame to CSV file

I have a dataframe in pandas which I would like to write to a CSV file. I am doing this using: df.to_csv('out.csv') And getting the error: UnicodeEncodeError: 'ascii' codec can't encode character u'\u03b1' …

Read More
How to output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format? Here's what I'm doing now: mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | …

Read More