Need Help w/Automating Excel File Upload for Report Generation

[NYCountryGal]

Okay I've been all over Google and Code400 forum to get my answer and either I'm not searching the correct way or I'm just simply an idiot.

Here's what I would like to do:

Once a week we receive an Excel spreadsheet from our client with orders that have shipped the previous week. The client won't send in csv format, they won't FTP to a site - it will be emailed as a spreadsheet. I want to have the user who receives the email - drop the excel spreadsheet into a folder on the IFS and then the excel data is automatically loaded into the 400 file that I have defined to match the fields in the excel file, and then submit the report to run that produces two different excel reports from the data received and other data we have on our box.

Here's what I have accomplished:

1) Using Scott Klement's programs - I've created a journal receiver to watch the folder and that part works fine. The part that isn't working is the automated upload of the excel data to the 400. Do I need to use CPYFRMIMPF or CPYFRMSTMF? I'm using CPYFRMSTMF and it isn't working (I did save the excel as a csv file in the IFS folder but that didn't resolve the issue).

2) I have the report programs all done and I create the needed excel reports and they email perfectly.

I can't send you a copy of the excel data - company rules because its client data, etc, etc, etc. But I am providing the file layout, and the program code so you can see what I am doing, and the error message I am receiving and hopefully point out my mistakes.

Thanks in advance!
Roxann

[FaStOnE]

Re: Need Help w/Automating Excel File Upload for Report Generation

Pssssst ... Believe it or not, the GoAnywhere Director you're running a test drive on will do this for you without blinking an eye!

I can send you some Script that will do it for you.

Let me know!

[NYCountryGal]

Re: Need Help w/Automating Excel File Upload for Report Generation

Rick - I know, I know. I have to do it the "hard" way first so that when I do this in Go Anywhere I have a measurement of how much time the tool will save the programmers when its purchased. Under the new direction the company is taking -- we have to prove out the need and savings in order to justify the purchase. Believe me - I'm irritated but if this is what it takes to get it... I'll gladly bloody my forehead.

Roxann

[FaStOnE]

Re: Need Help w/Automating Excel File Upload for Report Generation

Wish I could help with the other .. but, just not sure. I can help if you need the PoC though...

Good luck!

[tomholden]

Re: Need Help w/Automating Excel File Upload for Report Generation

ok first off...CPYFRMIMPF/CPYFRMSTMF will never work for this. if it's a excel 2007 & higher & if it's saved as an xlsx then you could simply process it from the IFS as a stream file (worth noting you'd still have a heckuva time getting this to work with CPYxxxF) regardless of what you do there will need to be some serious coding for this. if it's a .xls ("native" xls format) then you're going to have to use something like Scott's HSSF stuff that integrates with the POI java library (or perhaps there's something in PHP?). if it's a .xlsx then you can use the IFS APIs to read directly from the IFS and parse out the data and write it into your externally defined file. in other words lots of plumbing to be done...

[MichaelCatalani]

Re: Need Help w/Automating Excel File Upload for Report Generation

Instead of CPYFRMSTMF, use the CPYFRMIMPF command.

Also, attempt to do a DSPF STMF('/AB/ordrvw/ordrvw.csv') to make sure you can see the contents.

[GLS400]

Re: Need Help w/Automating Excel File Upload for Report Generation

Hi Roxann:
I'm not sure if this will help but It's at least worth looking at:

http://www.code400.com/forum/showthread.php/3937-Excel-VBA-to-extract-iSeries-data?highlight=excel

Look at DeadManWalks post

Best of Luck
GLS

[tomholden]

Re: Need Help w/Automating Excel File Upload for Report Generation

Instead of CPYFRMSTMF, use the CPYFRMIMPF command.

Also, attempt to do a DSPF STMF('/AB/ordrvw/ordrvw.csv') to make sure you can see the contents.

that would be too easy...her clients won't send csv files only excel

[MichaelCatalani]

Re: Need Help w/Automating Excel File Upload for Report Generation

that would be too easy...her clients won't send csv files only excel

ahh, I see that now.....damned clients

This is why they developed XML......

[tomholden]

Re: Need Help w/Automating Excel File Upload for Report Generation

ahh, I see that now.....damned clients

This is why they developed XML......

no worries...the clients will refuse to send XML or CSV so we can all stay in the same boat we're in

[DAG0000]

Re: Need Help w/Automating Excel File Upload for Report Generation

Since your manually receiving the file, just have your user who is receiving the file convert it to tab delimited before they put it in the folder. You can then use CPYFRMIMPF.

[NYCountryGal]

Re: Need Help w/Automating Excel File Upload for Report Generation

@Tommy - I was afraid someone would tell me this.

@Michael - I just tried that (the DSPF ...) and the beginning of the data there is garbly gook and at the end of the file there is garbly gook -- in between is the data. I've attached a picture to show you...

@GLS - LOL - I have several examples of this .... he was a co-worker for the last three years - up until March of this year. He did things the way Tommy suggested - not that I can't do that - I just thought I had read that there was a way to bring in an Excel file to a 400 file a different way....

@DAG0000 - I will try that this afternoon and see what my results are. My user is trainable ....

@Rick - Go Anywhere is looking better and better....expect a call soon!

Thanks for the help everyone -- I'll post my results soon!

[tomholden]

Re: Need Help w/Automating Excel File Upload for Report Generation

DAG0000's answer is the easiest if your users do it correctly every time.

[NYCountryGal]

Re: Need Help w/Automating Excel File Upload for Report Generation

@Tommy - My HOPE is that this won't be the method that we will be using. Right now I am having to go through this exercise to show how long it takes the programmer to code a process this way. Then I get to evaluate Go Anywhere from Linoma Software on how long it takes to do the same process. Rick has been telling me it takes far less time and is way more efficient. Since we do A LOT of reports for our clients and we receive data from our clients in a multitude of ways - everything I've learned thus far - Go Anywhere is the best way to go but -- per management - I've got to prove it to them before they agree to purchase it.... Lucky me!

@DAG0000 - Okay... I saved it as a TAB delimited file, I can do a DSPF STMF and I can see the data (no garbly gook), when I do the CPYFRMIMPF (code is below) its not copying because of error code 11 (see below) - what am I missing? I checked Google but I don't find anything that answers my question...

Here's what I used as the CPYFRM statement:CPYFRMIMPF FROMSTMF('/AB/ORDRVW/ordreview.txt') TOFILE(RP0033P01) RCDDLM(
*EOR) DTAFMT(*DLM) STRDLM(*NONE) FLDDLM(' ')

Here's the definition of error code 11:11 - The RCDDLM parameter for a stream file can only be *CR, *CRLF, *LF, or *LFCR and for a data base file the RCDDLM parameter can be *EOR or a valid value.

Here's a look see at the data in the file:
Shhh - not suppose to show you....

[NYCountryGal]

Re: Need Help w/Automating Excel File Upload for Report Generation

One thing I forgot to ask .. the error says the RCDDLM can only be one of the specified values - but what if my file doesn't contain one? Does that mean I need to create a file field definition like it talks about in the IBM data center? And if I have to create - can I use the source file from my file I'm copying it to?