Hi,

It is a common requirement to build a User Interface thru which end user can upload or download a file (CSV or Excel) to / from web. I have written up a series of separate How-to documents for all combinations of these use cases and this article is the root summary page of those.

1. Overview

"pxParseExcelFile", newly introduced from Pega 8, is the substitute for old "MSOParseExcelFile" and some people may believe that old one can be seamlessly replaced by new one. However, it is not really the case - "pxParseExcelFile" can handle Page List only but it is not intended to work for Single Value property or combination of Page List / Single Value property at moment ("MSOParseExcelFile" can handle both). To clarify the scope of support, I have summarized each use case in the table below.

2. Troubleshooting and Frequently Asked Questions

I have seen some people faced miscellaneous issues when using above activities. I will share a couple of tips from my past experiences. All the details are also explained in the How-to document of each corresponding post.

Q. When I hit the upload button without specifying a file (CSV / Excel), I get a PRRuntime exception as below. How can I avoid this?

A. This error message is displayed if you are using UI-Kit application. You can avoid this by adding an error handling step as below.

If you are using Theme Cosmos application, error message won't be displayed, but system may still get unresponsive (the behavior may differ per version). Therefore, I suggest that you implement this for any application.

Q. I have specified a file and hit the upload button, but nothing happens. Also, "Can't continue with file upload. "" is not a file." error is observed in the Tracer. What is going on?

A. This issue won't happen if you are using UI-Kit application, and probably you are using Theme Cosmos application. Also, you may be using FilePath control, or SetEncodingNow checkbox is turned off, or both. Ensure you use pzFilePathWithForm control and turn on SetEncodngNow checkbox as below.

(1) Use pzFilePathWithForm control instead of FilePath control

(2) Turn on SetEncodingNow checkbox in Parameters tab of control

Q. When I upload a Shift-JIS formatted CSV file, Japanese characters get garbled as below. How can I avoid this error?

A. Although UTF-8 is the global standard character encoding method today, that is not quite the case in Japan. As a matter of fact, Japanese Microsoft Excel reads and saves CSV file with ANSI (i.e. Shift-JIS) by default. Hence, as far as end users work on Japanese desktop environment, it is highly possible that they upload their local Shift-JIS formatted CSV file into Pega (Pega Platform handles all files in UTF-8), ending up with characters garbling. There are two ways to resolve this situation:

(1) End users converts the CSV file format from Shift-JIS to UTF-8. The easiest way is to use an editor such as Windows' notepad. With this approach, no customization is required for Pega Platform side. However, every end user needs to do this work, every time they upload files, which is painful.

(2) Another approach is to customize "pxUploadCSVResuts" activity to handle files in Shift-JIS (see screenshot below). Since this rule is Final, you'll have to do Save As to make your own one. The advantage of this approach is that end users don't need to do any extra work. The drawback is that if they upload UTF-8 formatted CSV files, characters get garbled in turn. That said, most of Japanese customers prefer this approach in my experience as Shift-JIS is still dominant in Japanese desktop environment.

Q. When I download and open a CSV file by Japanese Microsoft Excel, Japanese characters are garbled as below. How can I avoid this error?

A. The fundamental cause is the same as I explained above. The issue is coming from the gap that Pega Platform outputs files in UTF-8 and Japanese Microsoft Excel reads it in Shift-JIS. There are two ways to resolve this issue.

(1) End users converts the CSV file format from UTF-8 to Shitf-JIS (=ANSI).

(2) Another approach is to customize "pxConvertResultsToCSV" activity to output files in Shift-JIS (see screenshot below). Since this rule is Final, you'll have to do Save As to make your own custom one.

Q. Customer wants to download CSV file with different header name than actual property name. For example, instead of ID, Name, Price, they want to have #, 品名, 価格 in the outpute file. Is that possible?

A. Yes, it is possible. Specify your preferred header name for CSVPropHeaders parameter as below.

Q. Customer wants to upload CSV file with different header name than actual property name. For example, instead of ID, Name, Price, they want to have #, 品名, 価格 in the input file. Is that possible?

A. Out-of-the-box "pxUploadCSVResults" activity doesn't have this functionality. Whatever header name in the input file is loaded on a clipboard, but actual properties will stay blank. In this case, you can manually copy the value to the actual properties using RUF as below.

Q. When I download a file (CSV / Excel), another window pops up. Can I suppress this extra window?

A. Specify _self in Window Name parameter in Open URL in Window action. This allows a file to be downloaded within the current browser window.

Q. We are using pagination for the table. When we download a CSV or Excel file, only the first page is included in the file and all the rest are gone. How can we include the entire records in the file?

A. This issue won't happen if table source is property or Data Page. You are probably using Report Definition for the table source. One way to avoid this issue is to switch to use Data Page (you can use Report Definition for the Data Page source). Or if you still need to directly use Report Definition for the table source, disable paging in the Report Definition and call Rule-Obj-Report-Definition.pxRetrieveReportData prior to the main step (i.e. "pxConvertResultsToCSV" for CSV download, and "pxGenerateExcelFile" for Excel download) to create an additional page on a clipboard. This approach is a bit wasteful, but still works fine with RD.

Q. Customer has built funtion to upload Excel form as below using "MSOParseExcelFile" in Pega 7. Since this activity is deprecated in Pega 8, they have replaced "MSOParseExcelFile" with "pxParseExcelFile". However, it is not working as expected.

A. This Excel form contains both Single Value property and Page List property. As of Pega 8.8, "pxParseExcelFile" is not designed to support this complex form and it can support Page List only. Although "MSOParseExcelFile" is deprecated, it is still functional. You can use it in Pega 8 if necessary.

Q. Customer has built funtion to download Excel file as below using "MSOGenerateExcelFile" in Pega 7. Since this activity is deprecated in Pega 8, they have replaced "MSOGenerateExcelFile" with "pxGenerateExcelFile". However, there are some styling issues as shown below.

A. "MSOGenerateExcelFile" and "pxGenerateExcelFile" use different technology. If anything goes wrong with "pxGenerateExcelFile", try using "MSOGenerateExcelFile" to see if it resolves the issue. Although it is deprecated, it is still functional. You can use it in Pega 8 if necessary.

Q. Customer has Excel macro-enabled workbook (XLSM) and they want to use it as the template to generate an Excel file. However, when we uploaded this file to Binary File with XLSM extension, pxGenerateExcelFile didn't work. Why? Also, what are the supported extensions for downloading and uploading Excel file in Pega?

A. As of Pega 8.8, official documentation says XLSX is the only supported extension by pxParseExcelFile and pxGenerateExcelFile. There are no information available for MSOParseExcelFile and MSOGenerateExcelFile. We have tested XLS, XLSX, and XLSM for these four activities to see if it works. Below are the results.

• Upload

• Download

3. Related topic

Uploading a file (CSV or Excel) functionality merely constructs a Page List on a clipboard and it is up to you how you transact the data. Typical use case would be to insert or update the records into the database. For your reference, I have created How-to document to build Operator ID bulk insert function.

https://support.pega.com/discussion/how-build-operator-id-bulk-insert-function

Hope this helps. 

Thanks,