View Full Version : Transfer an Excel spreadsheet to the AS/400
01-01-1995, 02:00 AM
I have an EXCEL spreadsheet that was e-mailed to me with data I would like to upload to the AS/400. I have downloaded some dummy file records to the PC in order to get the .FDF file for uploading. I used a .CSV extension on the download. When I try to go after the spreadsheet and upload it to this file the file transfers but it is nothing but gibberish on the 400. I am using EXCEL 97 and Client Access express. Thanks for any help. Pat
01-23-2001, 11:50 AM
Pat - I do basically the same but I save my Excel as a .prn file to bring it to the AS/400. I also go into the Excel spreadsheet and make sure any numberic columns are 1 bigger than the field in the file, this allows for the number to be signed (ie. the database field is 9,0 and I make the column width in Excel 10). I'm sure you will get a few suggestions on this topic and it's been discussed before so you may want to use the search function on the forum.
01-23-2001, 12:05 PM
Here are the steps I use: <pre> 1)Save spreadsheet as .CSV to a shared folder. 2) On 400, create PF with field for each column (width each = max) i.e: A R FORMAT A FLD01 40 ALWNULL A FLD02 40 ALWNULL 3) Copy to PF: CPYFRMIMPF FROMSTMF('QDLS/folder/file.csv') TOFILE(lib/file) MBROPT(*ADD) RCDDLM(*LF) ERRRCDOPT(*REPLACE) </pre> A program will then have to convert the necessary fields to numeric, etc..
01-23-2001, 12:28 PM
Pat, I wonder if this is the reverse of the "CCSID=65535" problem. Check the file transfer properties and make sure that the "Convert CCSID" box is checked. Bill
01-23-2001, 01:53 PM
1. Use FTP to send the file from PC to your own library in the AS/400 2. If you have got the matching PF on the AS/400, change the DDS source to add the keyword ALWNULL (allow null) to all fields. 3. CPYFRMIMPF FROMFILE(the input file in your library) TOFILE(the physical file) MBROPT(*REPLACE)
01-23-2001, 04:05 PM
Try using the wizard in Client Access Data Transfer; I've found it to work well: a) Remove any column heading lines from your spreadsheet. Otherwise, all columns may be interpreted as text. b) Save As one of the following types listed by Excel 2000: Excel 4.0 limit of 16K rows Microsoft Excel 5.0/95 Workbook (*.xls) Microsoft Excel 97-2000 & 5.0/95 Workbook (*.xls) If you save it as native Excel 2000, the wizard won't work. Saving As CSV may remove leading zeros if reopened in Excel. c) Start a Client Access data transfer (click the Send button on a session) d) Select Tools | Create AS/400 Database File to start the wizard. e) Follow the wizard's steps. It will create the .FDF for you and return to the File Transfer box. f) Transfer the file.
01-24-2001, 04:34 AM
At the risk of belaboring the obvious....... When you downloaded the PF to the .csv did you retain the field names in row 1 for the upload? Is there any chance that you let Excel save the spreadsheet in its own format rather than forcing it to 'save as' a csv? It won't matter that the file extension is .csv if it's actually saved in Excel format. If you view the file in WordPad do you see raw data with comma separators? Are your numerics correct but your alphanumerics scrambled? If so I would ensure that 'Convert CCSID 65535' is checked on your CA xfer. Tom
01-24-2001, 05:56 AM
Thank you all for your help. I got it to work by creating the download file as a BIFF5 file. Then I saved the excel spreadsheet as an excel 95 worksheet and brought it up to the original file. Thanks again. Pat
01-24-2001, 06:35 AM
Hopefully you are working on a sane project where this isn't a factor but if you get more than 16 thousand and something rows in the spreadsheet the BIFF5 transfer will blow up. The .csv will still work with very large numbers of rows.
02-17-2001, 11:42 AM
Ken, I just wanted to let you know that the Client Access method works really well. Also, if the file already exists on the 400, a quick way to create the FDF is to download the file once - that will create an FDF for you.
06-20-2001, 03:14 PM
I am trying to unload an Excel file (saved as a type Excel 5.0/95 Workbook) to the 400 (V4R5) using the Data Transfer option in Client Access (V4R4 Mod Lvl 0 Service Level SF63638, Program Number: 5769-XE1). I am attempting to create the file description using the wizard ( with the File type = BIFF5(MS Excel 5)). If I scan the file using the first row for field names, I get the message "The scan operation has determined that the first row has a field with data which is not a valid field name. Uncheck First row of data contains field names' and re-scan the data." After doing this, I then get the message, "The scan operation found a column data mismatch at (row 2, column 1)." I've also tried the same process with the column headings deleted, but get the same results. I can not find any data formatted differently. The work sheet contains less than 10,000 rows. I've done this successfully in the past, but have not been able to get this to work recently. Does anyone have any ideas why this is NOT working? If at all possible, I do not want to unload as a text file and parse out on the 400. Other folks here have tried creating the DDS on the 400 and then unloading to it, but have had inconsistent luck with that also.
06-03-2004, 12:10 PM
Does anybody know how to solve this issue, I am trying to upload excel and receiving the same message. I have to go back for each row,column and format the column but I cant get it to work. Any help will be appreciated. Thanks, Tiki
12-22-2004, 10:22 AM
I hope someone has the answers to the problems of uploading an excel file to the as/400. I have been trying for several days to get an upload, but I keep getting errors. I've changed the format of the cells so many times that I have forgotten what the original format of the cells were. I have tried the wizard, removing the row 1 field names, changing individual cell formats, changing all the cell formats for a column, fixing column data mismatches, etc. etc.
12-22-2004, 11:13 AM
This works well for me: I create a dummy record in the database so I have something to download, then use client access transfer to download to my pc as an excel spreadsheet. Save the def file when you do that. Then copy from your spreadsheet and paste into the one that downloaded, then upload using the def file the downloaded created.
mark b. flanigan
01-02-2006, 02:19 PM
1) get all my excell cells the length and type you want. 2) save the excel spreadsheet as formatted text(.prn) on the pc. 3) using client access download to the 400 the (.prn) - unclick the use .fdf box. 4) set the length of the record to the appropriate size to be downloaded. transfer the data and this will create the file on the 400. 5) when you query the file or dsppfm the fist 12 bytes will be the source line numbers. within your rpg program you will need to rename the format since it will have the same name as the file. it's done a little differently depending if its rpg or rpgle. you will need to pars out you fields so to be certain you are getting the data you intend to be getting. have a co-worker who does the same procee excepts uses .cvs. this always hoses me up since it never failed that the user had a comma where is should not have been.  drop me an e-mail a email@example.com and i'll be happy to forward you a quick example i often use. hope this helps... mbf
Powered by vBulletin® Version 4.1.5 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.