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.

 

[Pekka]

Rapeek,
there are really many ways to skint the cat. I hope you have Ms Access.
Use CA ODBC to link to your output table in your iSeries.
Make something similar in Access:
Function sends()
DoCmd.SetWarnings False
On Error GoTo sends_Err

DoCmd.SendObject acTable, "your linked db2 table", "MicrosoftExcel(*.xls)", _
"pekka@somewhere_in.fi", "", "", _
"message object!", "Hey, this is new stuf....", False, ""
DoCmd.Quit acSave

sends_Exit:
Exit Function

sends_Err:
MsgBox Error$
Resume sends_Exit

End Function
Make autoexec macro in your access app. to execute this macro.
RunCode
Finction name: Sends()
Define in your NT scheduler the day and time when to run this access application.







Pekka

"every dog will have his day"

 

[thelearner]

Hi Pekka,

This looks very interesting but I have no idea where to start. If you don't mind to tell me step by step. Also, is my file should be in comma delimited file or data base file?

Thank in advance.

 

[Pekka]

Hi,
in this access odbc example the file could be a nativive db2 table.
1. Create new access 'database'
2. in access. File->Get external data->link tables. in link dialog select types of files: Odbc drivers. (yep, you have to define it). Select yor dns. Select your table. The link is defined.
3. In access, select modules tab. Select new. Copy paste that function. Define your table, your recipientetc etc.

3 create autoexec macro to execute this.
4. In NT(XP whatever) go to scheduler and and new task with proper properties to run it.
I hope that you have somebody in your shop who is familiar whit these task.

But the point is that the linked table is iSeries physical file and you dont have to take care of that editing stuff so much and you can output it to pure excel file and even e-mail it automatically!

Pekka

"every dog will have his day"

 

[thelearner]

Thank you so much Pekka, I will give it a try.

 

[Mercury2]

Hi,

I met this problem which is coming from Excel so don't directly open Excel and double-click on the spreadsheet first instead.
I know that's sound weird but you will see that the results are different between the 2 methods and 0401 will remain 0401.

Just my 2 cents. :-D

Nab

 

[thelearner]

Hi Nabuchodonosor,

Do you mean double click on the file? That did not work.

 

[Mercury2]

I don't understand why it doesn't work !
From iSeriesNav drag the csv file to the Windows desktop, double click to open. When you're done, drag it back to the IFS folder.
HTH (this time) :-D

 

[Mercury2]

Here it works like a charm on my desktop.

 

[thelearner]

still not working,Nabuchodonosor.

 

[Mercury2]

I believe we have to start from the beginning.
I want to open with Excel a csv file containing text fields that contain numeric data and sometimes have 0 in the front. I want to keep leading 0's in these fields, e.g. 0401 must remain 0401 and not become 401.

Here is what I do.

1/ (I've heard that IBM won't support the QDLS folder in the next releases, hence I don't use CPYTOPCD anymore) This first step creates the csv file and copy it in my IFS folder (Root) from QGPL/MYFILE :
CPYTOIMPF FROMFILE(QGPL/MYFILE) TOSTMF('/home/USER/TMP/MyFile.csv') MBROPT(*ADD) STMFCODPAG(*PCASCII) RCDDLM(*CRLF) STRDLM(*NONE) FLDDLM(',')

2/ From OpsNav/iSeriesNav, drag the csv file from USER/TMP/MyFile.csv to Windows desktop.

3/ Double click on the desktop csv file to open. Excel opens the csv file. Now you can see that the leading zeros remain.
e.g.
99,Some text,2003-12-24,C,23079 ,057,3003989192 ,00000000,...
4/ Do what you have to do on the spreadsheet and if necessary when done, drag it back to the IFS folder.

HTH this time