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?

12 comments:

  1. Can you tell me more abt FiletoCI tool..can i download it from some where??

    ReplyDelete
  2. Right now, we're only installing FileToCI as part of a service, not selling or distributing it independently.

    ReplyDelete
  3. How does this compare to VNDR_IMPORT app engine. Also do you know of any easy way of populating ExcelToCI with existing data, in case you using it just for Update

    ReplyDelete
  4. 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.

    ReplyDelete
  5. 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?

    ReplyDelete
  6. Buenas tardes Javier,
    Muy buen trabajo el que realizas en este blog.

    Es posible desde PeopleSoft escribir en un fichero .xls para luego hacer unas gráficas y exportar esas gráficas a PowerPoint. Todo ello automáticamente.
    ¿Alguna idea a seguir?

    Muchas gracias de antemano.
    Y perdona las molestias.

    Saludos,
    Paco

    ReplyDelete
  7. Hola Paco

    No conozco ninguna manera de automatizar la creación de un PPT desde PeopleSoft.

    Sin embargo, creo que podrías llegar a solucionarlo utilizando Macros de PowerPoint y Excel en Visual Basic for Applications.

    La idea sería construir una macro que acceda a PeopleSoft para extraer la información de una consulta (llamando a un CI o al Query API) y construir una gráfica en Excel. Una vez construida la gráfica, supongo que se podrá automatizar, también con macros, su copiado a PowerPoint.

    No sé exactamente cuál es el objeto de tener la gráfica en PPT, pero si no es obligatorio, podría generar una también desde PeopleCode o Crystal Reports.

    Un saludo.

    ReplyDelete
  8. 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.

    ReplyDelete
  9. Hi!

    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.

    I hope this helps!

    ReplyDelete
  10. Hi Javier, thanks for your great blog. I've learnt a lot from your post.

    We would like to load elapse time into our PS T&L timesheet using Excel to CI. Is this possible or should I look for a different solution?

    Thanks very much in advance.
    Rick

    ReplyDelete
  11. Hi Rick

    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.

    I hope this helps.

    Many thanks.

    ReplyDelete