ohgod Posted March 26, 2008 Posted March 26, 2008 There is a file like this: "Date";"Indoor Temperature";"Indoor Humidity";"Outdoor Temperature 1";"Outdoor Humidity 1";"Outdoor Temperature 2";"Outdoor Humidity 2";"Outdoor Temperature 3";"Outdoor Humidity 3";"Outdoor Temperature 4";"Outdoor Humidity 4";"Outdoor Temperature 5";"Outdoor Humidity 5" "16.03.2008 18:05";"24.8";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---" "16.03.2008 18:10";"24.9";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---" "16.03.2008 18:15";"24.9";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---" "16.03.2008 18:20";"24.9";"47";"24.9";"45";"---";"---";"---";"---";"---";"---";"---";"---" ... Will anybody be so kind and paste a simple script that can populate an excel file with a file containing data like this? Or/And point out some resources for snippets/functions/examples regarding excel handling (I'm searching for an adaptable for 3 hours now ) Thank you very much
weaponx Posted March 26, 2008 Posted March 26, 2008 I'm not sure what you need. Option 1:Save the file as *.csv and then I open it Excel using File > Open > *.csv > Delimited > Semi-colonOption 2:Save the file as *.csv and then do:#include <File.au3>_ReplaceStringInFile ("test.csv", ";", ",")
PsaltyDS Posted March 26, 2008 Posted March 26, 2008 There is a file like this: "Date";"Indoor Temperature";"Indoor Humidity";"Outdoor Temperature 1";"Outdoor Humidity 1";"Outdoor Temperature 2";"Outdoor Humidity 2";"Outdoor Temperature 3";"Outdoor Humidity 3";"Outdoor Temperature 4";"Outdoor Humidity 4";"Outdoor Temperature 5";"Outdoor Humidity 5" "16.03.2008 18:05";"24.8";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---" "16.03.2008 18:10";"24.9";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---" "16.03.2008 18:15";"24.9";"47";"24.8";"45";"---";"---";"---";"---";"---";"---";"---";"---" "16.03.2008 18:20";"24.9";"47";"24.9";"45";"---";"---";"---";"---";"---";"---";"---";"---" ... Will anybody be so kind and paste a simple script that can populate an excel file with a file containing data like this? Or/And point out some resources for snippets/functions/examples regarding excel handling (I'm searching for an adaptable for 3 hours now ) Thank you very much The idea from weaponx is very usable. Convert it to a format that Excel can import directly (.csv). If you want to code something that works directly with the file and spreadsheet: 1. Read to an array with FileReadToArray(). 2. Break each line into another array of row data with StringSplit() 3. Write the row's data to the Excel spreadsheet with _ExcelWriteArray(). This last function is part of the _ExcelCOM_UDF.au3 UDF by Locodarwin. Writing code for you is not what happens here (mostly), but you'll find lots of help learning to do it yourself! Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
ohgod Posted March 26, 2008 Author Posted March 26, 2008 stringsplit works but I cannot make _ExcelWriteArray work. it doesnt do a thing #include<ExcelCOM_UDF.au3> #include <Array.au3> #include <file.au3> global $aRecords global $istartrow global $istartcolumn global $sordarab global $oExcel $_oExcel = _ExcelBookNew(1) If Not _FileReadToArray("trimmed.csv",$aRecords) Then MsgBox(4096,"Error", " Error reading log to Array error:" & @error) Exit EndIf For $x = 1 to 60 ConsoleWrite("Record:" & $x & " " & $aRecords[$x] & @CRLF) ; MsgBox(0,"Xesfor","Record:" & $x & " " & $aRecords[$x]) For $y = 1 to 13 $sordarab= StringSplit($aRecords[$x], ",") ConsoleWrite("SORDARAB:" & $y & " " & $sordarab[$y] & @CRLF) ;MsgBox(0,"ypsilon","Record:" & $y & " " & $sordarab[$y]) Next _ExcelWriteArray($oExcel, $y, $x, $sordarab,0,0) Next
PsaltyDS Posted March 26, 2008 Posted March 26, 2008 stringsplit works but I cannot make _ExcelWriteArray work. it doesnt do a thing #include<ExcelCOM_UDF.au3> #include <Array.au3> #include <file.au3> global $aRecords global $istartrow global $istartcolumn global $sordarab global $oExcel $_oExcel = _ExcelBookNew(1) If Not _FileReadToArray("trimmed.csv",$aRecords) Then MsgBox(4096,"Error", " Error reading log to Array error:" & @error) Exit EndIf For $x = 1 to 60 ConsoleWrite("Record:" & $x & " " & $aRecords[$x] & @CRLF) ; MsgBox(0,"Xesfor","Record:" & $x & " " & $aRecords[$x]) For $y = 1 to 13 $sordarab= StringSplit($aRecords[$x], ",") ConsoleWrite("SORDARAB:" & $y & " " & $sordarab[$y] & @CRLF) ;MsgBox(0,"ypsilon","Record:" & $y & " " & $sordarab[$y]) Next _ExcelWriteArray($oExcel, $y, $x, $sordarab,0,0) Next Your excel object has a stray "_" in it. Try it like this:#include<ExcelCOM_UDF.au3> #include <file.au3> Global $aRecords, $istartrow, $istartcolumn, $sordarab, $oExcel ; Read file to array If Not _FileReadToArray("trimmed.csv", $aRecords) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf ; Create instance of Excel $oExcel = _ExcelBookNew() ; Add each line of the file to a row of the spreadsheet For $x = 1 To 60 ; Split the line at the commas $sordarab = StringSplit($aRecords[$x], ",") ; Write the resulting array to the spreadsheet _ExcelWriteArray($oExcel, $x, 1, $sordarab, 0, 1) Next Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
junkew Posted March 26, 2008 Posted March 26, 2008 I do not understand why you would write this in AutoIt as its just a oneliner in an excel macro See below for AutoIt code (data.txt should be in %temp% folder, tested with excel 2003) $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object $oExcel.Visible = 1 ; Let Excel show itself ;~VBA code ;~Workbooks.OpenText Filename:= _ ;~ "%temp%\data.txt", Origin:= _ ;~ xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _ ;~ , ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:= _ ;~ False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) _ ;~ , Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ ;~ Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), TrailingMinusNumbers:=True $oExcel.Workbooks.OpenText(@tempdir & "\data.txt", 1, 1, 1, 2, False, False, True, False, False, False) sleep(4000) ;See the results for 4 seconds $oExcel.ActiveWorkBook.Saved = 1 ; Simulate a save of the Workbook $oExcel.Quit ; Quit Excel FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets
ohgod Posted March 26, 2008 Author Posted March 26, 2008 Your excel object has a stray "_" in it. Try it like this:#include<ExcelCOM_UDF.au3> #include <file.au3> Global $aRecords, $istartrow, $istartcolumn, $sordarab, $oExcel ; Read file to array If Not _FileReadToArray("trimmed.csv", $aRecords) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf ; Create instance of Excel $oExcel = _ExcelBookNew() ; Add each line of the file to a row of the spreadsheet For $x = 1 To 60 ; Split the line at the commas $sordarab = StringSplit($aRecords[$x], ",") ; Write the resulting array to the spreadsheet _ExcelWriteArray($oExcel, $x, 1, $sordarab, 0, 1) Next A HUGE thank you for this and all the help! altought it's really slow
PsaltyDS Posted March 27, 2008 Posted March 27, 2008 (edited) A HUGE thank you for this and all the help! altought it's really slow Slow...? It only processes 60 lines (your number). How long does it take when you run it? Update: I created a txt file from you original post with about 100 lines in it and every fifth line blank. Then I ran this against it: expandcollapse popup#include<ExcelCOM_UDF.au3> #include <file.au3> #include <array.au3> Global $aRecords, $sordarab, $oExcel, $iReadTime, $iDeleteTime, $iCreateTime Global $iWriteTime, $iTotalTime, $sFile = @ScriptDir & "\test.txt" ; Read file to array $iReadTime = TimerInit() If Not _FileReadToArray($sFile, $aRecords) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf $iReadTime = TimerDiff($iReadTime) ; Remove Blank lines $iDeleteTime = TimerInit() For $x = $aRecords[0] To 1 Step -1 If StringStripWS($aRecords[$x], 8) = "" Then _ArrayDelete($aRecords, $x) Next $iDeleteTime = TimerDiff($iDeleteTime) ; Create instance of Excel $iCreateTime = TimerInit() $oExcel = _ExcelBookNew() $iCreateTime = TimerDiff($iCreateTime) ; Add each line of the file to a row of the spreadsheet $iWriteTime = TimerInit() For $x = 1 To 60 ; Split the line at the commas $sordarab = StringSplit($aRecords[$x], ";") ; Write the resulting array to the spreadsheet _ExcelWriteArray($oExcel, $x, 1, $sordarab, 0, 1) Next $iWriteTime = TimerDiff($iWriteTime) ; Show times Global $sMsg = "All Times in ms:" & @CRLF & _ "Read to array: " & Round($iReadTime, 3) & @CRLF & _ "Delete blanks: " & Round($iDeleteTime, 3) & @CRLF & _ "Create Excel: " & Round($iCreateTime, 3) & @CRLF & _ "Write rows to excel: " & Round($iWriteTime, 3) & @CRLF & _ "Total time: " & Round($iReadTime + $iDeleteTime + $iCreateTime + $iWriteTime, 3) ConsoleWrite($sMsg & @LF) Results:>Running:(3.2.10.0):C:\Program Files\AutoIt3\autoit3.exe "C:\temp\Test\Test1.au3" All Times in ms: Read to array: 2.39 Delete blanks: 44.349 Create Excel: 181.923 Write rows to excel: 1762.052 Total time: 1990.714 +>11:59:29 AutoIT3.exe ended.rc:0 Now, "slow" is a relative term, but approx. 1.9sec to do the whole function doesn't seem out of line. Edited March 27, 2008 by PsaltyDS Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
junkew Posted March 27, 2008 Posted March 27, 2008 See my previous answer. If 1-2 seconds is not fast I would like to know what your definition of slow is. $oExcel.Workbooks.OpenText(@tempdir & "\data.txt", 1, 1, 1, 2, False, False, True, False, False, False) It takes just a second to read and convert it on my 1.4 GHZ machine. FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets
ohgod Posted March 27, 2008 Author Posted March 27, 2008 Yesterday I run into the mentioned $oExcel.Workbooks.OpenText so hopefully it can be tried today. the file to be imported into excel contains data from at least 5 colums, and a new row for every five minutes. (it's about weather monitoring) so that is gonna be a lot of records I'm sorry if I didn't make that clear. Excel is only needed because of it's graphing capability, I want to have the datas on a sheet and some pre-made data dependant diagrams/ on a different one, then email the one i need. maybe i have too big dreams hopefully, the excel COM udf, a mail sender that can deal with multiply attachments, and you guys will help me to achive it
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now