upload from excel to as400

[David Abramowitz]

AFAIK, the CA Add-in is for downloads only. To perform an upload from within excel, I first create the upload transfer files (DTT and FDF) by doing a manual upload. I then create an Excel macro using the SHELL statement that runs the RFROMPCB command. Dave

[B.Wahl]

upload from excel to as400

David, Have you tried to save the sheet as a .csv on the IFS? You could then run the cpyfrmimpf to a file on the 400.

[David Abramowitz]

upload from excel to as400

I like my way better, as it is done in one step (from the user perspective). The user merely clicks the button assigned to the macro, and that's it! No other saves needed. This particular macro does many other things, some of them pretty slick: Using the SHELL statement to run RMTCMD which in turn executes an RPG program, all from within EXCEL. The only problem with SHELL, is that if you are running multiple commands, you first have to create a batch file (DOS .BAT). This is because the commands run asynchronously. That is the second command may start before the first is finished! Creating a batch file avoids this problem. BTW, didja know, you could embed a 5250 session within a worksheet via OLE? Many thanks (again) to B. Singleton for his assistance with SHELL. Dave

[rv_ooty@hotmail.com]

upload from excel to as400

If you have CAE express use the data transfer utility in menu. If you know VB then you have n number of options to do it. So you can find the efficient and fastest way. For example in VB coding... [expertise required - Windows API programming] create ADODB connection and ADODB command. Issue a select statement on the database file to upload and get the field information. Parse the Excel file field by field and create a text file in temp folder. This txt file would be like below. if numeric field, pad zeros in left to actual size of field. if alphanumeric, pad space in right to make actual size of field. Then create ftp connection to server issuing the internet connection API found in wininet.dll. Then issue ftpputfile api with this formed text file with the target database file. For example, a table [PF] with 126 fields of average 30 - 50 characters length and around 59,000 records took 1 hour and 20 minutes in conventional methods to upload. The above method which i tell takes 80 to 85 seconds totally. Good luck RV

[captainbob091949]

upload from excel to as400

The Uploader: A spoonful of Sugar by David Mayle. I down loaded the full setup witout code for people who don't have VB. Then ran the setup. Setup the ODBC DATA SOURCE NAME (dsn) MYAS400 on my PC that points to our AS/400. Then tried to run Project1 and received the following error: Run time error 76: Path not found. What may be wrong? TheUploader.exe is in folder C:Program FilesProject1

[Guest.Visitor]

upload from excel to as400

This was corrected in later versions of iSeries Access Data Transfer (V5R3 and up) through the addition of a "allow numeric data in character columns to be converted to character data". The checkbox should be checked to prevent error CWBTF0005 for numeric data in an alpha field. See http://www-912.ibm.com/s_dir/slkbase...a?OpenDocument IBM states that the option is located on the conversions tab under the file drop down menu in the data transfer session. In the Microsoft Excel "iSeries Access Data Transfer" add-in, I found the checkbox in the Properties section, under the options tab.

[Andy26]

upload from excel to as400

David, Usually I see this type of error when trying to upload character data to a numeric field. Is the field in the file numeric? If so would it be possible to change it to character? If you can't, then you could have an intermediary file that you upload to and then have a program to read that file parse out the data to the final file destination in the format that you need. Terry Anderson

[nshah400]

upload from excel to as400

David C., Have you tried rekeying the zipcode in the cell where it stops? I have had to do that as part of my trial and error exercise while uploading an Excel file where the column was redefined as text-only after the data was entered. for a new transfer process, CAE also scans the file to show any potential problems. Hope this helps..... Naresh shah

[TBarstow]

upload from excel to as400

Try building the AS400 file first, with this field defined as char. Then be sure to add "allow numeric data in character columns" option.

[dcrocker@handhtech.com]

upload from excel to as400

I have an excel spreadsheet that needs to be uploaded to the AS/400. One of the columns in the spreadsheet is zip code. Users have entered in the zip code and some rows have zip code + 4 entered. They have entered an "-" in the zipcode. ex 23233-1234. When uploading the excel spreadsheet using client access transfer data add in, the transfer always ends with CWBTF0005 (field is incorrect or does not match the PC data type). I am using client access express with the lates service pack entered. I am uploading to iSeries with V5R1M0 installed. I have changed all the cells in the column to be format text. However, the cell that it ends on only contains numeric data. Does anyone know what is happening. Thanks for your help, David

[Guest.Visitor]

upload from excel to as400

There have been many better and certainly more elegant solutions provided, but I thought I would add my 2 cents. I had a similar problem years ago and came up with a low tech, dumbed down solution. I determined that the upload tool must be mistaking the zip code column as numeric because most of the data in the column was numeric. I also assumed it made a guess as to the data type of the column based on the first couple of rows and not the entire column. To convince the upload tool that I wanted the zip code column to be alpha and not numeric I inserted a row just below the column headings and added dummy data to each cell in that row. I called this the dummy row. If I wanted a column to be alpha I would enter a string of "A"s in that column of the dummy row. If I wanted a column to be numeric I would enter a string of "9"s in that column of the dummy row. For example in the first row (the dummy row) in a Member Name column I would enter "AAAAAAAA". In the first row in a Zip Code column I would also enter "AAAAAAAAA". In the first row in a numeric column such as Membership Dues, I would enter "9999.99". I never saw data type or conversion errors again. Although, I would occasionally have to go back to the table on the AS400 and delete the dummy row. (Most of the time I could just ignore it.)