× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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;
    } 


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.