Tuesday, July 31, 2007

ExcelToCI limitations: when not to use it

Since it's introduction with PeopleTools 8.4x, ExcelToCI has generated excitement particularly among consultants running implementations. Finally there was a tool that allowed consultants with little or no technical knowledge to quickly load data into the system.

Now, as it normally happens in these situations, a good tool may become a bad choice if we are using it in a way it wasn't designed for. Personally, I've seen people trying to use ExcelToCI to run complex data loading resulting in unexpected delays due to the need to build ad-hoc tools for conversion.

What are the most significant ExcelToCI limitations? You may find others, but from my experience, these are quite important:

ExcelToCI does not allow to determine the order in which fields are loaded into the application. The order is determined by the tab order defined at the page level in Application Designer.

Loading large data volumes becomes unpractical for performance reasons. Excel could easily hang when manipulating spreadsheets with over 10,000 rows.

Excel becomes nearly blocked when executing the load. It's difficult to perform any other action on the same workstation, and particularly to load several files in parallel.

Can only run in Add or Update mode, but there is not hybrid option to first try to Add and then Update is the row was present.

At BNB, we built a tool called FileToCI (Ok, not much marketing brainstorming here!) that deals with most of these limitations:

Lets you choose in which order you want fields to be loaded.

Loads from CSV files or database tables, which are much easier to handle than Excel files.

Lets you load different files in parallel. Processing is done at the server level, without capturing your local resources.

Includes a Hybrid load option, which only adds the rows that were not present before the load and updates the remaining ones.

Again, I'm not saying ExcelToCI is useless. That would be far away from the truth. Reality still is that for setup data load and certain types of conversion, ExcelToCi remains a very intuitive and powerful tool.

What have been your experiences with ExcelToCI? Have you used it for massive data loading?

Well, it is different from VNDR_IMPORT because VNDR_IMPORT is exclusively for importing vendors. FileToCI is a tool that allows you to load any component in the Application, pretty much as ExcelToCI does, but without its limitations.Regarding the population of ExcelToCI with existing data, this is not an operation supported by the standard ExcelToCI. However, if you take a look at the code beneath the ExcelToCI implementation, you will see that apart from the CREATE, UPDATE and UPDATE DATA operations, there is one called GET, which has not been fully implemented, but shouldn't be too complex if you take some time to do it.

Javier, I upgraded users to Internet explorer 8. Users receive 'Sendsoaprequest_submitTODB' error when they perform ExcelToCI. I am not sure how this would be a IE error but they would like me to figure this out. I know nothing about PS. This error does not occure in IE6. Any ideas?

Forgive my ignorance or shall i say understanding but to get the data needed for the excel spreadsheet, do we have to write a query to get all the relevant information? Thank you so much for your time.

ExcelToCI is actually a tool to upload information from Excel to PeopleSoft. If you need to pre-populate Excel to run updates using ExcelToCI, then you have many options, including using PS/Query or plain SQL statements against your database. You may need some Excel copy/paste to transfer then the information to the ExcelToCI template.

Sorry for the late reply. Honestly, I haven't worked with the timesheet component with ExcelToCI. I would say that it works, but it may take a couple of iterations to consider the creation of a new timesheet, the addition of new lines and the update of them.