roofninja Posted April 6, 2012 Share Posted April 6, 2012 Several years ago, with the help of the community, we were able to create a program that would convert a txt file into an Excel spreadsheet. I would account for the file already having existed and having data already in it. Now that the Excel has been added into AutoIt, I would update it and with better code(I hoped). I am not sure what I am doing wrong here. I have been starring at this for several days now and can't get it to work. Here is the code so far and a sample txt file. I left extra things that I have tried in the past and the progress bar stuff. I wasn't sure if it was causing the program from working correctly. expandcollapse popup#Include <Excel.au3> #include <Array.au3> #Include <File.au3> ;------------------------------------ #include <Misc.au3> if _Singleton("Warning",1) = 0 Then Msgbox(0,"Warning","An occurence of your program is already running") Exit EndIf ;------------------------------------- Local $max ;$count = 5 Dim $nsheet[1], $sheet[1] ;-------------------------------- Local $bb, $count ;------------------------------------- $file = FileOpenDialog("Inventory",@ScriptDir,"(*.txt)") If @error Then MsgBox(4096,"","No File(s) chosen") Exit EndIf ;This section opens all files for use ;$file = FileOpen($file, 0) ;$file = FileOpen(@ScriptDir&"\inventory.txt", 0) ; Check if file opened for writing OK If $file = -1 Then MsgBox(0, "Error", "Unable to open inventory.txt data file.") Exit EndIf Local $oExcel = _ExcelBookOpen(@ScriptDir&"\test2_inv.xls",0, False);this will open the inventory.xls file. If @error >= 1 Then Local $oExcel = _ExcelBookNew(0);this is here to create the inventory.xls file if it does not exist. ;-------------------------------------------- ;title line _ExcelWriteCell($oExcel,"Room",1,1) _ExcelWriteCell($oExcel,"Lab Type",1,2) _ExcelWriteCell($oExcel,"Teacher",1,3) _ExcelWriteCell($oExcel,"Roam",1,4) _ExcelWriteCell($oExcel,"Make",1,5) _ExcelWriteCell($oExcel,"Model",1,6) _ExcelWriteCell($oExcel,"OS",1,7) _ExcelWriteCell($oExcel,"CPU",1,8) _ExcelWriteCell($oExcel,"RAM",1,9) _ExcelWriteCell($oExcel,"Service Tag",1,10) _ExcelWriteCell($oExcel,"FAIS",1,11) _ExcelWriteCell($oExcel,"PS",1,12) _ExcelWriteCell($oExcel,"NCWise",1,13) ;-------------------------------------------------- EndIf #cs _FileReadToArray($file,$nsheet) $sheet =_ExcelReadSheetToArray($oExcel) Do if $nsheet<>";" then _ExcelWriteCell($oExcel,$nsheet,($sheet[0][0]+1)) ;this writes all the data from the array Else EndIf Until $nsheet=$nsheet[0] ;_ExcelWriteArray($oExcel,($sheet[0][0]+1),1,$nsheet) #ce ;------------------------------------------------------ ;This is where all the work is done. ;ProgressOn ("","Processing the records","0 percent",50,300) ;For $i = 10 to 100 step 10 ;for the progress bar While 1 $line = FileReadLine($file) If @error = -1 Then ExitLoop ;---------------------------------------------------- $skip = StringReplace($line,"*","",0,0) if $skip<>";" then _ArrayAdd($nsheet,$line) ;this reads the file into an array $bb = $bb+1 EndIf if $skip = ";" then ;ProgressSet($i, $i & " percent") $count = $count+1 ;this counts the number of records that are parcessing ;$sheet = _ExcelSheetNameGet($oExcel);finds the name of the active sheet ;$array = _ExcelSheetUsedRangeGet($oExcel,$sheet);finds the last line of data. $sheet =_ExcelReadSheetToArray($oExcel) $max = _ArrayMaxIndex($sheet) +1 ;if $eof <= $array[3] Then ;this keeps us from copiing over data in sheet ;$eof = ($array[3] +1) ;EndIf ;------------------------------------------------------- if $bb = 13 then _ArrayDisplay($nsheet) for $aa = 1 to 13 ;_ExcelWriteCell($oExcel,$nsheet[$aa],$sheet[1][0]+1) ;this writes all the data from the array _ExcelWriteCell($oExcel,$nsheet[$aa],$max,$aa) ;this writes all the data from the array Next EndIf ;----------------------------------------- for $ts = 1 to 13 _ArrayDelete($nsheet,$ts) ;this deletes the array Next $bb = 0 EndIf ;$skip=";" Wend ;Next ;for the progress bar ;ProgressSet(100 , "Done", "Complete") ;ProgressOff() ; Now we save it into the temp directory; overwrite existing file if necessary _ExcelBookSaveAs($oExcel,@ScriptDir&"\test2_inv.xls","xls",0,0) ; And finally we close out _ExcelBookClose($oExcel) FileClose($file) ;for inventory.txt MsgBox(0,"Convert txt inventory","This program has converted "&$count&" records of Computers in the TXT inventory into your EXCEL inventory",5) Exit sample txt file expandcollapse popupLG NA Smith Yes Dell D520 XP P4 1015 7LJCSC1 200-9999 Yes Yes ;*************************************** roam NA Cabin Yes Dell D520 XP P4 1015 7LJCSC1 200-7777 Yes Yes ;*************************************** ROAMT NA Lacy No Gateway blank XP P4 2039 0475528 200-8888 No No ;*************************************** WL2 NA Jones No HP Book2530p XP P4 2973 0432112 200-7777 No No ;*************************************** ROAMT NA Wilson No Gateway E295C XP P4 2039 0475528 400-4444 No No ;*************************************** RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!! Link to comment Share on other sites More sharing options...
roofninja Posted April 9, 2012 Author Share Posted April 9, 2012 I welcome anyone to re-write it. What it currently is doing is that it is in an infant loop somewhere. Also I can't figure out how to tell the program the last line that has data on it and make it plus one for where to write the new data to the spreadsheet. RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!! Link to comment Share on other sites More sharing options...
Juvigy Posted April 9, 2012 Share Posted April 9, 2012 The infinite loop is this:;This is where all the work is done. ;ProgressOn ("","Processing the records","0 percent",50,300) ;For $i = 10 to 100 step 10 ;for the progress bar While 1You dont have exitloop from there. Link to comment Share on other sites More sharing options...
water Posted April 9, 2012 Share Posted April 9, 2012 I have been starring at this for several days now and can't get it to work.Could you please describe what doesn't work? Do you get any error message, does the script crash?What version of AutoIt and Office do you use? 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...
water Posted April 9, 2012 Share Posted April 9, 2012 This gets you the maximum row used: $iMaxRow = $oExcel.ActiveSheet.UsedRange.Rows.Count 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...
roofninja Posted April 9, 2012 Author Share Posted April 9, 2012 I see that now. .Could you please describe what doesn't work? Do you get any error message, does the script crash? What version of AutoIt and Office do you use?I am using Win7 x64 and office 2010. AutoIt version is 3.3.6.1. RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!! Link to comment Share on other sites More sharing options...
water Posted April 9, 2012 Share Posted April 9, 2012 Do you run the 32 bit version of Office? 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...
roofninja Posted April 9, 2012 Author Share Posted April 9, 2012 The infinite loop is this: You dont have exitloop from there.I see that now. Not sure how to do an EOF on the file. Should I read the file into an array first and would that be better? RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!! Link to comment Share on other sites More sharing options...
roofninja Posted April 9, 2012 Author Share Posted April 9, 2012 Do you run the 32 bit version of Office?64 bit version of Office. RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!! Link to comment Share on other sites More sharing options...
water Posted April 9, 2012 Share Posted April 9, 2012 If your script crashes or _Excel* functions return strange results test our script with a 32 bit version of Office and make sure that the script is compiled/executed for 32 bit. There are some threads on this forum which state that even MS doesn't recommend to use the 64 bit version of Office at this time. 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...
roofninja Posted April 9, 2012 Author Share Posted April 9, 2012 If your script crashes or _Excel* functions return strange results test our script with a 32 bit version of Office and make sure that the script is compiled/executed for 32 bit.There are some threads on this forum which state that even MS doesn't recommend to use the 64 bit version of Office at this time.I will keep this in mind as we get this program finished. I currently don't have a 32 bit version of Office running, but do have access one. RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!! Link to comment Share on other sites More sharing options...
Juvigy Posted April 9, 2012 Share Posted April 9, 2012 Did you check the "while" loop you have ? It is infinite loop - it never ends. Link to comment Share on other sites More sharing options...
BrewManNH Posted April 9, 2012 Share Posted April 9, 2012 Did you check the "while" loop you have ?It is infinite loop - it never ends.The While loop has an exitloop right after the FileReadLine, so if @error = -1 (which is the EOF indicator for FileReadLine) it exits the While loop. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator Link to comment Share on other sites More sharing options...
qsek Posted April 9, 2012 Share Posted April 9, 2012 Better use: If @error Then ExitLoop In case the file is renamed or cant be accessed it will give @error=1 so the loop never exits Teamspeak 3 User Viewer - Quick and functional TS3 Query script, which shows online users.Cached Screenshot Deleter - Deletes older Fraps Screenshots if they exceed a specified limit.Unresolved Topics:Intercept and modify dragdrop text behaviour in scite Link to comment Share on other sites More sharing options...
BrewManNH Posted April 9, 2012 Share Posted April 9, 2012 I think the real reason is because the fileopen command is commented out, and you're reading the same line of the file every time through the While loop because you're using FileReadLine with a filename and not a file handle. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator Link to comment Share on other sites More sharing options...
roofninja Posted April 9, 2012 Author Share Posted April 9, 2012 I think the real reason is because the fileopen command is commented out, and you're reading the same line of the file every time through the While loop because you're using FileReadLine with a filename and not a file handle.I thought that using FileOpenDialog replaced the FileOpen? Also, none of the examples in the help show that I need to use a FileOpen. RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!! Link to comment Share on other sites More sharing options...
BrewManNH Posted April 9, 2012 Share Posted April 9, 2012 FIleOpenDialog only opens the File Open dialog, not the file, you need to use FileOpen in read mode to read the file because the only thing that FileOpenDialog returns is the file name. All the examples for FileReadLine are using a FileOpen command before it. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator Link to comment Share on other sites More sharing options...
roofninja Posted April 9, 2012 Author Share Posted April 9, 2012 This gets you the maximum row used: $iMaxRow = $oExcel.ActiveSheet.UsedRange.Rows.Count Sorry, I missed this one. Where is this kind of usage in the help files? How did you know to use this? I must be missing some help files. RUN . . . Slide . . . TAG . . . Your out . . . PAINTBALL !!! Link to comment Share on other sites More sharing options...
Blue_Drache Posted April 9, 2012 Share Posted April 9, 2012 (edited) Sorry, I missed this one. Where is this kind of usage in the help files? How did you know to use this? I must be missing some help files.Check the help file inside of Excel for that one. Specifically under the Alt+F11 script entry. The Obj() programming feature closely follows the VBS syntax available inside Excel. Activate the developer tab, hit Alt+F11 and open the object browser to get a feel of the syntax you can use. Edited April 9, 2012 by Blue_Drache Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache Link to comment Share on other sites More sharing options...
Reg2Post Posted April 9, 2012 Share Posted April 9, 2012 Is there a reason why you don't just open the txt files with Excel? If not, I suggest doing so to simplify your code. You can specify which row to start appending the data from your text file in the Excel file with a delimiter of your choice. Afterwards, you can add your row descriptions with a column insert. 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