Upload Spreadsheet to AS400 using Excel addin – creates graphic fields for alpha fields

25 pts.
Tags:
AS/400
Excel 2007
iSeries Data Transfer
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

——————————————————————————————————————————————————–

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    What is your QCCSID system value set to be? Tom
    125,585 pointsBadges:
    report
  • PaulClemens
    The system value is 65535.
    25 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • PaulClemens
    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.
    25 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • aceofdelts
    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
    1,930 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following