Getting data from an Autoit array onto Clipboard

[cd264]

Hi all, I'm pretty new to Autoit and very new to these forums, so please be gentle with me!

I have an autoit script which takes data from a number of text files (produced by a data logger). Example data is given below:

Timebase;Left;Right;

0.51000;-0.1563;-0.1718;

0.51025;-0.1556;-0.1663;

0.51050;-0.1495;-0.1614;

0.51075;-0.1477;-0.1532;

0.51100;-0.1385;-0.1526;

0.51125;-0.1392;-0.1514;

My script then reads ~4000 lines from the left and right channels into separate arrays ($Left and $Right). It then calculates $Left-$Right and $Left +$Right and places these into arrays.

I then use the _ArrayToClip() command to copy the array to the clipboard where I then paste it into excel (using $myobject.ActiveSheet.Paste). I do this for all four arrays and then repeat the whole process for the next text file. There could be up to 80 text files which need to be processed and placed into excel. -Speed is therefore quite important.

The good thing is that the above works.

It looks as though the slowest part is actually pasting each column into excel. What would make things faster is if I could create a multidimensional array (sorry about the terminology here, I have a VB/VBA background) for the data and paste it all in one go. (I could work around the Autoit array size limitations.) My problem is that whenever I try to copy/paste a multidimensional array into excel I get an error. Is there a better way to do this than using _ArrayToClip?

I have also used _ExcelWriteArray() from the EXCELCOM UDF but also get error messages when using multidimensional arrays.

Questions:

Am I doing it wrong?

Is there a better way to get multidimensional data either into Excel or onto the clipboard?

Are there any examples of code which does this?

My current code is listed below. I appreciate that it can be optimised much more but we all have to start somewhere and will improve the fourier transform bit later.

CODE

#Include <File.au3>

#Include <Array.au3>

#include <GUIConstants.au3>

;#include <EXCELCOM UDF.au3>

$filesuffix=".asc"

Dim $aRecords

Dim $szDrive, $szDir, $szFName, $szExt

dim $myobject

dim $excelPID

dim $x

dim $filenumber

dim $left[4099];[17];[$lines-9]

Dim $right[4099];[17];[$lines-9]

dim $LminusR[4099];[17];[$lines-9]

dim $LplusR[4099];[17];[$lines-9]

dim $hFilesFolders

dim $rows

;******************************************************************

;Automatic version, processes all files in the script directory.

;get the dirve and directory details

$TestPath = _PathSplit(@ScriptFullPath, $szDrive, $szDir, $szFName, $szExt)

$mydrive=$szdrive; these bits are legacy really to feed the correct info into parsefile.

$mydirectory=$szdir; a variable like $path would probably have been better.

;find the files of defined type in the script directory

$FileList=_FileListToArray(@scriptdir & "\","*"& $filesuffix,1)

If @Error=1 Then; check that the file exists

MsgBox (0,"","No Files\Folders Found.")

Exit

EndIf

;make progress bars GUI

GUICreate("Processing Softness Meter Data",420,100,-1,-1,-1)

GUICtrlCreateLabel("Overall Progress",20,10,100)

GUICtrlCreateLabel("File Reading",20,40,100)

GUICtrlCreateLabel("File Writing",20,70,100)

$allfiles = GUICtrlCreateProgress (180,10,200,20,$PBS_SMOOTH)

$singlefile = GUICtrlCreateProgress (180,40,200,20,$PBS_SMOOTH)

$writing = GUICtrlCreateProgress (180,70,200,20,$PBS_SMOOTH)

GUISetState ()

doexcel(1,"");My function to start a copy of excel so that all addins are loaded

; so that a fourier analysis can be run.

for $n=1 to ubound($filelist)-1;this section parses the datafiles

$filename=$filelist[$n] ;i'm sure I have a good reason for this line!

GUICtrlSetData ($writing,0);resets the file-write progress bar

$filepathname=$mydrive & $mydirectory & $filename

$lines=_FileCountLines($filepathname)

$rows=0

If Not _FileReadToArray($filename,$aRecords) Then ;check that the file has been loaded correctly

MsgBox(4096,"Error", " Error reading log to Array error:" & @error)

Exit

EndIf

For $x = 258 to 4354;reads the required rows to build the FT dataset then put into excel

GUICtrlSetData ($singlefile,($x-258)/4096*100);progress bar

$aa=StringRegExp($aRecords[$x],";-{0,1}[0-9]{1,3}.[0-9]{1,7};",1) ;LEFT

$bb=StringRegExp($aRecords[$x],";-{0,1}[0-9]{1,3}.[0-9]{1,7};\z",1) ;RIGHT

for $j=0 to UBound($aa)-1; do the maths then run function to place in excel

$left[$rows]=stringmid($aa[$j],2,stringlen($aa[$j])-2)

$right[$rows]=stringmid($bb[$j],2,stringlen($bb[$j])-2)

$LminusR[$rows]=$left[$rows]-$right[$rows]

$LplusR[$rows]=$left[$rows]+$right[$rows]

$rows=$rows+1

Next

Next

_ArrayToClip($left);copy to clipboard

doexcel(2,"Left");##################### put the data into excel ###########

_ArrayToClip($right)

doexcel(2,"right");

_ArrayToClip($LplusR)

doexcel(2,"LPR");

_ArrayToClip($LminusR)

doexcel(2,"LMR");

guictrlsetdata($allfiles,$n/(ubound($filelist)-1)*100)

Next

;more excel stuff

doexcel(3,"");do the FT stuff

func doExcel($section,$Channel)

;This function looks after all the excel processing.

;The $Section string dictates which part of the process to run.

Switch $section

case 1;initialiation of the excel sheets

;write directly into an excel sheet.

;make the excel sheet

;check that excel is running

splashtexton("","Loading Excel",120,30,-1,-1,1)

$excelup=ProcessExists("excel.exe")

if $excelup=0 Then

;start excel

shellexecute("excel.exe","","","open",@SW_HIDE )

sleep(10000) ;pauses while excel opens

$excelPID=ProcessExists("excel.exe")

Else

$excelPID="Excel Up Already"

endif

$MyObject = Objget("","Excel.Application"); Open an Excel Object NEEDS EXCEL TO BE OPEN FIRST

If @error Then

MsgBox(0, "Excel Error:", "Error creating Excel object!")

Exit

EndIf

If Not IsObj($MyObject) Then

MsgBox(16, "Excel Error:", "Error creating Excel object!")

Exit

EndIf

$myobject.application.screenupdating=false

$myobject.Application.Calculation = -4135

$myobject.Application.EnableEvents = False

$MyObject.Visible = 0

$MyObject.workbooks.add

$MyObject.Sheets("Sheet1").Select

;$myobject.range("A1").Select

SplashOff()

case 2;writes data into cells

$MyObject.Sheets("Sheet1").Select

switch $Channel

case "Left"

$myobject.range("A2").Select

$myobject.activecell.offset(0,$n-1).Select

$myobject.ActiveSheet.Paste

case "Right"

$myobject.range("AA2").Select

$myobject.activecell.offset(0,$n-1).Select

$myobject.ActiveSheet.Paste

case "LPR"

$myobject.range("BA2").Select

$myobject.activecell.offset(0,$n-1).Select

$myobject.ActiveSheet.Paste

case "LMR"

$myobject.range("CA2").Select

$myobject.activecell.offset(0,$n-1).Select

$myobject.ActiveSheet.Paste

EndSwitch

$myobject.activecell.offset(-1,0).value= stringleft($filename,stringlen($filename)-4) ;add the file name to the top of the col

case 3;do FT calculations

;create integrals

$myobject.range("A4098").formula="=sum(A2:A4097)"

$myobject.activesheet.range("A4098").Select

$myobject.selection.copy

$selectionrange="b4098:" & chr(65+ubound($filelist)-2) & "4098"

$myobject.ActiveSheet.range($selectionrange).select

$myobject.ActiveSheet.Paste

;get to bottom of the table

$myobject.range("A4100").Select

$myobject.activecell.offset(-1,0).value="Mean"

$myobject.activecell.offset(-1,1).value="FT OUTPUT"

$myobject.activecell.offset(-1,2).value="Absolute Value"

$myobject.activecell.offset(-1,3).value="Integral"

$myobject.activecell.offset(-1,4).value="Frequency"

$myobject.ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4098]C:R[-4098]C["&$n-2 & "])"

$myobject.Range("A4100").Select

$myobject.Selection.Copy

$myobject.Range("A4101:A8195").Select

$myobject.ActiveSheet.Paste

$myobject.Application.Calculation = -4105

$myobject.Application.Run ("ATPVBAEN.XLA!Fourier", $myobject.ActiveSheet.Range("$A$4100:$A$8195"),

$myobject.ActiveSheet.Range("$B$4100:$B$8195"), False, False)

$myobject.range("c4100").formula="=imabs(B4100)"

$myobject.range("c4101").formula="=imabs(B4101)"

$myobject.range("d4100").formula="=c4100"

$myobject.range("d4101").formula="=c4101+d4100"

$myobject.range("e4100").formula="0"

$myobject.range("e4101").formula="=e4100+4000/4096"

$myobject.Range("c4101:e4101").Select

$myobject.Selection.Copy

$myobject.Range("c4102:e6148").Select

$myobject.ActiveSheet.Paste

;now do the summary table

$myobject.range("G4100").value="FINAL OUTPUT"

$myobject.range("G4101").value="Mean"

$myobject.range("G4102").value="Stdev"

$myobject.range("G4103").value="Stdev/Mean"

$myobject.range("G4104").value="Value/Datapoint"

$myobject.range("h4101").formula="=average(a4098:" & chr(65+ubound($filelist)-2) & "4098)"

$myobject.range("h4102").formula="=Stdev(a4098:" & chr(65+ubound($filelist)-2) & "4098)"

$myobject.range("h4103").formula="=abs(h4102/h4101)"

$myobject.range("h4104").formula="=h4103*sqrt(4096)"

$myobject.range("G4107").value="Frequency"

$myobject.range("h4107").value="Integral"

$myobject.range("i4107").value="Change of integral"

$myobject.range("j4107").value="Error Estimate"

;1

$myobject.range("G4108").formula="=E4100"

$myobject.range("h4108").formula="=D4100"

$myobject.range("i4108").formula="=D4100"

$myobject.range("j4108").formula="=100*h4103"

;2

$myobject.range("G4109").formula="=E4162"

$myobject.range("h4109").formula="=D4162"

$myobject.range("i4109").formula="=h4109-h4108"

$myobject.range("j4109").formula="=100*h4104/sqrt(G4109-G4108)"

;3

$myobject.range("G4110").formula="=E4203"

$myobject.range("h4110").formula="=D4203"

$myobject.range("i4110").formula="=h4110-h4109"

$myobject.range("j4110").formula="=100*h4104/sqrt(G4110-G4108)"

;4

$myobject.range("G4111").formula="=E4305"

$myobject.range("h4111").formula="=D4305"

$myobject.range("i4111").formula="=h4111-h4110"

$myobject.range("j4111").formula="=100*h4104/sqrt(G4111-G4108)"

;5

$myobject.range("G4112").formula="=E4510"

$myobject.range("h4112").formula="=D4510"

$myobject.range("i4112").formula="=h4112-h4111"

$myobject.range("j4112").formula="=100*h4104/sqrt(G4112-G4108)"

;6

$myobject.range("G4113").formula="=E4920"

$myobject.range("h4113").formula="=D4920"

$myobject.range("i4113").formula="=h4113-h4112"

$myobject.range("j4113").formula="=100*h4104/sqrt(G4113-G4108)"

;7

$myobject.range("G4114").formula="=E6148"

$myobject.range("h4114").formula="=D6148"

$myobject.range("i4114").formula="=h4114-h4113"

$myobject.range("j4114").formula="=100*h4104/sqrt(G4114-G4108)"

;copy the block of calculations to the 3 other positions AA BA CA

;then run the FTs for these datasets.

$myobject.range("A4098:J8196").Select

$myobject.Selection.Copy

$myobject.range("AA4098").Select

$myobject.ActiveSheet.Paste

$myobject.range("BA4098").Select

$myobject.ActiveSheet.Paste

$myobject.range("CA4098").Select

$myobject.ActiveSheet.Paste

$myobject.range("Ab4099:AB8195").clear

$myobject.range("Bb4099:BB8195").clear

$myobject.range("Cb4099:CB8195").clear

;do the FTs

;Right

$myobject.Application.Run ("ATPVBAEN.XLA!Fourier", $myobject.ActiveSheet.Range("$AA$4100:$AA$8195"), $myobject.ActiveSheet.Range("$AB$4100:$AB$8195"), False, False)

;LPR

$myobject.Application.Run ("ATPVBAEN.XLA!Fourier", $myobject.ActiveSheet.Range("$BA$4100:$BA$8195"), $myobject.ActiveSheet.Range("$BB$4100:$BB$8195"), False, False)

;LMR

$myobject.Application.Run ("ATPVBAEN.XLA!Fourier", $myobject.ActiveSheet.Range("$CA$4100:$CA$8195"), $myobject.ActiveSheet.Range("$CB$4100:$CB$8195"), False, False)

;rename the sheet

$myobject.sheets("Sheet1").name=stringleft($filename,stringlen($filename)-6)

;make visible

$myobject.application.screenupdating=true

$myobject.Application.EnableEvents = True

$MyObject.Visible = 1

$MyObject = 0 ;release excel

;sleep(10000);wait and close

;if $excelPID<>"Excel Up Already"then processclose($excelPID);kills all of excel without saving anything!

EndSwitch

endfunc

Many thanks for reading so far.

[Locodarwin]

I have tried unsuccessfully to paste multidimensional arrays into Excel.

Why not load those text files directly into Excel with the OpenText() workbook method, using the colon as a delimiter? You could then relatively quickly make & paste your calculations.

$MyObject.Workbook.OpenText("C:\logfile.txt", Default, Default, Default, Default, Default, False, False, False, False, True, ";")

Give something like that a go.

-S

[cd264]

Many thanks for the reply.

I was going to do it all in Excel VBA as this would have had a shallower learning curve, however there is an issue that the text files can be very very large, enough to knock out excel. Though this is currently not a problem but we are getting close, if I work to that limitation, my colleagues will need it exceeded!

Also the preprocessing is likely to be used in another analysis technique, so if I do the calculations within autoit, I can fork the output to whatever technique I need to run.

Actually with one or two tweaks I am getting the code quite efficient.

I have an idea that instead of making the array multidimensional, I keep it in a single dimension and add tabs (or commas etc) to delimit it:

$myarray[$x]=$myarray[$x] & chr(9) & $MyNextField

If I paste that into Excel, I should be able to convert text to columns.

What do you think? Will it work?