02-20-2017 07:55 AM
Hi,
I often save TDM files in Excel format and usually just include the channel name and unit. However, since I usually have multiple groups, I then have to manually insert the group name into the Excel file so that the customers can understand which groups the channels belong to.
I assume it might be possible to automate this using a script to save to Excel whilst inserting the group name into row 1, followed by the channel name in row 2 and the unit in row 3, as normal.
Can anyone guide me as to how I might go about this?
Thanks.
02-27-2017 08:39 AM
Hi,
when opening a TDM file with the Excel Importer, channels are separated by group.
http://www.ni.com/example/27944/en/
Description: "Each group in the TDM/TDMS file is loaded onto a separate Excel Worksheet"
After importing it in Excel you can save it in Excel file format.
Regards
02-27-2017 09:42 AM
Thanks, I will review this.
However, I was intending to have all the channels on the same sheet, with the group name in row 1. Is that possible?
Regards,
Simon.
02-27-2017 11:00 AM - edited 02-27-2017 11:06 AM
As far as I know there is no way to tell the Excel Importer to put all channels from different groups in one worksheet.
Here are some alternatives that I know:
- post process your data with a VBA macro
- if there is no Excel installed on the computer that must create the Excel file, you can use the FileSystemObject to write a text (csv) file with your structure and change the file extension to .xls. When opening it in Excel it should be interpreted automatically.
- if there is Excel installed you can use the Excel COM interface in DIAdem to build an excel file with your structure. Here's an example that writes all channels from data portal to excel in one worksheet including group name:
dim i,j,oExcel,oWS,oChn
set oExcel = CreateObject("Excel.Application")
call oExcel.Workbooks.Add()
set oWS = oExcel.ActiveSheet
for i=1 to ChnNoMax
set oChn = Data.GetChannel(i)
oWS.Cells(1,i).Value = oChn.Name
oWS.Cells(2,i).Value = oChn.UnitSymbol
oWS.Cells(3,i).Value = oChn.Properties("groupname").Value
'the following loop can take very long time depending on the channel size!!!
for j=1 to oChn.Size
oWS.Cells(j+3,i).Value = oChn(j)
next
next
call oExcel.ActiveWorkbook.SaveAs(CurrentScriptPath & "Data in Excel")
call oExcel.Quit()
set oExcel = nothing
Be careful as this can take very long time for very long channels.
Regards
02-28-2017 08:21 AM
This is much faster and should do the job 😉
dim i,j,oExcel,oWS,oChn
set oExcel = CreateObject("Excel.Application")
call oExcel.Workbooks.Add()
set oWS = oExcel.ActiveSheet
for i=1 to ChnNoMax
set oChn = Data.GetChannel(i)
oWS.Cells(1,i).Value = oChn.Name
oWS.Cells(2,i).Value = oChn.UnitSymbol
oWS.Cells(3,i).Value = oChn.Properties("groupname").Value
oWS.Range(oWS.Cells(4,i), oWS.Cells(3+oChn.Size,i)) = oExcel.Transpose(oChn.GetValuesBlock(1,oChn.Size,eValueBlockSafeArray))
next
call oExcel.ActiveWorkbook.SaveAs(CurrentScriptPath & "Data in Excel")
call oExcel.Quit()
set oExcel = nothing
02-28-2017 08:23 AM
Thanks Christian. I'll check it out.
Regards.