Have data in Simcenter Testlab (formerly called LMS Test.Lab) that you want to export to Excel? Have data in Excel that you want to import to Simcenter Testlab? Luckily, it’s easy!
There are multiple options available in both Simcenter Testlab and Simcenter Testlab Neo: 1. Display Export to Excel (both Simcenter Testlab and Simcenter Testlab Neo) 2. Excel Data Driver (both Simcenter Testlab and Simcenter Testlab Neo) 3. ASCII and CSV Files (both Simcenter Testlab and Simcenter Testlab Neo) 4. Data Block Editor (Simcenter Testlab "Classic") 5. Export to Excel Method (Simcenter Testlab Neo)
1. Display Export to Excel (both Simcenter Testlab and Simcenter Testlab Neo)
To export data to Excel, right click on a curve in a display, select “Copy Values” and then select “All” (Figure 1).
Figure 1: Copy the values directly from the plot.
Open Excel and paste the data. The first few dozen rows (Figure 2) that are pasted contain header information: details like timestamps, weighting, and datatype etc. This is to preserve information about the data even if it is moved into Excel.
Figure 2: Paste the data into Excel. The first lines are header information (engineering unit, function class, etc).
Below the header is the numerical data as shown in Figure 3.
Figure 3: In the Excel sheet, below the header information, is the numerical data.
It is also possible to copy multiple curves from a display. Select multiple curves (hold control key and click on curves), then right click, “Copy Values”, and select “All” as shown in Figure 4.
Figure 4: Copy multiple curves from a display.
To copy just a subset of the data instead of the entire curve, zoom in on a narrower frequency range, right click, “Copy Values”, and select “Between X Limits” as shown in Figure 5.
Figure 5: Copy range of values instead of the whole curve.
Data in the legend calculated by a double X cursor can also be copied into Excel as shown in Figure 6.
Figure 6: Copy values from a cursor legend.
Save the Excel file. It is possible to browse to the data in the Excel file from Simcenter Testlab as described in the next section.
Export to Excel from a display works the same in both Simcenter Testlab "Classic" and Simcenter Testlab Neo.
2. Excel Data Driver in Testlab (both Simcenter Testlab and Simcenter Testlab Neo)
Simcenter Testlab can read data directly from Excel. This works the same in both Simcenter Testlab "Classic" and Simcenter Testlab Neo.
To read data into Simcenter Testlab from Excel, the Excel file must have a specific header. There are two options to create a Excel file with a proper header:
Export data from Testlab: Export data of the desired type (spectrum, time, octave, etc) from Testlab to Excel to create a file with the proper header..
FileTypes Example file: Use one of the examples in the "FileTypes.xls" file in the zip file attached to this article.
Once a starter Excel file is created with either method above, modify the Excel file to have the desired data. The two methods are explained in further detail below:
2.1 Export Data from Testlab to Excel
Browse to the data in the navigator tree on the left side of the Navigator workbook. Select the Excel file saved in the previous section and the data block should populate in the middle pane. Drag the data block to the display to plot it (Figure 7).
Figure 7: Browse to the Excel file (and sheet) to view in Simcenter Testlab.
It is important that the Excel data being imported has a header. The header has critical information about amplitude format, weighting, and more. Without the proper header information, the Excel data will not import into Simcenter Testlab. By exporting a similar block of data to what will be imported, it ensures the header is good.
2.2 FileTypes Example Excel file
Attached to this article is an example Excel file (FileTypes1.xls) with headers for most popular data types (Figure 8).
Figure 8: The example Excel file "FilesTypes.xls" attached to this article contains multiple example files.
Different measurement function types (octaves, overall level, time, spectrum, etc) are on the different worksheets (tabs along the bottom) within the example file.
The numerical values in the file can then be edited while keeping the Excel header information the same. A sample Excel file with two examples of importing time data is attached to this article. It has the following reduced header showing that either sample frequency or X axis increment can be used:
Curve 1
Curve 2
Function\Point id
Point1
Function\Point id
Point2
Function\Point direction
+X
Function\Point direction
+X
Function\Function class
Time
Function\Function class
Time
Function\Sample frequency
10
Function\X axis increment
0.100000000000
s
g
s
g
Linear
Real
Linear
Real
-10.0000
-0.2809
-10.0000
0.2599
-9.9000
0.2222
-9.9000
0.0084
NOTE: Simcenter Testlab Version 18 and higher has Excel Data driver functionality integrated into the software by default. Previous versions require turning on the "Excel Data Driver" in the "Add-in" list of Simcenter Testlab.
2.3 Excel *.xls versus *.xlsx
The amount of data that can be contained in a block is limited by the type of Excel file:
32,768: If a *.xls file is used, the data block can contain 32,768 data points.
1,048,576: If a *.xlsx file is used, the data block can contain 1,048,576 data points.
3. ASCII and CSV Files (both Simcenter Testlab and Simcenter Testlab Neo)
There are additional options to read time data into Simcenter Testlab from files containing Comma Separated Values (CSV) and American Standard Code for Information Interchange (ASCII) values.
To read time data from these types of files, the file extension must be *.asc. It cannot be *.csv or any other extension.
The values to be read can be delimited with a comma (",") or "Tab" between values. A header with a specific format needs to be added to the file so Simcenter Testlab will read the time history (Figure 9).
Figure 9: Example *.asc file with header contained between BEGIN and END. Comma separated values follow after the END line.
The headers fields are contained between a BEGIN and END lines. Possible header fields include:
RATE: Number of samples per second
DELTA: If RATE is not used, DELTA is the time between samples.
UNIT: The engineering unit for the data. See the knowledge article Simcenter Testlab Units for valid values.
CHANNELNAME: Sets the Point Id or main identifier field for the data channel.
EDA_AbsoluteTime: Sets a absloute time (second, day, month, year) same as IRIG or GPS would.
If neither DELTA nor RATE is specified the data will be assigned a sample rate of 1 Hz (1 sample per second) in Simcenter Testlab.
Instead of adding a header at the beginning of the file it can be a separate file of the same name in the same directory as the *.asc file. For example, filename.header and filename.asc.
There are example *.asc files in the zip file attached to this article.
4. Data Block Editor (Simcenter Testlab "Classic")
The Data Block Editor is another option to import data from Excel (or via manual entry) into Simcenter Testlab. It is available in Simcenter Testlab "Classic". It is not a feature of Simcenter Testlab Neo.
Data Block Editor can be turned on using "Tools -> Add-ins" from main Testlab menu. No additional tokens are necessary. This is shown in Figure 10.
Figure 10: Tools -> Add-ins -> Data Block Editor.
Go to the Data Block Editor workbook (Figure 11). Here, the user can set the appropriate information necessary when importing to Testlab from Excel.
Figure 11: Data Block Editor Workbook.
In the Data Block Editor, it is possible to set the function type, format, amplitude scaling, x-axis increment information, Point ID, point direction, y axis unit, and weighting. This is done in the upper left of the menu.
After setting these parameters the user can simply paste their data into the table on the right side of the screen and the picture filling the bottom half of the screen will populate with data.
To save this to your project, select “Add to Workspace” and then “Save workspace in active project” (both buttons on the right side of workbook).
Select the folder in which to save the data (or create a new folder) and select “OK” (Figure 12).
Figure 12: Saving the imported data to the project.
The data will be saved into the project in the specified folder as shown in Figure 13.
Figure 13: The data was saved into the project and can be plotted / processed as necessary.
The Data Block Editor can only be used to import one block of data at a time. The Data Block Editor is limited to 32,768 data points per block/function.
Even though this feature is only available in Simcenter Testlab "Classic", the data block that is generated can be used Simcenter Testlab Neo or Simcenter Testlab "Classic".
Figure 14: The "Report to Excel" method can be the end result of any process.
The "Report to Excel" method can be incorporated into any process. By incorporating it, multiple data sets can be automatically processed and results sent to Microsoft Excel automatically.
The windows file folder to store the resulting Excel file is one of the options in the "Report to Excel" method.
If desired, use the "Data" ribbon of Excel and "Get Data" to monitor (Figure 15) a windows file folder to automatically update bar charts, line graphs, and other reports in Microsoft Excel.
Figure 15: Use "Get Data" in the "Data" ribbon of Excel to monitor automatically monitor a file folder.
After selecting a folder to monitor, the "Power Query Editor" to "Combine and Transform" multiple Excel sheets into a single report (Figure 16).
Figure 16: The "Combine and Transform" feature of the Excel "Power Query Editor" can be used to generate a single Excel report from multiple Excel files.
The "Power Query Editor" allows users to clean the headers of the Excel file, identify data values to be plotted, and combine multiple files into one result. More information on using the "Power Query Editor" of Microsoft Excel in the movie below:
