04-24-2017 01:53 AM - edited 04-24-2017 01:55 AM
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.
Solved! Go to Solution.
04-24-2017 04:58 AM
There are two ways to do the job
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, "&", "&") & "</filename><decimalpoint>.</decimalpoint><delimiter>,</delimiter>", "CSV")
04-25-2017 12:49 PM
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 ?
04-26-2017 01:46 AM
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.