create excel file from AS400

[thelearner]

Hi,

I have to create a report and send to client in excel file as a weekly basis. What I did is create comma delimited file (.CSV), copy to folder (CPYTOPCD), then use excel to open it. The problem is I have some text fields that contain all numeric data and sometime it has 0 in the front, like '0401'. When I opened in excel, it becomes 401.
How can I keep '0' in my field.

Thank in advance.

 

[RedMage1967]

Have you tried using the Client Access plug in that allows you to export a PF directly into an excel spreadsheet?

RedMage1967
IBM Certifed - RPG IV Progammer

 

[thelearner]

Hi RedMage1967,

No, never try that. Can it be set up to do this automatically, without any manual work at all? If yes, how?

Thanks

 

[as400pro]

Simplify AS/400--EXCEL File Transfer with CA Tools.

If you run Client Access V3R1M3 or higher to access the AS/400, you can use a new Excel toolbar icon included as an add-in in Client Access to directly retrieve AS/400 file data into an Excel spreadsheet.
To enable the icon, click "Tools" and then "Add-Ins," and then check "Transfer Data" from an AS/400 checkbox. The new icon should appear just below the file drop-down menu.
When you click the icon, a simple wizard steps you through selecting the file and fields you want to transfer from the AS/400 to Excel.
This new icon is a nifty addition to Client Access because it removes a few steps from the old transfer process.
You can find the add-in under C:\Program Files\IBM\Client Access\Shared\cwbtfxla.xll.

From my website:

http://as400pro.com/TipsCA.htm#9

T. Bishop

http://www.as400pro.com

 

[thelearner]

Hi as400pro,

What I meant my automatic is after the first set up then on the next run time I don't have to get involve at all. Since this is a weekly job and there will be more like this. I can't afford to do those few clicks every time we have file to send out. Beside what if I'm not in the office then our clients won't get the report.
On my original post, I mentioned that I creat CSV file and copy to pc folder and email out. All these are handle inside my RPG programs. Unfortunately it did not work for num. character.
Thanks

 

[flapeyre]

You could use %editw(number:'0 ') (that's four blanks after the zero) in your RPG program; that would make a 4-digit field with leading zeros. However, it would go the Excel spreadsheet as a character field; I'm not sure you want that.


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[thelearner]

Hi flapeyre,

This is a character field that contains number. If I want to try what your suggested, that means I have to convert my character field to numeric field and use fxn %EDITW?

 

[iSeriesCodePoet]

If csv isn't an option you can using the PIO project from Apache, you will have to write an RPG interface to the java classes, but once it is set up, you can create many different types of Excel reports. I have never used it, but I know someone who does. My perfrence if possible would be csv. If you double click on it, Excel will open it.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi SeriesCodePoet,

My problem with CSV is I have a charter field that contain numeric characters like '0401'. When I open with CSV, it show as '401'. You mentioned about Apache and Java. Let's say that I'm only know how to spell them!!

 

[iSeriesCodePoet]

Oh... is that all???

On every character field, put a double (&quot quote, and if that doesn't fix it, add in the double qoute a single quote so then your '0149' is either "0419" or "'0149". That should fix it.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi,
Ok, I tried %editw(number:'0 ') from flapeyre . By converting my char field to num field then used %editw fxn. Then my comma delimit string looked like this
" 401" with blank in the front.

For your suggestion, iSeriesCodePoet , is this mean I will see "" around 0149 when I open excel. If yes, then I can't use it.

Thanks

 

[flapeyre]

rapeek:

Are you sure there are four (4) blank spaces after the zero in the %TRIM function?


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[iSeriesCodePoet]

No, if you look at what Excell produces when you tell it to create a csv file, they have the "". Let us know if it works.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi,

This is how I tries with double quote

DATA = '"' + %CHAR(RLSDAT) + '",' +
'"' + TKFILR + '-' + %EDITC(TKENT#:'X') +
'-' + %EDITC(TKCKDG:'X') + '",' +
'""' + TKETCD + '"",' +
'"' + TKPENT + '",' +
'"' + MODE + '",' +
'"' + EMSPI + '",' +
'"' + OGA + '"';
fields TKETCD and TKPENT are the problem ones. I only tested with TKETCD first. It is a char. field with length of 2x.
It contains data like 01, 02, etc.
When I open with excel it show like this
02""
I also tries this
'"' + quote + TKETCD + '",' // quote = X'7D'
When I open excel it show
'02

to reply to flapeyre, why do you talk about %trim. You suggested %EDITW. I convert character field to digit field with %int then use %EDITW with my digits field. My result has 1 blanks (replace the leading zero)in the comma dilimited string.

 

[iSeriesCodePoet]

The only other thing I can think of is to put a space in front of the zero. That will fix it .

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

No, I already tried that. didn't work

 

[flapeyre]

How about %editcde(field:'X')? Try that.


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[MdnghtPgmr]

rapeek,

What you are seeing in Excel is because of Excel. No amount of formatting the CSV file will do the trick. You need to format the column from within Excel. I forget the exact "edit word" you need but it's in the help somewhere.

HTH,
MdnghtPgmr

 

[MdnghtPgmr]

Goto Format | Cells then pick Custom. Then click on a format and hit help. That sould get you more info.

HTH,
MdnghtPgmr

 

[thelearner]

Yes, flapeyre. I tried %editcde(field:'X').