writing channel average result to Excel (*.csv)

[BSN]

Hi Guys,

I am new at DIAdem and I want to import data from Excel (*.csv file) as channels and take average of each channel and write the result to the end row of same Excel (*.csv file).

I have added a sample (*.csv file) of which i am importing to diadem and making channels. I have to take average of each channel and write the result at the 104th row of this file. I am making a general program that automatically imports this file and calculates the average of each channel and writes it back to the end of this file. For averaging, I am using the descriptive analysis tool of diadem and I am running this in a loop but I am unable to write my averaged result back to this file. Please guide me as I am new in Diadem.

[AndreasK · Accepted solution]

There are two ways to do the job

1. Append text to the text file
2. Rewrite the file using the CSV plugin

1. If you want to append to an File just open it and append the values in Text mode.

Option Explicit

dim filepath : filepath = "C:\temp\FL_insurance_sample.csv"

data.Root.Clear

call DataFileLoad(filepath, "CSV")
dim ArithmeticMeanCh : set ArithmeticMeanCh = ChnStatisticsChannelCalc("'[1]/policyID' - '[1]/point_longitude'",32,0,0,0,1,0,"NameName")(1)

' Create Line to append
dim newLine : newLine = ""
dim i : for i = 1 to ArithmeticMeanCh.size
  ' add separator for following values
  if 1 <> i then newLine = newLine & ","
  ' Make sure double is formated with dot as decimal point
  newLine = newLine & Str(ArithmeticMeanCh(i),"d.dd")
Next

' make sure file is not blocked by DIAdem
data.Root.Clear

' open the file and append a new line
dim fh : fh = TextFileOpen(filepath, eTextFileAttributeANSI OR eTextFileAttributeWrite, "CRLF")
call TextFileWriteln(fh, newLine)
call TextFileClose(fh)

2. It is also possible to resize the channels and resave the file using the correct CSV settings.

Option Explicit

dim filepath : filepath = "C:\temp\FL_insurance_sample.csv"

data.Root.Clear

call DataFileLoad(filepath, "CSV")
dim ArithmeticMeanCh : set ArithmeticMeanCh = ChnStatisticsChannelCalc("'[1]/policyID' - '[1]/point_longitude'",32,0,0,0,1,0,"NameName")(1)

dim i : for i = 1 to ArithmeticMeanCh.size
  dim chO : set chO = data.Root.ChannelGroups(1).Channels(i)
  chO.Values(chO.Size + 1) = ArithmeticMeanCh(i)
Next
data.Root.ChannelGroups(1).Channels.Remove(ArithmeticMeanCh.Name)

Call DataFileSave("<filename>" & replace(filepath, "&", "&amp;") & "</filename><decimalpoint>.</decimalpoint><delimiter>,</delimiter>", "CSV")

[BSN]

Thank you AndreasK,

I tried it and it works, there is one problem though that this is not general program to calculate averages of data channel as I have to write channel names every time like in this line of code

dim ArithmeticMeanCh : set ArithmeticMeanCh = ChnStatisticsChannelCalc("'[1]/policyID' - '[1]/point_longitude'",32,0,0,0,1,0,"NameName")(1)

and this code saves data channels to csv file only. Do you know a way to export data channels to Excel (.xls) file ?

[AndreasK]

dim grpO : set grpO = data.Root.ChannelGroups(1)
dim chnsString : chnsString = "'" & grpO.Name & "/[1]' - '" & grpO.Name & "/[" & grpO.Channels.count & "]'"

dim ArithmeticMeanCh : set ArithmeticMeanCh = ChnStatisticsChannelCalc(chnsString,32,0,0,0,1,0,"NameName")(1)

will calculate all channels of group one instead.

I know no easy way to easily modify xls files. If Excel is installed it is possible to use Excel automation to modify data inside of Exel but CSV is easy because it is a simple text file.