Use the search and browse feature to view Kepware's repository of more than 500 Knowledge Base articles. Narrow your results or type your query into the search field below.

Search Solutions Results By: View All Solutions

Kepware Knowledge Base: Solution


Logging Data to an Excel Worksheet Using DataLogger


Last Update: 11/12/2018

Users can log data to an existing Microsoft (MS) Excel spreadsheet by using DataLogger and MS Access Link Tables. For more information, refer to the instructions below.

Selecting a Database

  1. To start, open MS Access and then select the database in which the Link Table will be created. If it is a new project, users must create a new database.
  2. Next, add a Link Table to the Access Database.

Adding a Link Table Using Office 2003 and Older

  1. To start, click File | Get External Data | Link Tables.
  2. In the file browser, change the file type to Microsoft Excel. Then, select the spreadsheet to which data will be logged.
  3. Next, use the Link Spreadsheet Wizard to select the spreadsheet that will be used for logging.*
  4. Specify a name for the new link table, and then click Finish.

    Note: A message box will appear indicating that the table has been linked to the spreadsheet successfully.

Adding a Link Table Using Office 2007 and Newer

  1. To start, locate the Ribbon bar and then select External Data.
  2. In Import & Link, select Excel.
  3. Next, use the Get External Data Wizard to select the spreadsheet that will be used for logging.*
  4. Link to the data source by creating a linked table and then specifying where the data will be logged.
  5. Once finished, click OK.
  6. In the Link Spreadsheet Wizard, select the worksheet that will be linked.
  7. Specify a name for the Link Table, and then click Finish.

    Note: A message box will appear indicating that the table has been linked to the spreadsheet successfully.

Adding a New Access DSN

  1. Create a new Access DSN that will point to the Access database with the Link Table.
  2. In the server, open DataLogger and then create a new log group.
  3. Open the General tab, and then click Configure DSN to create a new DSN connection. If one already exists, skip ahead to Step 9.
  4. In the Microsoft ODBC Data Source Administrator, open the System DSN tab.
    Note: A System DSN will be available to the server regardless of whether it is running in Interactive or System Service mode.
  5. Next, click Add to add a new DSN.
  6. In Create New Data Source, select the ODBC Driver for the Microsoft Access Database. Then, click OK.
  7. Next, enter a name for the data source. Then, click Select to specify the database that the DSN will represent.
  8. Once finished, click OK. Then, click OK again to close the ODBC Data Source Administrator.
  9. In the DSN drop-down menu, select the DSN that contains the Link Table.
  10. Next, select the Data Map tab. Then, click Browse to locate and select the items that will be logged to the spreadsheet.
  11. In Table Selection, select Log to an existing table. Then, use the Table Name drop-down menu to select the linked table that was added to the database.
  12. Next, select the Table Format for the table to which information will be logged. Narrow is for one tag per record and Wide is for multiple tags per record. For the linked table, the record would be a row.
  13. Click Map Fields to select the column/field to which each tag value will be logged. Then, select a Server Item Parameter and the Database Column with which it will be associated. Once finished, click Link to associate them.
  14. Repeat the process for each item to be logged.
  15. Once finished, click OK.
  16. Next, select the Triggers tab to specify how the data will be logged. By default, an Always Triggered trigger will be created for the log group that will log on data change or every 500 milliseconds. In this example, "Trigger" is set to log once every 10 seconds.
  17. Once finished, save the server project.

*The Excel sheet should have uniquely named column headers, since they will be used for the file names in the Link Table. DataLogger will use the field names to associate/map server item parameters.

Note 1: For more information, refer to the DataLogger product manual.

Note 2: DataLogger will open the data table for exclusive use, so the table will not be directly viewable.

Related Products
DataLogger, KEPServerEX