RE: MS Excel ====>> iSeries.

The approach I suggested requires zero RPG programming.

Are you looking for a generic file upload utility that can update ANY file
(or potentially any file) on your iSeries?  That will take more time to
develop than doing a one-off.  I would start out just doing it for one file
to know what you are up against and save the more generic programming for
round two of the project. Besides, depending on the orchestration of your
tables/files you will need to normalize how the user puts information into
the csv file. For instance I have a lot of associations in my db and to
match two tables I allow the user to enter multiple values in the same cell
delimited by a | character.  They don't enter the throw away unique key of
the table, but rather the descriptive name of the record (if that makes
sense).

Here is the listener method that is being called from my JSF app that does
the upload and parsing of the csv file. Hopefully that will give more
insight as to what I was trying to accomplish.  This upload is different
than the one I described earlier (which was more complicated). This one is
meant to allow a user to maintain the user profiles on the system from an
excel spreadsheet.

HTH,
Aaron Bartell
http://mowyourlawn.com/blog

    public String upload() throws IOException {

        if (this.upFile == null || this.upFile.getName().equals("")) {
            UIHelper.addMessage(null, "Must select a CSV file to upload.");
            return null;
        }

        if (!this.upFile.getName().toUpperCase().endsWith("CSV")) {
            UIHelper.addMessage(null, "The file you have choosen to upload
is not a .CSV file.");
            return null;
        }

        Sysctl sys = Sysctl.getRecord();

        String csvFile = sys.getUploadfolder() + (new
VMID()).toString().replaceAll(":", "");
        FileControl.delete(csvFile);
        FileControl.write(upFile.getInputStream(), csvFile);

        User user;

        Integer useruid = LoginController.user().getUid();

        DataFile read = DataFile.createReader("8859_1");
        read.setDataFormat(new CSVFormat());
        read.containsHeader(true);

        int curRow = 1;
        int processedCount = 0;

        int NAME = 0;
        int PASSWORD = 1;
        int FIRST_NAME = 2;
        int LAST_NAME = 3;
        int ACCESS_LEVEL = 4;
        int ACTIVE = 5;
        int EMAIL = 6;
        int COST_GROUP_DEFAULT = 7;
        int PRICE_COLUMN = 8;
        int PROTECT_COST_GROUP = 9;
        int COMPANY = 10;
        int USER_PREMISSION_GROUPS = 11;

        try {

            read.open(new File(csvFile));
            for (DataRow row = read.next(); row != null; row = read.next())
{
                curRow += 1;
                
                // Query the user table to see if this name exists. If
                // it doesn't that means that this is a new user and an
                // INSERT will occur.
                user = User.getExact(row.getString(NAME));

                if (row.getString(NAME).trim().equals("")) {
                    UIHelper.addMessage(null, "Invalid " +
FieldLabel.getFieldLabel("user_name") + " specified:"
                            + row.getString(NAME) + " Row " + curRow);
                    continue;
                }

                Accesslevel al =
Accesslevel.getExact(row.getString(ACCESS_LEVEL));
                if (al.getUid() == null) {
                    UIHelper.addMessage(null, "Invalid Access Level:" +
row.getString(ACCESS_LEVEL) + " Row " + curRow);
                    continue;
                }

                Groups priceColumn =
Groups.getExact(row.getString(PRICE_COLUMN), Groups.PRICECOLUMN);
                if (priceColumn.getUid() == null) {
                    UIHelper.addMessage(null, "Invalid Price Column: " +
row.getString(PRICE_COLUMN) + " Row " + curRow);
                    continue;
                }

                Groups costGroup =
Groups.getExact(row.getString(COST_GROUP_DEFAULT), Groups.COST);
                if (costGroup.getUid() == null) {
                    UIHelper.addMessage(null, "Invalid " +
FieldLabel.getFieldLabel("user_costgroupdefault")
                            + " specified:" +
row.getString(COST_GROUP_DEFAULT) + " Row " + curRow);
                    continue;
                }
                
                Company company = Company.getExact(row.getString(COMPANY));
                if (company.getUid() == null) {
                    UIHelper.addMessage(null, "Invalid " +
FieldLabel.getFieldLabel("company") + " specified:"
                            + row.getString(COST_GROUP_DEFAULT) + " Row " +
curRow);
                    continue;
                }

                user.setAccessleveluid(al.getUid());
                if (row.getString(ACTIVE).equals("TRUE"))
                    user.setActive(Const.TRUE);
                else
                    user.setActive(Const.FALSE);
                user.setCompanyuid(company.getUid());
                user.setCostgroupuiddefault(costGroup.getUid());
                user.setEmail(row.getString(EMAIL));
                user.setFirstname(row.getString(FIRST_NAME));
                user.setLastname(row.getString(LAST_NAME));
                user.setName(row.getString(NAME));
                if (!row.getString(PASSWORD).equals(LEAVE_AS_IS))
 
user.setPassword(PasswordService.getInstance().encrypt(row.getString(PASSWOR
D)));
                user.setPricecolumnuid(priceColumn.getUid());
                if (row.getString(PROTECT_COST_GROUP).equals("TRUE"))
                    user.setProtectcostgroup(Const.TRUE);
                else
                    user.setProtectcostgroup(Const.FALSE);

                //
                // Save the user here to retrieve the uid so it can be used
                // in subsequent statements.
                //
                DBConn.sess().saveOrUpdate(user);

                //
                // Add this user to the appropriate user permission groups.
                // Delete all existing relationships before creating the new
                // ones.
                //
                Usergroupasc.deleteUser(user.getUid());

                StringTokenizer userPermGrp = new
StringTokenizer(row.getString(USER_PREMISSION_GROUPS), "|");
                while (userPermGrp.hasMoreTokens()) {
                    String grpName = userPermGrp.nextToken();
                    Groups grp = Groups.getExact(grpName, Groups.USER);

                    if (grp.getUid() == null) {
                        UIHelper.addMessage(null, "Invalid " +
FieldLabel.getFieldLabel("usergroup") + " specified:"
                                + grpName + " Row " + curRow);
                        continue;
                    }

                    Usergroupasc uga = new Usergroupasc();
                    uga.setGroupuid(grp.getUid());
                    uga.setUseruid(user.getUid());
                    DBConn.sess().saveOrUpdateCopy(uga);
                }

                
                processedCount += 1;

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            read.close();
            FileControl.delete(csvFile);
        }

        UIHelper.addMessage(null, (processedCount) + " rows inserted/updated
in user table(s).");

        return null;
    }