AccountingWeb
News
Tutorial: Import data from Pegasus Opera into Excel. By David Carter
This page is for readers of AccountingWEB who need to get reports out of Pegasus Opera and Opera II.It’s not immediately obvious how to get data out of Opera and into Excel. You need an add-on module, either Reporter or (later and better) XRL Reporter.
XRL Reporter is a very good package and last month it won a first place among AccountingWEB’s Software Satisfaction awards, so it’s definitely worth considering.
However, you don’t have to buy an extra module in order to get Opera data into Excel. In fact it’s very easy to do. There’s no need to install any special drivers or the like because Opera is written in the Visual Foxpro database. Foxpro is a Microsoft product, and Visual Foxpro drivers are supplied as standard with Windows.
This article shows how to use Excel's Import External Data command to import Opera data files into Excel.
Step 1: Locate the folders where Opera is stored
Before anything, you need to know where your Opera data files are stored. Use Windows Explorer for this. Mine are in the folder:
c:\opera II\data
Step 2: Set up Opera as a Data Source in Control Panel
Next step is to set up Opera as a data source. From the Start button: Settings - Control Panel - Click on the ODBC32 icon or (Windows XP) Administrative Tools - Data Sources (ODBC).
The ODBC Data Source Administrator box appears.
Switch to the System DSN tab. Add.
From the list of drivers, select: Microsoft Foxpro VFP Driver (*.dbf)
Data Source Name: Opera
Description: Opera
Database type: choose Visual Foxpro database (I don’t know what Free Table Directory, the alternative, means).
Path: Browse to find c:\opera II\data (or whichever is your path):
Open the DATA folder, then select: comp_z.dbc.... Open. OK
You are back at the ODBC Data Source Administrator box. Opera has been added to the list of System Data Sources. OK.
Step 3: Import the data into Excel
Now start up Excel; make sure a new Workbook is open. Click onto cell A1. Then from main menu:
The Choose Data Source box appears. Select Opera from the list. OK.
Hopefully, you now see the Query Wizard – Choose columns box, with a list of tables AALOG, ABBAL etc .
Step 4: Download your data files into Excel.
You are now able physically to get your Opera data into Excel. You next task is to download the various Opera data tables and start digging through them to find the fields you want. There’s a lot of them, but these in particular seem worth a look.
if you are interested in Sales and Margin Analysis
if you are interested in Nominal (GL) Reporting
Tip: after downloading a data table, highlight rows 1 to 4, then Copy. Then go into another worksheet and Paste Special - Transpose. This will give you a list of the field names, plus some sample data.
END OF SESSION
Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
Reporting tutorials index: Use MS Query to extract and analyse accounts data
Reporting Tools #4: Introduction to summary reporting
Towards an analysis-ready file for reporting #1
Towards an analysis-ready file for reporting #2
Management Reporting in Sage Line 50 and Instant
Want to learn more about pivot tables? Start here
Improve your reporting skills with self-teach tutorials
An introduction to Excel-driven reporting tools
Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.
AccountingWEB.co.uk 3-Nov-2007
Categories: IT Features, Management Reporting Features, ExcelZone Features
Times read: 4713
