I am working with IBM DB2. My operating system is Unix-based (AIX), and I am working directly on the console (No GUI). I have a table called "users". For the schema, let’s just use "myschema". (But the schema was set after the login so I don't need to write again).

The table has 10 columns. I want to export the table as a CSV, but only with 3 of the columns. However, no matter which command I use, the darn thing writes everything into CSV-column "A".

So everything ends up in one column (but several rows). As shown in the commands, I actually only want to export "userid", "name", and "description". The description could contain special characters like ",+.&/" .

I would also export everything and then delete the unnecessary columns. But as mentioned, it writes everything into one column in the CSV. Into column A.

What I have tried so far:

db2 "export to users.csv of del select userid,name,description from users"

db2 "export to users.csv of del modified by coldel, select userid,name,description from users"
db2 "export to users.csv of del modified by coldel\t select userid,name,description from users"
db2 "export to users.csv of del modified by NOCHARDEL select userid,name,description from users"

also used "call" commands like

db2 "CALL SYSPROC.ADMIN_CMD('EXPORT TO users.csv OF DEL MODIFIED BY COLDEL, SELECT userid,name,description FROM users')"

and I also tried to export to users.txt and to awk,sed and cut. But no. It doesn't work, and I don't know why.

db2 -x "select userid,name,description from users" > users.txt

and then

awk '{print $1 "," $2 "," $3}' users.txt > users.csv
or
sed 's/\t/,/g' users.txt > users.csv
or
cut -d ' ' -f 1,2,3 users.txt | tr ' ' ',' > users.csv

Why is the system doing this?

Edit

I want each row of the database table to be written into its own Excel (CSV)-row. This works. However, I want the database table column 'userid' to go into CSV column 'A', 'Name' into 'B', and 'Description' into 'C', but this doesn't work.

The results are all written into CSV column 'A'. So 'B' and 'C' remain empty. Each DBtable-column schould get its own CSV-column

Databasetable: columns are "userid","name" and "description"
e.g. results: "UserID" "Name" "Description"

000001 "Doe, John" "Adminuser, Superadmin"
000002 "Doe, Thomas" "Adminuser, Moderator"
000003 "Doe, Karen" "Customer/Premiumcustomer"
000004 "Bot, Bot" "Bot +Hidden"

Like I said, actually all results has its own row but is only in one CSV-column. "A"
Actually CSV looks like e.g
A1=00001~"Doe, John"~"Adminuser, Superadmin"
A2=00002~"Doe, Thomas"~"Adminuser, Moderator"
A3=00003~"Doe, Karen"~"Customer/Premiumcustomer"
A4=00004~"Bot, Bot"~"Bot +Hidden"

B and C are empty.

I am working with IBM DB2. My operating system is Unix-based (AIX), and I am working directly on the console (No GUI). I have a table called "users". For the schema, let’s just use "myschema". (But the schema was set after the login so I don't need to write again).

The table has 10 columns. I want to export the table as a CSV, but only with 3 of the columns. However, no matter which command I use, the darn thing writes everything into CSV-column "A".

So everything ends up in one column (but several rows). As shown in the commands, I actually only want to export "userid", "name", and "description". The description could contain special characters like ",+.&/" .

I would also export everything and then delete the unnecessary columns. But as mentioned, it writes everything into one column in the CSV. Into column A.

What I have tried so far:

db2 "export to users.csv of del select userid,name,description from users"

db2 "export to users.csv of del modified by coldel, select userid,name,description from users"
db2 "export to users.csv of del modified by coldel\t select userid,name,description from users"
db2 "export to users.csv of del modified by NOCHARDEL select userid,name,description from users"

also used "call" commands like

db2 "CALL SYSPROC.ADMIN_CMD('EXPORT TO users.csv OF DEL MODIFIED BY COLDEL, SELECT userid,name,description FROM users')"

and I also tried to export to users.txt and to awk,sed and cut. But no. It doesn't work, and I don't know why.

db2 -x "select userid,name,description from users" > users.txt

and then

awk '{print $1 "," $2 "," $3}' users.txt > users.csv
or
sed 's/\t/,/g' users.txt > users.csv
or
cut -d ' ' -f 1,2,3 users.txt | tr ' ' ',' > users.csv

Why is the system doing this?

Edit

I want each row of the database table to be written into its own Excel (CSV)-row. This works. However, I want the database table column 'userid' to go into CSV column 'A', 'Name' into 'B', and 'Description' into 'C', but this doesn't work.

The results are all written into CSV column 'A'. So 'B' and 'C' remain empty. Each DBtable-column schould get its own CSV-column

Databasetable: columns are "userid","name" and "description"
e.g. results: "UserID" "Name" "Description"

000001 "Doe, John" "Adminuser, Superadmin"
000002 "Doe, Thomas" "Adminuser, Moderator"
000003 "Doe, Karen" "Customer/Premiumcustomer"
000004 "Bot, Bot" "Bot +Hidden"

Like I said, actually all results has its own row but is only in one CSV-column. "A"
Actually CSV looks like e.g
A1=00001~"Doe, John"~"Adminuser, Superadmin"
A2=00002~"Doe, Thomas"~"Adminuser, Moderator"
A3=00003~"Doe, Karen"~"Customer/Premiumcustomer"
A4=00004~"Bot, Bot"~"Bot +Hidden"

B and C are empty.

• csv
• console
• db2
• aix

Share Improve this question Follow edited Jan 25 at 16:12 halfer 20.4k1919 gold badges109109 silver badges202202 bronze badges asked Jan 17 at 16:29 Hakuna MatataHakuna Matata 1344 bronze badges 1

• Why you don't use Ant? It's quite flexible and has a <sql> task that generates CSV, for any simple/complex query as needed. It runs in command line, of course. – The Impaler Commented Jan 21 at 21:17

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Your question does not explain how exactly you determine that "writes everything into CSV-column "A"." (assuming you are using the export command).

In particular, if you are reading the exported CSV file into a spreadsheet program then you must tell the spreadsheet program (or whatever is reading the CSV) to use the exact same column-delimiter that you used on export. If you fail to do that, then the spreadsheet will assume the wrong delimiter and therefore screw up - but this is because you are using the tool incorrectly.

When working with the shell for data exporting, you should use the db2 export command , rather than redirect the output of a select query which is shown in your question. Redirection is the wrong tool here. It will not give the desired result.

If any of the columns might contain the default column-delimiter (comma) then you must use modified by coldel... on the export command line (that is to say, you cannot use the default column delimiter).

Use one of the following if you are running with a full db2-client or if you are working locally on the Db2-LUW server. First verify that the specified coldel character does NOT appear in any of the columns (use a query), in the examples below I show a caret (0x5e) or a tilde (0x7e).

db2 export to users.csv of del modified by coldel0x5e select userid, name,description from users

or

db2 export to users.csv of del modified by coldel0x7e select userid, name,description from users

The export command will create separate columns, delimited by the specified coldel value either the caret or the tilde whichever works for you . Notice that I specify the delimiter in hex , this is deliberate, to avoid shell problems , as some shells can assign special meaning to these symbols which you avoid by specifying the hex code instead of the raw character.

Note however that when working with the shell like this, the Db2 client tool (in this case the clp) will check whether any codepage conversion needs to happen. That means you need to know the database codepage, and you need to know the value of the shell $LANG. If codepage conversion can happen, it can give unintended results, so be absolutely sure to prevent such conversions where possible by setting LANG appropriately before the export (or before any subsequent import or load into any other Db2 database).

Also, when using db2 export in shell scripting, take care to ensure that the output files get erased (if they exist) before running the export. Also, the script must carefully check the exit-code returned by the clp (0=success 1=no rows found 2 = warning >3 = error ) and respond accordingly.

After a lot of tinkering, I finally figured out the solution myself.

There are two options:

Option 1: Save the file as a TXT instead of CSV. Then, open Excel (just Excel, not the file itself) ... and in Excel go to File -> Open and open the TXT file. There, you can specify a desired delimiter, and it will split the content into three columns.

Option 2: This is even simpler:
db2 "export to users.csv of del modified by coldel0x3b select userid, name, description from users"
This way, everything is done automatically. It directly writes the data into columns "A," "B," and "C."