How to Add a Spreadsheet to Business Objects Query
By Marc Frazier
Although Business Objects is a self-service tool for accessing data warehouse defined universes (table views), pulling data from less sophisticated sources allows you to collaborate and to handle to urgent requests. Picture your colleague giving you a spreadsheet to analyze: You could spend countless hours formatting a report from scratch or you could let Business Objects do the heavy lifting with its pre-formatted reporting tools. Adding a Microsoft Excel file to a query does require SQL (Structured Query Language) coding, but Business Objects’ point-and-click interface does all the hard work without you having to do any programming.
Click “Start,” from the Windows Desktop. Hover over “Programs,” locate “Business Objects” and double-click. If you have a desktop shortcut named “Business Objects” you can simply double-click that icon.
Enter your user name and password, when prompted. You are allowed three attempts, and it is case sensitive. If you can't log in, contact your system administrator.
Select “File” and “New,” from the menu bar.
Direct Business Objects to point to the location of the data you want to retrieve in the window that appears: “Universe” (remote server) or “Others” (local computer). Select “Personal Data Files,” from the drop-down list of “Others.”
Select “Microsoft Excel Files” (*.xls) at the “Format” prompt, when the “Access Personal Data” wizard launches.
Click “Browse,” and an Explorer window will appear where you can navigate to the spreadsheet on your hard drive.
Select the Excel file and click “Open.”
Select the option “First row contains column names.”
Select the “Definition” tab, and then select any field’s name to accept or rename column headers. You have successfully imported spreadsheet data, from which you can now perform analysis and create reports.
- Some spreadsheets will have hidden rows or columns containing irrelevant data or single cell totals. Make a copy of the original sheet by holding down the “CTRL” key and dragging the tab to the end. From the menu bar, select "Format"and "Unhide," then delete extraneous rows and columns so that Business Objects has a nice clean block of data to query.
As a programmer and database administrator, Marc Frazier brings both technical and financial acumen to his online articles. Since 2003 he has been writing user manuals, policy and procedures, developing risk models, performance dashboards and eLearning applications. He holds a Bachelor of Arts in English from the University of California, Los Angeles and a Technical Communications Certificate from the University of California, San Diego.