With Citadel 5, you can import data into external applications using SQL and ODBC. NI LabVIEW Datalogging and Supervisory Control (DSC) Module leverages the power of the Citadel database to provide users with robust data logging solutions. This document describes how to extract data contained in a Citadel 5 database and import it into MS Excel.
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.
A System Data Source Name (DSN) is automatically created when you install and configure the LabVIEW DSC Module. Any data that you have logged using the LabVIEW DSC module is located in the database specified in the project library properties.
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.
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.
Open Microsoft Excel and create a new workbook.
Select the Data tab, and under the Get External Data group select From Other Sources»From Microsoft Query, as shown in the following figure.
Click the OK button. The Query Wizard appears and lists the different ways you can access data stored in the Citadel database.
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.
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.
Click the Next button after you finish configuring the filter options.
Specify the sort order for the data by selecting "Local Time" and Ascending. Click the Next button.
Notice also that the “Command text” is available from this dialog box.
Remember that if you added a date range to the query, as in this example, you cannot edit the query using the Query Wizard.
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.
Open a new worksheet in the Excel workbook in which you inserted the table.
Select the Insert tab, and under the Tables group select PivotTable»PivotTable.
Click the OK.
A PivotTable will appear on a new sheet. Click on the PivotTable to configure the PivotTable. A PivotTable Field List will appear on the right. For the PivotTable in this example, drag LocalTime in the Row Labels box and the relevant data into the Values box. The worksheet should look like the following figure.
The default for the Values field is sum. To change this, click on the data in the Values box and select Value Field Settings…
Drag and drop the fields into the appropriate areas to configure your PivotTable.
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.
Select a line graph as they type of chart you wish to add and click OK.
Select a line graph because line graphs work best for trend lines, and click the OK button.
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.
Configure the axis only show one out of every ten labels as in the figure below .
Collaborate with other users in our discussion forums
A valid service agreement may be required, and support options vary by country.