Upload Excel file from PC to iseries

510 pts.
Tags:
AS/400
iSeries Data Transfer
I need to Upload an excel file with (.xls format) from PC to iseries. So I have changed to CSV format . I have created a folder in QDLS folder and i have placed the excel to QDLS folder using iseries navigator. So using following command i have copied all the records to Physical file.Its working fine. CPYFRMIMPF FROMSTMF('QDLS/CONTRACT/TEST99.CSV') TOFILE(library1/CONTOUT) RCDDLM(*CRLF) STRDLM(*NONE) RPLNULLVAL(*FLDDFT) 1. I need to do the same without using iseries navigator and without converting it to CSV format(Comma delimited format).Please let me know.

Software/Hardware used:
AS400,RPG IV, CLLE

Answer Wiki

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

Discuss This Question: 5  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
  • Splat
    If you want to avoid using Navigator you might be better served by using the IFS rather than QDLS.  Create and share a folder in the IFS root, then put your file in the shared folder.
    7,235 pointsBadges:
    report
  • Rickmcd
    If you have Client Access why not use Excel addins?
    1,605 pointsBadges:
    report
  • NickHutcheson1
    This is one.  &LOADHDR is preloaded as VALUE('/HOME/path/out/LoadMaster_' || +&DATETIME || '.CSV')                     CPYTOIMPF  FROMFILE(LIB/LODHHF) TOSTMF(&LOADHDR) +             MBROPT(*REPLACE) STMFCODPAG(*PCASCII) +                 RCDDLM(*CR) DTAFMT(*DLM) DATFMT(*ISO) +                 TIMFMT(*ISO)                              
    1,380 pointsBadges:
    report
  • TomLiotta
    First three steps:   1. Stop 2. Using 3. QDLS   It's been maybe 15 years since /QDLS was a viable networking solution. The whole world and especially AS/400s have long since left /QDLS technology behind. The days of DOS file systems and DOS networking effectively no longer exist. If you're using any version of Excel or Windows that is from this century, and if your AS/400 OS version is newer than V3R2, then /QDLS should simply be forgotten.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    ...without using iseries navigator and without converting it to CSV format(Comma delimited format).   First, why is iSeries Navigator being used? Just drag/drop the file to that shared directory that you want to put the spreadsheet in to. If you prefer, just {Save} from Excel into the directory you want.   But your example command implies that you want the data from the spreadsheet stored in a database file, and Excel spreadsheets aren't compatible with database files. The data formats must be converted. The CPYFRMIMPF command is capable of converting the intermediate .CSV file format into database formats and Excel can export spreadsheet data to .CSV format, but Excel spreadsheet .XLS files use a much more complex format that is intended to control how Excel handles the data. Other programs don't know what to do with the control data inside of .XLS files. (Open one with Notepad to get an idea how complicated they are. And Microsoft regularly adds new stuff for new versions of Excel.)   Since .XLS files are not intended for export/import, you need to use something that is capable of handling the data conversions. If you want the data to be put into a DB2 table on your AS/400, you can use the iSeries Access add-on for Excel. It will convert the data for you.   If the spreadsheet isn't formatted appropriately for the format of your database file, you'll need a utility function that can read the .XLS data into a program. The program can then format fields/variables as needed for database I/O. Programming might be for a PC and use ODBC (or related) methods for database work, or it could be native AS/400 programming.   Commonly, the conversion functions use POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files methods. Scott Klement has done some excellent work describing how POI/HSSF/XSSF can be used by AS/400 developers.   Start by using the /root file system instead of /QDLS for uploading to your AS/400. (Alternatively, have your AS/400 directly access the .XLS file on your PC through the /QNTC file system.)   Tom
    125,585 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