Uploading EXCEL workbooks to AS/400 via Client Access

[Guest.Visitor]

I have experienced similar issues. The help doc's DO NOT "help". Here is what I think you should do. 1. "clean up" the worksheets. in other words, strip out all the "pretty and fancy stuff". Get the worksheet down to just data. (You could try uploading the file "as is" but I do not think it will work.) 2. Instead of just running the "upload", you must first use the "wizard" to create the data object definition on the iseries and then upload the file. (this wizard is located under the TOOLS pulldown menu). Just follow the steps and it will allow you to label the fields, change the attributes and THEN it will direct you to upload the file. This is the only method I have found that works. hth

[S.Mildenberger]

I do the following, File, Save As to save the file in .csv format in the IFS Create a database file to receive the data CPYFRMIMPF to copy the IFS file to the database file. Scott Mildenberger

[Guest.Visitor]

Scott, Do you use DDS to create the database file? Or if not, what record length? And what do you do with all of the "fancy" stuff, such as explanatory text ("The next prices are for Deaters only" or whatever)? Also, since Excel has numbers in edited format, what do you do with those columns? Thanks in advance... Al

[andreas.goering@web.de]

Al, we have developed a tool "iGetExcel" which allows you to convert Excel files native -without any PC- to any AS400 database. Also on-the-fly creation of new files is possible. Have a look at www.goering.us for an flash-animated demo. Regards Andreas Goering GOERING iSeries Solutions

[S.Mildenberger]

Yes, I use DDS to create the file. I just delete all the headings and extra text. I believe the edited numbers come across fine, if they didn't just change the column formatting. Scott Mildenberger

[Guest.Visitor]

Scott, Do you use DDS to create the database file? Or if not, what record length? And what do you do with all of the "fancy" stuff, such as explanatory text ("The next prices are for Deaters only" or whatever)? Also, since Excel has numbers in edited format, what do you do with those columns? Thanks in advance... Al

[Guest.Visitor]

Scott, If you have a column that represents dollars and cents it may look like $123.45- in Excel. Do you define that column as alpha, or signed numeric or what? If you call it numeric, does that mean that all of the editing gets stripped off? Which is of course I would love to see! Thanks for the help... Al

[S.Mildenberger]

I would just change the column to numeric in Excel so you just get the number value when you export it. Scott Mildenberger

[David Abramowitz]

One solution would be to create a macro, that first copies the selected range of cells to a new workbook. The macro can then use the SHELL statement to upload the data via file transfer. What you have to do is first predetermine the range that will be uploaded and manually create the new workbook, so that you then may create the .DTT. Dave

[Guest.Visitor]

A vendor has their price list available on EXCEL. The worksheets are "fancy", ie, have cute headings and so on. I am having no luck uploading this stuff to the /400 using Client Access. I have XP professional, CA Express, V5R1 on the /400. I tell the CA transfer utility to consider the input file the latest Excel format. Depending on other options in the transfer I get various messages, such as "no data" in the input file, or "the workbook has several sheets" and do I want to transfer all of them (obviously I say yes, but have trid o as well). I never get any data transferred. I DO NOT use any PC file definition file, nor do I have DDS on the /400, since I had a vague notion that Excel to /400 was a "gimme". Guess not, huh? Any ideas, I can't be the first to want to do this, but I'm out of ideas. Thanks in advance, Al Harkabus

[Guest.Visitor]

This is what I have been doing and has worked fine for me. First, I remove headers and extras and leave only the data columns I need, then I save the file with extension .prn Once saved, I can use Client Access "Data Transfer to AS/400" or ftp. FTP moves the data with no extra columns. Thanks.