Exporting to text file of varied but fixed width columns

mrvinch · #1 (**permalink**) 05-19-11, 18:32

Hi all,

I am not familiar with DB2, I live in the land of php and mysql and have come here hoping some fine folk could suggest a solution to the following. I have been searching but either I'm not using the correct terminology or this task isn't one that cause others' grief, because I can't seen to get close to a solution.

I have a client that requires text file output of select data presented as a series of fixed width columns.

For example the csv version of the output would look something like this:

john,smith,23,staples,street

However I need to produce the output as

john smith 23 staples street

where the first column is a total of 10 characters wide and john consumes the first four and the remaining 6 are space characters.
where the second column is a total of 20 characters and smith consumes the first five and the remaining 15 are space characters.
etc
etc

The database is DB2 v8 on Windows Server 2008

I am hoping for a SELECT / EXPORT style query that will produce the output in one step, but I'd be equally happy with a .csv file manipulation solution.

Would anyone have any advice on how to achieve the fixed width output by any method?

Thanks in advance,
Nicholas

n_i · #2 (**permalink**) 05-19-11, 21:54

Code:

export to myfile.txt of del 
 modified by nochardel 
 select 
  cast('john' as char(10)) || 
  cast('smith' as char(20)) || 
  etc etc
 from mytable

mrvinch · #3 (**permalink**) 05-20-11, 11:13

Thank you.
I had suspected cast might be involved.
I'll put it to use immediately and see how I go.
Cheers,
Nicholas

Internet Services

Web Development

Digital Marketing

Consumer Tech