Opening Excel™ data files using Analysis Studio
Data extraction from different data sources is an elementary procedure in statistical analysis and data mining projects. Retrieving data using Analysis Studio can be done simply by using the Analysis Studio data base wizard and SQL query generator. This article includes and example of retrieving data from a local Excel ™ file.
Start the Analysis Studio application and click File -> New. The project manager screen will show:
Enter the name of your project in the project name text box and click the "Create New" link. The data base wizard will appear.
The data base wizard includes built in connections to various data base engines including:
- SQL Server ™ (starting version 2000)
- Oracle ™ (starting version 8.7)
- Excel ™ (starting version 3.0)
- CSV files
- MS Access ™ (starting version 97)
- Xml files
Click the desired data base (in this case Excel™) and click the "New Connection" label to create a new connection. The new connection editor screen will show.
Enter the excel file name in the "File Name" field and select the Excel™ version (required only when the excel file version is older than Excel™ 97). You may click the "Test Connection" button to ensure that the connections settings are valid. Click OK and return to the data base connection wizard.
Note – A common Excel™ connection problem is the attempt to access a file currently opened by Excel™. Make sure that the file is not open for edit in Excel™
Now the data base connection wizard will have the newly created connection in the recently used connections. Click next, the table (excel sheet) selection screen will show.
Mark a data sheet and click the add button or double click a data sheet. The added data sheet will show in the right side of the window. In general data sheets (data tables) that appear on the right side will be retrieved by Analysis Studio. The data base wizard allows editing relations between tables for complex queries.
Once data tables are selected, the finish button will be enabled. Click the "Finish" button to start analyzing the selected data using Analysis Studio.
The Analysis Studio data screen of a currently added Excel™ data file.