abdulrahmanok Posted August 25, 2017 Share Posted August 25, 2017 (edited) Welcome all, Firstly that's my code: #include <Excel.au3> #include<file.au3> ProcessClose("Excel.exe") ;;;;;;;;;;;;;; Compare Between Calc And DaysArray And Add "None" $aArray = FileReadToArray(@scriptdir & "\Calc.txt") If @error Then Else $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $file2 = @scriptdir & "\calc.txt" Global $TextFromCalc If Not _FileReadToArray($file2, $TextFromCalc, 0) Then MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the Calc : " & @error) ; An error occurred reading the current script file. EndIf Local $oWorkbook = _Excel_BookOpen($oExcel, @scriptdir & "\Excel.xlsx") Sleep(100) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $TextFromCalc+1, "C2") EndIf "Calc.txt" contains: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 I want to make excel write in C2 raw the "Calc" file but bypass lines like this: 2 4 6 .... I will be here for any declaration Edited August 28, 2017 by abdulrahmanok Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted August 25, 2017 Moderators Share Posted August 25, 2017 Is the calc.txt always going to have sequential numbers? Rather than reading a file into one array, and then trying to extract all even/odd numbers, you could simply add only those that meet the criteria to the array in the first place: #include <Array.au3> Local $aArray[0] For $a = 1 To 10 If Mod($a, 2) = 0 Then _ArrayAdd($aArray, $a) Next _ArrayDisplay($aArray) If that is not what you are after, please explain the criteria for what lines you would want to skip "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 25, 2017 Author Share Posted August 25, 2017 thanks for reply and about " please explain the criteria for what lines you would want to skip " I want to start reading lines from Line 2 and then skip line each time like this: Line 2 "skip line 3" Line 4 "skip line 5" Line 6 skip line 7 ... and after then put the final array values to Excel (C2 raw) Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted August 25, 2017 Moderators Share Posted August 25, 2017 Ok, now to the other piece of my question - will the text file actually contain numbers on each line, or was this just an example? "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 25, 2017 Author Share Posted August 25, 2017 (edited) yes it was example and this is some edit on your code : $aArray = FileReadToArray(@scriptdir & "\Calc.txt") If @error Then Else $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $file2 = @scriptdir & "\calc.txt" Local $aInput $file = "calc.qu" _FileReadToArray($file, $aInput) For $i = 1 to UBound($aInput) -1 if Mod($i, 2) = 0 Then _ArrayAdd($aInput, $i) ConsoleWrite(@CRLF&$aInput[$i]) Next _ArrayDelete($aInput,$aInput) ;Empty pervius values _ArrayDisplay($aInput) Local $oWorkbook = _Excel_BookOpen($oExcel, @scriptdir & "\Excel.xlsx") Sleep(100) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aInput, "C2") EndIf but for some reason Excel output like this: 16 2 ( I want Start from here and ignore previous line) 4 6 8 10 12 14 16 Edited August 25, 2017 by abdulrahmanok Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 25, 2017 Author Share Posted August 25, 2017 is there is anyway to ignore reading array[0] value ? Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted August 25, 2017 Moderators Share Posted August 25, 2017 Instead of this: For $i = 1 To Ubound($aInput) -1 try this: For $i = 1 To $aInput[0] "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 25, 2017 Author Share Posted August 25, 2017 after some search I ended up with this: #include <Excel.au3> #include<file.au3> ProcessClose("Excel.exe") ;;;;;;;;;;;;;; Compare Between Calc And DaysArray And Add "None" $aArray = FileReadToArray(@scriptdir & "calc.txt") If @error Then Else $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $file2 = @scriptdir & "\calc.txt" Local $aInput $file = "calc.txt" _FileReadToArray($file, $aInput) For $i = 1 to UBound($aInput) -1 if Mod($i, 2) = 0 Then _ArrayAdd($aInput, $i) ConsoleWrite(@CRLF&$aInput[$i]) Next _ArrayDelete($aInput,$aInput) ;Empty pervius values _ArrayDelete($aInput,0) ;Delete First Value _ArrayDisplay($aInput) Local $oWorkbook = _Excel_BookOpen($oExcel, @scriptdir & "\Excel.xlsx") Sleep(100) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aInput, "C2") EndIf and it's worked perfectly , I will try your methods now Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 25, 2017 Author Share Posted August 25, 2017 3 minutes ago, JLogan3o13 said: Instead of this: For $i = 1 To Ubound($aInput) -1 try this: For $i = 1 To $aInput[0] Unfortunately still getting (16) value Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted August 25, 2017 Moderators Share Posted August 25, 2017 Why are you reading the file to an array twice? Once here: $aArray = FileReadToArray(@scriptdir & "calc.txt") And then again here: _FileReadToArray($file, $aInput) Why not read it to array once and then manipulate? I also see that you are declaring your calc.txt file multiple times, both inside variables and out: FileReadToArray(@scriptdir & "calc.txt") $file2 = @scriptdir & "\calc.txt" $file = "calc.txt" Makes it a lot easier if you simply declare to a variable once and then use that throughout. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 25, 2017 Author Share Posted August 25, 2017 (edited) Thanks for your patience , Unfortunately after copy all of this to my real program it doesn't work as expected : Code: ProcessClose("Excel.exe") ;;;;;;;;;;;;;; Compare Between DaysArray And DaysArray And Add "None" If @error Then Else $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 Local $aInput $file = "DaysArray.txt" _FileReadToArray($file, $aInput) For $i = 1 To UBound($aInput) - 1 If Mod($i, 2) = 0 Then _ArrayAdd($aInput, $i) ConsoleWrite(@CRLF & $aInput[$i]) Next _ArrayDelete($aInput, $aInput) ;Empty pervius values ; _ArrayDelete($aInput, 0) ;Delete First Value _ArrayDisplay($aInput) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel.xlsx") Sleep(100) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aInput, "C2") EndIf "DaysArray.txt" contains: Your Hours For 01-Aug-17 Is : 4.5 Your Hours For 03-Aug-17 Is : 2.5 Your Hours For 06-Aug-17 Is : 4 Your Hours For 08-Aug-17 Is : 3 Your Hours For 09-Aug-17 Is : 5 Your Hours For 10-Aug-17 Is : 7 Should I put this in new thread? Edited August 25, 2017 by abdulrahmanok Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted August 25, 2017 Moderators Share Posted August 25, 2017 No stick to a single thread. How about posting a real example of the DaysArray.txt, and what exactly you expect to be written to Excel? All of the "just for example" data makes it a bit confusing. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
junkew Posted August 26, 2017 Share Posted August 26, 2017 Use a regular expression to parse the lines you need FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
water Posted August 26, 2017 Share Posted August 26, 2017 Some additional questions: Why do you manually start up Excel and then use the Excel UDF functions? 11 hours ago, abdulrahmanok said: $oExcel = ObjCreate("Excel.Application") I would stick to the Excel UDF which has additonal error handling. Why do you close the Excel process? _Excel_Open simply connects to a running instance or start a new one if needed. Just my 2 cents to improve your script My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 26, 2017 Author Share Posted August 26, 2017 the real "DaysArray" attached . I expect to see in Excel "C2" raw: 4.5 2.5 4 3 5 7 Why do you manually start up Excel and then use the Excel UDF functions? because this is part of my main program and it give user details in Excel when call Excel Func so must open file first and make it visible. Why do you close the Excel process? because I got some error's when keep it running and try to save overwrite it. DaysArray.txt Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 26, 2017 Author Share Posted August 26, 2017 4 hours ago, junkew said: Use a regular expression to parse the lines you need I didn't understand what do you mean. Link to comment Share on other sites More sharing options...
junkew Posted August 26, 2017 Share Posted August 26, 2017 Something like this Attention point is the last line, not sure if you have CRLF there try also $strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*?\r\n),"$2,") #include <Excel.au3> #include<file.au3> $fileToRead="C:\autoit_test\DaysArray.txt" ; Read the contents of the file using the handle returned by FileOpen. Local $strFileRead = FileRead($fileToRead) consolewrite("Initial file contents" & @CRLF) consolewrite("=====================" & @CRLF) consolewrite($strFileRead & @CRLF) $strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*)(\r\n)?","$2,") consolewrite("Only every 2nd line" & @CRLF) consolewrite("=====================" & @CRLF) consolewrite($strFileRead & @CRLF) FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 27, 2017 Author Share Posted August 27, 2017 thanks @junkew actually I got the true values but the last issue that I can't convert ($strFileRead) to Array because excel putting all values in 1 cell like Image. I tried to add @crlf $strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*)(\r\n)?",@crlf&"$2") still doesn't work. The whole code: #include <Excel.au3> #include <file.au3> ProcessClose("Excel.exe") ;;;;;;;;;;;;;; Compare Between DaysArray And DaysArray And Add "None" If @error Then Else $fileToRead="DaysArray.txt" $strFileRead = FileRead($fileToRead) $strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*)(\r\n)?",@crlf&"$2") consolewrite("Only every 2nd line" & @CRLF) consolewrite("=====================" & @CRLF) consolewrite($strFileRead & @CRLF) $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 Local $aInput $file = "calc.qu" _FileReadToArray($file, $aInput) For $i = 1 To UBound($aInput) - 1 If Mod($i, 2) = 0 Then _ArrayAdd($aInput, $i) ConsoleWrite(@CRLF & $aInput[$i]) Next _ArrayDelete($aInput, $aInput) ;Empty pervius values _ArrayDelete($aInput, 0) ;Delete First Value ;_ArrayDisplay($aInput) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel.xlsx") Sleep(100) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $strFileRead& @CRLF, "C2") EndIf Link to comment Share on other sites More sharing options...
junkew Posted August 27, 2017 Share Posted August 27, 2017 You can use stringsplit to make it an array see helpfile for examples FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
abdulrahmanok Posted August 27, 2017 Author Share Posted August 27, 2017 (edited) Ty very much I Appreciated your help, Final code: #include <Excel.au3> #include<file.au3> $fileToRead="DaysArray.txt" ; Read the contents of the file using the handle returned by FileOpen. Local $strFileRead = FileRead($fileToRead) ;~ consolewrite("Initial file contents" & @CRLF) ;~ consolewrite("=====================" & @CRLF) ;~ consolewrite($strFileRead & @CRLF) $strFileRead=stringregexpreplace($strFileRead,"(.*?\r\n)(.*)(\r\n)?","$2,") $Array=stringsplit ($strFileRead,",") _ArrayDisplay( $Array,"Four matches, split apart, as expected") consolewrite("Only every 2nd line" & @CRLF) consolewrite("=====================" & @CRLF) consolewrite($strFileRead & @CRLF) Edited August 27, 2017 by abdulrahmanok Link to comment Share on other sites More sharing options...
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