If there’s one common theme among IBM i power users, it’s that they all love playing with spreadsheets! Fortunately, Valence makes it easy to cater to this love. RPG developers simply feed an SQL statement to vvOut_execSQLtoSS() or vvOut_execSQLtoCSV(), and the results are automatically downloaded into an Excel-compatible spreadsheet file.
But what about going the other direction? What if users could fiddle with downloaded spreadsheet data and then upload the massaged information to an RPG program that could validate the data and plug it into a physical file? Fortunately this too is fairly simple to do in Valence, and this month’s tip shows you how it’s done. To demonstrate this upload-and-consume concept we’ll use the File Upload example app included in Valence.
In the latest Valence 4.2 maintenance release (4.2.2015121501), RPG program EXUPLOAD1 has been updated to include logic that reads through an uploaded file. If you’re on a prior release, please upgrade before continuing so you can follow along with the code. In order to easily extract the “meat” of the spreadsheet data, it is recommended users save their spreadsheet as a file of comma-separated values, also known as a .csv file. These files are completely data-centric and devoid of any formatting noise, thus making it easily digested by an RPG program. With the data saved in .csv format, the first step for the developer is to give the user a prompt for uploading the file to the server for consumption by an RPG program. The File Upload app provides a “Browse” button so users can quickly navigate to the specific path on their PC containing the desired file. Once selected, the “Upload” button makes a call to Valence program VVUPLOAD to handle the process of transferring the file to IBM i backend.
Note that this file uploading process is different from a typical Valence AJAX call, as the form parameters and file data are sent as a multipart document in the body of the HTTP request. So you can’t use a standard vvIn_char() call on the back-end to retrieve posted data as you normally would. Instead, VVUPLOAD explicitly pulls in the standard expected parameters (specifically, the session ID, app ID, program to call and file/path) from the HTTP document, prepares the IBM i to receive the file data and makes a call to the specified program, passing the session ID as the lone parameter.
Any additional “non-standard” parameters passed from the browser are stored by VVUPLOAD as session variables, which the called RPG program can then access via vvUtility_getSessVar(‘variableName’:sid); It is the called program’s job to receive the uploaded file, typically saving it to the IFS via vvIn_file(), and then reading through it to process the individual columns. In EXUPLOAD1, the vvIn_file() call uploads the spreadsheet and returns a pointer to the data, which the readCSV() procedure subsequently interrogates. The name of the file, which has been processed inside VVUPLOAD, is returned through the VVIN.FILENAME data structure field. If the file name ends in .csv, the program calls the readSCV() procedure. With each row in the .csv file separated by a linefeed (CR+LF), the readCSV() routine loops through the row data and uses the vvUtility_parseIntoCharArray() procedure to extract the individual comma-separated values, placing the data into an array that the RPG program can work with. Since by convention the first row of most .csv files contains field names or headings, the EXUPLOAD1 program assumes the first row is a header row and places each cell into an array called HEADINGS. The contents of subsequent rows are placed into an array called DETAILS. It should be noted that under certain circumstances, an individual cell in a .csv file may be enclosed in double quotes.
For example, a string containing a value with a comma (such as “123,456”) must be enclosed in double quotes so the internal comma is not treated as a column separator. Furthermore, any cells containing double quotes must be escaped with an additional double quote (so Simon said, “hello” would be encoded as Simon said, “”hello””). To handle these .csv-specific conditions, the vvUtility_parseIntoCharArray procedure now has an optional “watch for quotes” parm which, when set to *ON, tells the routine to automatically handle these quoted cells. With all the spreadsheet data available to your RPG program, you’re free to write whatever logic you need to validate the data and update the appropriate physical file(s). You can use the column headings in HEADINGS to drive the validation for each corresponding DETAILS array value, sending back an error message when a user attempts to upload, for example, an invalid product or customer number.