Data Transfer AS400/Excel

[SuzieW]

I'm sure I read an article once in News/400 (or something) which explained how to get meaningful column headings when importing AS400 files into Excel. Something to do with ODBC I think. As far as I remember, you could actually replace the "A", "B", "C" column buttons in Excel with the field text. Did I dream this?

At the moment, I'm getting the DDS field names across the top which are not ideal for the end user.

If anyone has a link to something which might help, I would be grateful.

I've googled various combinations of things but so far come up with nothing...

 

[HitechUser]

SuzieW,

Correct me if I am wrong, but you can NOT change the column buttons in Excel(standard worksheet). These are used in the formuals etc. You may be able to do what you wish using a custom form and VBA. Otherwise the first row will have your column names. You could always freeze and protect this row..

or

The other way you can modify your row and column names is to hide them entirely and create your own system.


I hope this helps.

 

[SuzieW]

Hi HitechUser

I don't really mind whether the column headings replace the buttons or appear on the first row.

What's important to me is getting a meaningful column headings through rather than the internal DDS field name. The textual descriptions are available on the AS400, but I wondered how to get them through to the destination file when the Data Transfer is run.

 

[flapeyre]

If you do a DSPFFD to an outfile, the outfile will have the column headings in these three fields:

Column heading 1 WHCHD1 A 20
Column heading 2 WHCHD2 A 20
Column heading 3 WHCHD3 A 20

Write an RPG to to output these fields (comma and quote delimited) as the first record of your .csv file. Then just add the actual data to the .csv after that.

Feles mala! Cur cista non uteris? Stramentum novum in ea posui!

 

[gracy]

If you create the file with sql you can use the for column feature.

In the example below when the file is brought into excel the column heading is Finish_Wgt but in rpg programs I use the field zfwgt.

create table mylib/myfile
Finish_Wgt for column zfwgt dec (5,0) not null with default

 

[HitechUser]

SusieW,

Have a look here IBM. You can retrieve the column description using EXTCOLINFO.

Let us know..

 

[Mercury2]

SuzieW,

Have a look here, this may be of great interest to you.

 

[SuzieW]

Thanks all, I appreciate your help.

Sounds like there are a few ways of getting the results I need.