Uploading Excel Spreadsheet into Oracle eBusiness Suite. Part 1

Almost any Oracle eBusiness Suite implementation faces a requirement to upload data from Excel. Why? Sometimes, because the end users are used to Excel interface. For example, purchasing department has its own Excel template for requisitions which’s been used for years and everybody likes it and wants to keep on using it. There are also situations where Excel provides a perfect environment for data manipulation and preparation. We found this to be the case almost on all our implementation of Oracle Project Accounting with monthly updates to project budgets.

Oracle’s Applications Desktop Integrator (ADI) provides means to load information from Oracle’s predefined spreadsheets for a set number of business objects, such as GL journals, GL budgets, physical inventory. When ADI meets your needs, this is the best option to go for.

Upload of complex data structures from Excel, such as sales orders or purchase requisitions, requires significant Visual Basic programming. Such Excel workbooks require strict layout structure, built in real-time validations, production database connection, and are specific to the customer. Construction cost of such a template should be measured against the productivity gains. Since an Oracle eBusiness Suite implementation team typically lacks knowledge of Visual Basic, the cost of such an extension is high. We have built several Excel templates for sales order upload and manipulation using Excel 2007 for Oracle eBusiness Suite 11.5.10.

In this post I would like to discuss an approach to load Excel spreadsheets with tabular data, such as project budgets or meter reads, into Oracle eBusiness Suite.

The simplest way to load data from Excel into an Oracle database is

• save the Excel spreadsheet as a comma delimited file
• somehow place it on the concurrent manager tier, or anywhere where SQL*Loader is available
• run SQL*Loader to import the file into a staging table.

This approach shows the major data transformation needs, i.e. data should

• be stored in an ASCII file
• exist on the concurrent manager tier.

It also involves significant human intervention in order to

• Convert Excel file into CSV
• Upload to concurrent manager tier
• Run SQL*Loader

What we will discuss here is how to build a friendly user interface through which a user can upload an Excel file, and see the data loaded into a staging table using SQL*Loader. We will build this extension in such a manner that it does not depend on the nature of the incoming data and can be reused to load different Excel spreadsheets into different staging tables.

Want to know how? Stay tuned…