Export to a Fixed Length File

[troyu]

Hi - I am trying to export to a fixed-length file, and I am almost there. I get the file in fixed-length format, however I have double quotes on the far left and far right of the file now. The output looks like this:

"312 My Store 12112"
"991 Your Store 11200"

My statement is as follows:
export to C:\data.txt of DEL MODIFIED BY CHARDEL"" select cast(storenum as char(6)) || cast(desc as char(55)) || cast(zipcode as char(55)) from TableA;

I tried with a single double quote, but I receive an error message when attempting this.

 

[gregsimpson]

Troyu,

possibly your using the wrong file type here. You are using DEL which is a delimited ASCII file. Your SQL statement has you concatenating all the fields together, but he delimiter is still "". As far as DB2 is concerned you have asked for all these columns to be returned as 1 field, which it has done. As the delimiter is ", it has wrapped " around the result.

I suspect instead of using type DEL, you wish to use a type of ASC, which is a non delimited ASCII file, sometimes referred to as a fixed length ASCII file.

These files consist of a stream of ASCII characters of data organized by row and column. Rows in the data stream are separated by carriage return line feed or new line character, and all column values are of fixed length. All variable length characters are padded with blanks and represented using their maximum length. There are no column or character delimiters.

This seems to satisfy what you require from this post and your previous posr regards new line characters etc.


This link explain a little more

http://www.cs.yorku.ca/~seela/db2/import.html

Cheers
Greg

 

[troyu]

Hello Greg.
I have looked into exporting to a ASC file type. It looks like this file type is not supported to be exported to, however you can import using ASC.

I tried using this file type in my statement, and received error messages. The DEL option is very close to what I would like, however just includes the quotation marks at the start and end of each row. Is there a switch of some sort to eliminate these quotation marks at the start and end of each row?

 

[BrianTyler]

Troyu,

You could use a simple select statement with the -x and -z options to suppress the column headings and save the results to a file.

Regards

Brian

 

[BrianTyler]

Troyu,

To save you fighting the IBM documentation:

db2 -x +o -zdata.txt "select cast(storenum as char(6)) || cast(desc as char(55)) || cast(zipcode as char(55)) from TableA"


Brian