RogueX20 Posted June 12, 2011 Share Posted June 12, 2011 This stuff is fun. I wish I started to learn years ago. Finished up a program with the help of members on this site. I thank you all. I read a lot to get concepts and my head wrapped around all of this and finally decided to put it to use to some stuff that was dumped on a few of us recently at work. Below is the code that I would like to know how more of you experienced programmers would clean it up. I tested and it does the job, so I am content with that. However if there is cleaning up or ways to make it more efficient I would like to see. That would give me more ideas on when I go to write. expandcollapse popup#include <Excel.au3> #include <Array.au3> $sFilePath1 = @ScriptDir & "\MainFile.xlsx" $oExcel = _ExcelBookOpen($sFilePath1) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist") Exit EndIf $search = FileFindFirstFile("y*.txt") $i = 0 If $search = -1 Then MsgBox(0, "Error", "No files/directories matched the search pattern") Exit EndIf While 1 $file = FileFindNextFile($search) $fileRead = FileOpen($file, 0) $line = FileReadLine($fileRead,4) ;testing to see what shows up in the line ;MsgBox(0, "Line read: ", $line) ;ie - Department XYZ has the following charge of 112.00 for Widget purchased on Oct 22, 2010 released to John Doe $aArray = StringRegExp($line, "Department (.+) has the following charge of (.+) for (.+) purchased on (.+) released to (.+)", 3) ;_ArrayDisplay($aArray) If @error Then ExitLoop $i = ($i + 1) ;looking to copy the name of the file and the info in the array into the excel file _ExcelWriteCell($oExcel, $aArray[1], $i, 1) _ExcelWriteCell($oExcel, $aArray[2], $i, 2) _ExcelWriteCell($oExcel, $aArray[3], $i, 3) _ExcelWriteCell($oExcel, $aArray[4], $i, 4) _ExcelWriteCell($oExcel, $aArray[0], $i, 5) _ExcelWriteCell($oExcel, $file, $i, 6) WEnd FileClose($search) FileClose($fileRead) Link to comment Share on other sites More sharing options...
JohnOne Posted June 12, 2011 Share Posted June 12, 2011 (edited) You are using fileopen within your loop, and only closing it when loop is ended I dont know if any, the concequences of this, butYou may as well take it out all together as the filereadline will open and close the file when a path is given. EDIT expandcollapse popup#include <Excel.au3> #include <Array.au3> $sFilePath1 = @ScriptDir & "\MainFile.xlsx" $oExcel = _ExcelBookOpen($sFilePath1) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist") Exit EndIf $search = FileFindFirstFile("y*.txt") $i = 0 If $search = -1 Then MsgBox(0, "Error", "No files/directories matched the search pattern") Exit EndIf While 1 $file = FileFindNextFile($search) ;$fileRead = FileOpen($file, 0) $line = FileReadLine($file, 4) ;testing to see what shows up in the line ;MsgBox(0, "Line read: ", $line) ;ie - Department XYZ has the following charge of 112.00 for Widget purchased on Oct 22, 2010 released to John Doe $aArray = StringRegExp($line, "Department (.+) has the following charge of (.+) for (.+) purchased on (.+) released to (.+)", 3) ;_ArrayDisplay($aArray) If @error Then ExitLoop $i = ($i + 1) ;looking to copy the name of the file and the info in the array into the excel file _ExcelWriteCell($oExcel, $aArray[1], $i, 1) _ExcelWriteCell($oExcel, $aArray[2], $i, 2) _ExcelWriteCell($oExcel, $aArray[3], $i, 3) _ExcelWriteCell($oExcel, $aArray[4], $i, 4) _ExcelWriteCell($oExcel, $aArray[0], $i, 5) _ExcelWriteCell($oExcel, $file, $i, 6) WEnd FileClose($search) ;FileClose($fileRead) EDIT 2 also, you should have "If @error Then ExitLoop" directly after the line "$file = FileFindNextFile($search)" so it exits the loop if no more files are found Edited June 12, 2011 by JohnOne AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans. Link to comment Share on other sites More sharing options...
RogueX20 Posted June 12, 2011 Author Share Posted June 12, 2011 Thanks John. Link to comment Share on other sites More sharing options...
Zedna Posted June 12, 2011 Share Posted June 12, 2011 Little optimization instead of $i = ($i + 1) use this $i += 1 Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
JohnOne Posted June 12, 2011 Share Posted June 12, 2011 If you intend to expand upon this project it might be wise to make it more modular and create a function, something like (untested) expandcollapse popup#include <Excel.au3> #include <Array.au3> $sFilePath1 = @ScriptDir & "\MainFile.xlsx" $oExcel = _ExcelBookOpen($sFilePath1) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist") Exit EndIf $search = FileFindFirstFile("y*.txt") $i = 0 If $search = -1 Then MsgBox(0, "Error", "No files/directories matched the search pattern") Exit EndIf While 1 _MyFunc() If @error Then ExitLoop WEnd FileClose($search) Func _MyFunc() $file = FileFindNextFile($search) If @error Then Return SetError(1) $line = FileReadLine($file, 4) $aArray = StringRegExp($line, "Department (.+) has the following charge of (.+) for (.+) purchased on (.+) released to (.+)", 3) $i += 1 _ExcelWriteCell($oExcel, $aArray[1], $i, 1) _ExcelWriteCell($oExcel, $aArray[2], $i, 2) _ExcelWriteCell($oExcel, $aArray[3], $i, 3) _ExcelWriteCell($oExcel, $aArray[4], $i, 4) _ExcelWriteCell($oExcel, $aArray[0], $i, 5) _ExcelWriteCell($oExcel, $file, $i, 6) EndFunc ;==>_MyFunc AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans. Link to comment Share on other sites More sharing options...
Zedna Posted June 12, 2011 Share Posted June 12, 2011 (edited) Maybe you can use _ExcelWriteArray(), something like this (not tested): _ExcelWriteCell($oExcel, $aArray[1], $i, 1) _ExcelWriteCell($oExcel, $aArray[2], $i, 2) _ExcelWriteCell($oExcel, $aArray[3], $i, 3) _ExcelWriteCell($oExcel, $aArray[4], $i, 4) _ExcelWriteCell($oExcel, $aArray[0], $i, 5) _ExcelWriteCell($oExcel, $file, $i, 6) _ExcelWriteArray($oExcel, $i, 1, $aArray, 0, 1) _ExcelWriteCell($oExcel, $file, $i, 6) Edited June 12, 2011 by Zedna Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
RogueX20 Posted June 12, 2011 Author Share Posted June 12, 2011 I thought there might be some optimization that could help it. Zedna I also thought there must be a shorter way to get what I want to write to the excel file to happen. Thanks! Will test in a few. Link to comment Share on other sites More sharing options...
RogueX20 Posted June 12, 2011 Author Share Posted June 12, 2011 Seems to be a limit on how many files it will loop through. Stopped at the same count the 3 times I ran it. 893. Let me check a few things. Link to comment Share on other sites More sharing options...
RogueX20 Posted June 12, 2011 Author Share Posted June 12, 2011 Problem found. Some of the files do not have the same info in a particular manner. Going to have to put some error checking in the code to skip when it doesn't find that particular string on a particular line. You've all been a great help. I am getting addicted to this stuff. Better pace myself as there is so much to learn just to be considered "decent". 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