CODEWITHSUNDEEP
db2export-to-csvdb2-luw
Abruzzo Forte e Gentile
Abruzzo Forte e Gentile

Reputation: 14869

exporting to csv from db2 with no delimiter

I need to export content of a db2 table to CSV file. I read that nochardel would prevent to have the separator between each data but that is not happening.

Suppose I have a table

  MY_TABLE
  -----------------------
  Field_A   varchar(10)
  Field_B   varchar(10)
  Field_A   varchar(10)

I am using this command

  export to myfile.csv of del modified by nochardel select * from MY_TABLE

I get this written into the myfile.csv

 data1    ,data2    ,data3

but I would like no ',' separator like below

 data1    data2    data3

Is there a way to do that?

Upvotes: 0

Views: 21756

Answers (3)

ckg
ckg

Reputation: 2380

Use "of asc" instead of "of del". Then you can specify the fixed column locations instead of delimiting.

Upvotes: 0

Seb.B.
Seb.B.

Reputation: 151

Yes there is another way of doing this. I always do this:

  1. Put select statement into a file (input.sql):

    select 
    cast(col1 as char(20)),
    cast(col2 as char(10)), 
    cast(col3 as char(30));

  2. Call db2 clp like this:

    db2 -x -tf input.sql -r result.txt

This will work for you, because you need to cast varchar to char. Like Ian said, casting numbers or other data types to char might bring unexpected results.

PS: I think Ian points right on the difference between CSV and fixed-length format ;-)

Upvotes: 0

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

You're asking how to eliminate the comma (,) in a comma separated values file? :-)

NOCHARDEL tells DB2 not to surround character-fields (CHAR and VARCHAR fields) with a character-field-delimiter (default is the double quote " character).

Anyway, when exporting from DB2 using the delimited format, you have to have some kind of column delimiter. There isn't a NOCOLDEL option for delimited files.

The EXPORT utility can't write fixed-length (positional) records - you would have to do this by either:

  • Writing a program yourself,
  • Using a separate utility (IBM sells the High Performance Unload utility)
  • Writing an SQL statement that concatenates the individual columns into a single string:

Here's an example for the last option:

export to file.del 
    of del
    modified by nochardel
    select 
       cast(col1 as char(20))   ||
       cast(intcol as char(10)) || 
       cast(deccol as char(30));

This last option can be a pain since DB2 doesn't have an sprintf() function to help format strings nicely.

Upvotes: 4

Related Questions