Accessing Citadel 5 Data with Microsoft Excel

Introduction
With Citadel 5, you can import data into external applications using SQL and ODBC. National Instruments Lookout and the LabVIEW Datalogging and Supervisory Control Module (DSC Module) leverage the power of the Citadel database to provide users with robust datalogging solutions. This document describes how to extract data contained in a Citadel 5 database and import it into MS Excel.

Required Software
National Instruments Lookout 6.0 or the LabVIEW DSC Module 7.x
Microsoft Excel
Microsoft Query

Overview
This tutorial explains how to use MS Excel and MS Query to obtain and chart data from a Citadel 5 database. You must have existing data in your database to complete the steps in this document.
This tutorial does not demonstrate or explain how to configure Lookout or the DSC Module to log data to a Citadel database.

Importing Data with the Query Wizard

Step 1: Configuring a Data Source through ODBC

The Lookout Developer Manual and the LabVIEW Datalogging and Supervisory Control Module Developer Manual contain extensive documentation about configuring a Citadel 5 database for access through ODBC. A System Data Source Name (DSN) is automatically created when you install and configure Lookout and/or the DSC Module. If you need to reconfigure this data source or want to create a new DSN, refer to the Developer Manual for instructions.

Step 2: Understanding Citadel 5 Data Storage

Citadel logs data only when the data value has changed, not necessarily at a specified interval. Citadel 5 provides access to data in two ways: the IntData table and the RawData table. Query the IntData table to return interpolated data at a specified time interval. Perform a similar query on the RawData table to return data as it was actually logged into the database.

Step 3: Importing Data into Excel

In this tutorial, you use the IntData table to generate a report of the time history of the process. This query provides data that is spaced evenly through time.

1. Open Microsoft Excel and create a new workbook.

2. Select Data»Import External Data»New Database Query, as shown in the following figure.



3. Select the DSN that you want to connect to. The following figure shows how you select the data source. If you are using Lookout, the default DSN name is c__program_files_national_instru*. If you are using the DSC Module, the default DSN name is C__data*.



4. Click the OK button. The Query Wizard appears and lists the different ways you can access data stored in the Citadel database.

5. From the IntData table, select the following three data items: IntInterval, LocalTime, and the tag of the data item you want to import, as shown in the following figure. Click the Next button.



Now you need to configure the time interpolation value for the query.

6. Select IntInterval and set equals to 0:30, as shown in the following figure, which returns a value for the selected data item from Citadel at an interval of 30 seconds. If you want to retrieve data at 10 minute intervals, set equals to 10:00.



You also can configure a specific date/time range, where the date/time format is specific to your computer. However, if you configure a specific date/time range, you cannot use the Query Wizard to modify the query at a later time. The following figure shows how you can set a specific date/time range using the LocalTime column.



7. Click the Next button after you finish configuring the filter options.

8. Specify the sort order for the data by selecting "Local Time," ascending. Click the Next button.

The equivalent SQL query string for the query you configured in the Query Wizard - Filter Data dialog box is as follows:

SELECT IntData.IntInterval, IntData.LocalTime, IntData."AE-nfolse/Process1/MyPot@value" FROM NICIT.IntData WHERE ((IntData.IntInterval='0:30')) AND ((IntData.LocalTime>{ts '2005-02-02 14:33:00'}) AND (IntData.LocalTime<={ts '2005-02-02 14:40:00'})) ORDER BY IntData.LocalTime

The Query Wizard now prompts you for where you want the query to be stored or if you want to save the query.

9. Import the query directly into Excel and confirm where you want the data to be inserted. Click the Finish button.

The data now appears in the Excel worksheet, and you can graph the data using the Chart Wizard in Excel. You should see something similar to the following figure.



If you need to modify the query after importing the data into Excel, right-click anywhere on the data and select Edit Query from the shortcut menu, as shown in the following figure. Remember that if you added a date range to the query, as in this example, you cannot edit the query using the Query Wizard.

Advanced – Using PivotTables to View Data

PivotTables are a powerful tool you can use to automatically generate tables and charts based on data stored in a database. You can use PivotTables to view data stored in a Citadel database and to easily display data for a given date range or average values over time.

Note PivotTables are an advanced and extremely customizable tool. Refer to the Microsoft Help for information about customizing a PivotTable. You also can find relevant examples on the Microsoft Web site.

Step 1: Configuring a PivotTable in Excel

1. Open a new worksheet in the Excel workbook.

2. Select Menu»Data»Pivot Table and PivotChart Report.

3. Use the data you imported in the previous example by selecting the External Data Source option, as shown in the following figure. Alternatively, you can connect the PivotTable directly to the Citadel database through ODBC. Click the Next button.



4. Click the Get Data button and follow the same procedure as in the last example to configure the query. The Query Wizard closes after you compete the configuration, but Microsoft Query should still be running in the background. If you need to modify the query, click on Microsoft Query in the taskbar and reconfigure the query. You also can click Get Data to open the wizard.

5. Click the Next button. You are now prompted to configure the properties of the PivotTable.

6. Click the Layout button, as shown in the following figure.


[+] Enlarge Image

7. Drag and drop the appropriate fields into the table. Row should contain LocalTime, and Data should contain the data that you want to chart. By default, Excel configures the field to produce a sum. To change the field to produce an average, double-click the field. Click the OK button to confirm the PivotTable layout.



8. Click the Finish button in the PivotTable and PivotChart Wizard. A new PivotTable now appears on the worksheet.

Note When you create a PivotTable, Excel copies the data from the data source into the PivotTable. If the data changes, you need to update the data by right-clicking on the PivotTable and selecting Refresh Data from the shortcut menu. Updating the data might take a significant amount of time if you have a lot of data points to import. To decrease the update time, try modifying the query to reduce the amount of data you return.

Step 2: Creating a PivotChart

1. Right-click the table and select PivotChart from the shortcut menu, as shown in the following figure.



2. After the chart appears, you must configure it by right-clicking the chart background and selecting Chart Type from the shortcut menu, as shown in the following figure.



3. Select a line graph because line graphs work best for trend lines, and click the OK button.

4. You can configure the x-axis so it does not have too many markers by right-clicking the axis text and selecting Format Axis from the shortcut menu, as shown in the following figure.


5. Configure the axis as show in the following figure.



6. Click the OK button. You have completed importing data from a Citadel 5 database into Excel.
Related Links:
KnowledgeBase 2Y5FO5UL: Can I Retrieve Data from a Citadel Database without Lookout or the LabVIEW DSC Module Installed?

Reader Comments | Submit a comment »