Upload Spreadsheet to AS400 using Excel addin - creates graphic fields for alpha fields
Home > IT Answers > AS/400 > Upload Spreadsheet to AS400 using Excel addin...
PaulClemens
25 pts.
0
Q:
Upload Spreadsheet to AS400 using Excel addin - creates graphic fields for alpha fields
AS/400, Transfer Data To iSeries, Excel 2007
I am using the Excel addin to transfer excel files to our AS400.  The file does not exist on the AS400 and when the transfer runs, the field created for the alpha fields is 'Graphic' when I display the file field definitions.  I cannot read these fields in my RPG program - they are only text data.  What do I have to do to get the fields defined as regular alpha fields.

Software/Hardware used:
AS400 V5R4 / Excell
ASKED: Oct 20 2009  7:21 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Teandy
3190 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Paul,
In order to cut down on the type of problems you describe, and to satisfy auditor requirements that ODBC be read only, here is how we handle this situation.

1.) Save the excel sheet to the IFS as a .CSV file.
2.) Create a holding file on the i5 with a file layout that matches the .CSV.
3.) Use the CPYFRMIMPF (Copy From Import File) command to pull the file from the IFS to the holding file.
4.) Process the holding file to move the data the file(s) that need it.

You could create a program that will do all of this for you and then give the user an option on a menu to upload the .CSV after they save it to the IFS.

If you want an example of how to do this, let me know and I will post something for you.

--------------------------------------------------------------------------------------------------------------------------------------------------------
Last Answered: Oct 21 2009  4:42 PM GMT by Teandy   3190 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

TomLiotta   7355 pts.  |   Oct 20 2009  10:38PM GMT

What is your QCCSID system value set to be?

Tom

 

PaulClemens   25 pts.  |   Oct 21 2009  12:28PM GMT

The system value is 65535.

 

TomLiotta   7355 pts.  |   Oct 21 2009  6:23PM GMT

Try changing the job as CHGJOB CCSID(37)

Use CCSID(37) for US English as well as various related language settings. To get a more certain value, DSPJOB OPTION( *DFNA ) and scroll down two or three screens to see what ‘Default coded character set identifier’ was chosen for the job. Use that value first. It’s likely that your default is 37.

If that helps, we probably know the things to do next. If it doesn’t help, please describe any differences that you can recognize.

Tom

 

PaulClemens   25 pts.  |   Oct 21 2009  7:05PM GMT

Thanks everyone for your help…ultimately I:
1. created the file using DDS on my AS400,
2. added some records to the file
3. downloaded the file to the pc using Client Access
4. I saved the FDF from the download
5. I started Excel and clicked the AS400 file transfer addin
6. I created a transfer to the AS400 using the FDF
7. I transferred the file to the AS400
All fields in the file were available to me.

 

TomLiotta   7355 pts.  |   Oct 22 2009  12:51AM GMT

Stay aware that the root problem still exists. This should work without those steps. However, until the system CCSID is set to a useful CCSID value, e.g., 37, translation difficulties will arise at various points.

Your system is configured with QCCSID=65535. You can think of this as something like having all of your FTP transfers done in BINARY mode. CCSID 65535 effectively means “Do not translate/convert”. Setting a standard system CCSID tells the system a lot about how character translation should be done between PCs (or non-PCs) and your system or even between different jobs within your system.

Although you needed to be focused on your current problem, keep in mind that the QCCSID system value ought to be set.. Ideally, plan for it being done, the sooner the better.

Tom

 

Aceofdelts   385 pts.  |   Oct 23 2009  9:06PM GMT

We do a few data transfers via add-ins. Loading to a work file is an advisable technique..

You can skip the IFS if you have a good transfer definition. I recently fought with an upload that had similarities to your issue. One step that helped was to create a DDS with the file layout that I wanted. I created a phony record in this file and ran a data transfer to my PC (to a throw-away file with the same name as my (temporarily renamed) real file. This creates the data transfer map. Finally, I uploaded my real file using this map (it is a “.FDF” on the PC).

Note that it’s OK to define alpha fields to a longer length than needed. Can get complications if data is longer than the target field.

Mike

 
0