bdb4269 Posted July 9, 2007 Share Posted July 9, 2007 Basically, when you save an excel file as space delimited (.prn) -- if the data is longer than about 240 columns total, then it splits each record, into 2 lines. I made a script that will count the lines in the file, and then split the first half and second half to separate files, and then join those two files line by line, into the destination file. My one problem, is that the last field included on the first set of lines, has to end on the same column in every record - for everything to line up correctly when the second part of the line, is added to the end of the first part of the line. And I don't want to have to have that requirement for my script to work. So basically at the part where i join them like thisFileWriteLine($write, $line1 & $line2)I would like essentially like to know how I could force it to start inserting the $line2 part at a specific column (like column 260 would be nice)So regardless of where $line1 ends, $line2 will always start at a specific column. Does that make sense?----------------------------------------------------------------------------Below is the whole script. -- Please be nice, I am just starting to teach myself AutoIt, so I am sure, there are a lot of things/ways, that I could do this easier than I have (nameably, Im sure, I could do without writing the split file, to actual files, and could just store them in 2 arrays) - So feel free to let me know of ways I could do stuff easier too.expandcollapse popup$infile = FileOpenDialog("Sourcefile","","PRN files (*.prn)",1) $outfile = FileSaveDialog("Targetfile","","All (*.*)",16) $line = "" $line1 = "" $line2 = "" $count1 = 0 $count2 = 0 $read1 = FileOpen($infile,0) If $read1 = -1 Then MsgBox(0, "Error", "Unable to open input file 1.") Exit EndIf While 1 $line1 = FileReadLine($read1) If @error <> 0 Then ExitLoop $count1 += 1 Wend FileClose($read1) ;####################################################################### $read1 = FileOpen($infile,0) If $read1 = -1 Then MsgBox(0, "Error", "Unable to open input file 1.") Exit EndIf $write1 = FileOpen("c:\bentemp1",2) If $write1 = -1 Then MsgBox(0, "Error", "Unable to open temp output file 1.") Exit EndIf $write2 = FileOpen("c:\bentemp2",2) If $write2 = -1 Then MsgBox(0, "Error", "Unable to open temp output file 2.") Exit EndIf While 1 $line = FileReadLine($read1) If @error <> 0 Then ExitLoop $count2 += 1 If $count2 <= $count1/2 then FileWriteLine($write1, $line) else FileWriteLine($write2, $line) EndIf Wend FileClose($write1) FileClose($write2) FileClose($read1) ;####################################################################### $read1 = FileOpen("c:\bentemp1",0) If $read1 = -1 Then MsgBox(0, "Error", "Unable to open temp input file 1.") Exit EndIf $read2 = FileOpen("c:\bentemp2",0) If $read2 = -1 Then MsgBox(0, "Error", "Unable to open temp input file 2.") Exit EndIf $write = FileOpen($outfile,2) If $write = -1 Then MsgBox(0, "Error", "Unable to open output file.") Exit EndIf While 1 $line1 = FileReadLine($read1) $line2 = FileReadLine($read2) If @error <> 0 Then ExitLoop FileWriteLine($write, $line1 & $line2) Wend FileClose($write) FileClose($read1) FileClose($read2) MsgBox(0, "Success! ", $infile & " has been converted into " & $outfile & ".")BONUS QUESTION: In SciTE - in which settings file can I change the caret color and properties? I have got my au3 highlighting all set up how I want, in in SciTEUser.properties (see below), but I don't see a variable for the actual caret. expandcollapse popup#Background style.au3.32=style.*.32=$(font.base),back:#000000 #CaretLineBackground caret.line.back=#333333 # Brace highlight style.au3.34=fore:#0000FF,bold,back:#808080 # Brace incomplete highlight style.au3.35=fore:#009933,italics,back:#FF0000 #White space style.au3.0=fore:#FFFFFF,back:#000000 #Comment line style.au3.1=fore:#009933,italics,back:#000000 #Comment block style.au3.2=fore:#669900,italics,back:#000000 #Number style.au3.3=fore:#AC00A9,italics,bold,back:#000000 #Function style.au3.4=fore:#8000FF,italics,bold,back:#000000 #Keyword style.au3.5=fore:#0000FF,bold,back:#000000 #Macro style.au3.6=fore:#FF33FF,bold,back:#000000 #String style.au3.7=fore:#9999CC,bold,back:#000000 #Operator style.au3.8=fore:#FF8000,bold,back:#000000 #Variable style.au3.9=fore:#00FF00,bold,back:#000000 #Sent keys style.au3.10=fore:#FF8800,bold,back:#000000 #Pre-Processor style.au3.11=fore:#F000FF,italics,back:#000000 #Special style.au3.12=fore:#A00FF0,italics,back:#000000 #Abbrev-Expand style.au3.13=fore:#FF0000,bold,back:#000000 #Com Objects style.au3.14=fore:#0000FF,italics,bold,back:#000000 #Standard UDF's style.au3.15=fore:#0080FF,italics,bold,back:#000000 Link to comment Share on other sites More sharing options...
evilertoaster Posted July 9, 2007 Share Posted July 9, 2007 do you have a sample excel file you can post? It wounds liek maybe you want to puts tabs before the line. Link to comment Share on other sites More sharing options...
bdb4269 Posted July 9, 2007 Author Share Posted July 9, 2007 do you have a sample excel file you can post? It wounds liek maybe you want to puts tabs before the line.For an example, use any excel file that is long -- the ones I would be using don't follow any sort of ryme or reason. That said though -- I can make the script work now, by just inserting a field into the excel file (in the place where it will end up as the last field on the first set of lines), and filling it for every record with somthing static like "00000".But my whole point, is that I dont want to have to do anything special to the excel file to make this work. And I can't save it any other format than ".prn" because that is the only format, that actuall saves it like a "fixed field" file. So it keeps everything lined up vertically. And I'm sure there is a really easy autoit function to insert the second string at a given column. I just don't know what it is! Link to comment Share on other sites More sharing options...
bdb4269 Posted July 9, 2007 Author Share Posted July 9, 2007 Oh, I see what you mean by tabs now -- but no that wont work -- I can't have tabs in this file. The output I am going for is a "fixed field" ascii file. All I really need to know, is how to do this FileWriteLine($write, $line1 & $line2) Except have $line2 always start at column 260 -- instead of having it start just after $line1. ---------------------------------- For instance, in one language I used to use, this kind of formatting would be done kinda like this FileWriteLine($write, $line1 \260g $line2) where "\260g" simply means, goto column 260 -- so regardless of whether $line1 is 10 columns or 250 columns - $line2 would always start at column 260, and it would automatically insert any needed spaces. Link to comment Share on other sites More sharing options...
evilertoaster Posted July 9, 2007 Share Posted July 9, 2007 what would fill the empty collums? sapces? maybe do stringlen() to get teh length of the first string then subtract 260 to get teh offest to start the second line from. Link to comment Share on other sites More sharing options...
bdb4269 Posted July 9, 2007 Author Share Posted July 9, 2007 what would fill the empty collums? sapces?maybe do stringlen() to get teh length of the first string then subtract 260 to get teh offest to start the second line from.Yes - the empty columns should be filled with spaces. That would certainly be doable. Is there an easy way to offset the second line(once I have determined the needed offset using StringLen), by that needed number of spaces, and have the spaces inserted automatically -- or would I just have to essentially, "manually" insert spaces on the line until, I have filled it to 260 and then stick $line2 on the end? Link to comment Share on other sites More sharing options...
evilertoaster Posted July 9, 2007 Share Posted July 9, 2007 or would I just have to essentially, "manually" insert spaces on the line until, I have filled it to 260 and then stick $line2 on the end?If i understand what your doing i think that would work. so you could buidl the string-$padding=""for $i=1 to 260-stringlen($line1) $padding=$padding&" "next Link to comment Share on other sites More sharing options...
SadBunny Posted July 9, 2007 Share Posted July 9, 2007 If i understand what your doing i think that would work. so you could buidl the string- $padding="" for $i=1 to 260-stringlen($line1) $padding=$padding&" " next The stringformat probably suits your present needs better, because it's shorter, less work and way more flexible and powerful if you ever want to do more advanced string formatting: (this will open a notepad window, it has fixed with font by default so shows the idea, I guess) $text1_1 = "test col 1" $text1_2 = "2nd col 1" $text2_1 = "this is the second column 1 entry" $text2_2 = "2nd column 2" $line1 = StringFormat("%-50s",$text1_1)&$text1_2 $line2 = StringFormat("%-50s",$text2_1)&$text2_2 Run("notepad.exe") Sleep(1000) send($line1&@CR&$line2) Roses are FF0000, violets are 0000FF... All my base are belong to you. Link to comment Share on other sites More sharing options...
bdb4269 Posted July 9, 2007 Author Share Posted July 9, 2007 The stringformat probably suits your present needs better, because it's shorter, less work and way more flexible and powerful if you ever want to do more advanced string formatting: (this will open a notepad window, it has fixed with font by default so shows the idea, I guess) $text1_1 = "test col 1" $text1_2 = "2nd col 1" $text2_1 = "this is the second column 1 entry" $text2_2 = "2nd column 2" $line1 = StringFormat("%-50s",$text1_1)&$text1_2 $line2 = StringFormat("%-50s",$text2_1)&$text2_2 Run("notepad.exe") Sleep(1000) send($line1&@CR&$line2) Thats what I was looking for! Thanks! Now if I can just figure out how to use arrays, so I don't have to write the two intermediary files, and can instead just store that in an array? Got any quick tips for that? Link to comment Share on other sites More sharing options...
SadBunny Posted July 9, 2007 Share Posted July 9, 2007 Thats what I was looking for! Thanks! Now if I can just figure out how to use arrays, so I don't have to write the two intermediary files, and can instead just store that in an array? Got any quick tips for that? Dim $array1[$NumLines/2] Dim $array2[$NumLines/2] for $a = 0 to ($numLines/2)-1 ; <- arrays are 0 based, hence the 0 and the -1 $array1[$a] = FileReadLine($file) $array2[$a] = FileReadLine($file) next This should store every colum in an array, provided the file is organized like: Line1Col1 Line1Col2 Line2Col1 Line2Col2 Line3Col1 Line3Col2 ... etc Roses are FF0000, violets are 0000FF... All my base are belong to you. Link to comment Share on other sites More sharing options...
bdb4269 Posted July 10, 2007 Author Share Posted July 10, 2007 (edited) Dim $array1[$NumLines/2] Dim $array2[$NumLines/2] for $a = 0 to ($numLines/2)-1 ; <- arrays are 0 based, hence the 0 and the -1 $array1[$a] = FileReadLine($file) $array2[$a] = FileReadLine($file) next This should store every colum in an array, provided the file is organized like: Line1Col1 Line1Col2 Line2Col1 Line2Col2 Line3Col1 Line3Col2 ... etc Sweet -- I got it working with the array's now. (I ended up doing a bit different, so it fit into the code I already had there.) Thanks for all the info!! I have only been here a short time -- but I really gotta say this is one of the most helpful forum communities I have ever seen. Out of curiosity -- In trying to weed out some of the more pointless stuff in the script, I was trying to figure out how I could just open the input file once, as opposed to twice (once to count the lines, and once, to split it up into arrays) The only way I could figure out how to get it back to line 1 for FileReadLine, without closing and reopening the file was to change it from this. (changed part is between the "#######" lines) expandcollapse popup$read1 = FileOpen($infile,0) If $read1 = -1 Then MsgBox(0, "Error", "Unable to open input file" & $infile) Exit EndIf While 1 $line1 = FileReadLine($read1) If @error <> 0 Then ExitLoop $count1 += 1 Wend ;####################################################################### FileClose($read1) $read1 = FileOpen($infile,0) If $read1 = -1 Then MsgBox(0, "Error", "Unable to open input file" & $infile) Exit EndIf ;####################################################################### Dim $part1[$count1] Dim $part2[$count1] $count2 = 0 $count3 = 0 While 1 $line = FileReadLine($read1) If @error <> 0 Then ExitLoop $count2 += 1 If $count2 <= $count1/2 then $part1[$count2] = $line Else $count3 += 1 $part2[$count3] = $line EndIf Wend FileClose($read1) to this expandcollapse popup$read1 = FileOpen($infile,0) If $read1 = -1 Then MsgBox(0, "Error", "Unable to open input file" & $infile) Exit EndIf While 1 $line1 = FileReadLine($read1) If @error <> 0 Then ExitLoop $count1 += 1 Wend ;####################################################################### $line = FileReadLine($read1,1) $count2 += 1 $part1[$count2] = $line ;####################################################################### Dim $part1[$count1] Dim $part2[$count1] $count2 = 0 $count3 = 0 While 1 $line = FileReadLine($read1) If @error <> 0 Then ExitLoop $count2 += 1 If $count2 <= $count1/2 then $part1[$count2] = $line Else $count3 += 1 $part2[$count3] = $line EndIf Wend FileClose($read1) It seems like there would be a better way, than putting an iteration of the while loop in front of it, -- but I could be completely wrong, or perhaps that better way, is to close and reopen the file. What do you think? -- Is there anyway, to reset "FileReadLine" so that the next time it is called without a line specified, it will read the first line of the file? (aside from closing and opening the file) (I know technically I could just read the file specifying line by line in the while loop, but i read that this goes a lot slower if you are specifying line number for each FileReadLine) Edited July 10, 2007 by bdb4269 Link to comment Share on other sites More sharing options...
SadBunny Posted July 10, 2007 Share Posted July 10, 2007 (edited) It seems like there would be a better way, than putting an iteration of the while loop in front of it, -- but I could be completely wrong, or perhaps that better way, is to close and reopen the file. What do you think? -- Is there anyway, to reset "FileReadLine" so that the next time it is called without a line specified, it will read the first line of the file? (aside from closing and opening the file) (I know technically I could just read the file specifying line by line in the while loop, but i read that this goes a lot slower if you are specifying line number for each FileReadLine) Specifying lines is slow, it should indeed be avoided if possible. Maybe the _FileCountLines and _FileReadToArray functions would help you a lot? The file reading work has already been done for you Then all you would need to do was split the array into two arrays using a simple for-loop like For $a = 0 To $lineCount-1 Step 2, and combining that with the function _ArrayAdd to fill up two result arrays with $origArray[$a] and $origArray[$a+1] Late here... Going to bed now /edit: edited bold 'For' example to work Edited July 10, 2007 by SadBunny Roses are FF0000, violets are 0000FF... All my base are belong to you. Link to comment Share on other sites More sharing options...
bdb4269 Posted July 10, 2007 Author Share Posted July 10, 2007 (edited) Specifying lines is slow, it should indeed be avoided if possible. Maybe the _FileCountLines and _FileReadToArray functions would help you a lot? The file reading work has already been done for you Then all you would need to do was split the array into two arrays using a simple for-loop like For $a = 0 To $lineCount-1 Step 2, and combining that with the function _ArrayAdd to fill up two result arrays with $origArray[$a] and $origArray[$a+1] Late here... Going to bed now /edit: edited bold 'For' example to work Thanks for pointing me to all those functions!! I learned a lot playing around with them and tweaking my script. And, I think I got it all ironed out how I like it now. (I also added an option so it can work on files with 2-4 layers of lines) expandcollapse popup#include <File.au3> $OK = MsgBox(49, "NOTE", "This will convert a .prn file that was output by excel, as 2, 3 or 4 layers of lines, into a file with just one layer of lines.") If $OK == 2 Then Exit $infile = FileOpenDialog("Sourcefile","","PRN files (*.prn)",1) $layers = InputBox("How many layers are there?", "How many layers are there? (2=Defualt)", "2") if $layers<>2 AND $layers<>3 AND $layers<>4 Then MsgBox(0, "Error", "As of right now, this script can only handle 2, 3 or 4 layers. Please restart, and enter either a '2', a '3' or a '4'.") Exit EndIf $outfile = FileSaveDialog("Target file","","All (*.*)",16) ;####################################################################### $totallines = _FileCountLines($infile) Dim $totalfile[$totallines+1] Dim $part1[($totallines/$layers)+1] Dim $part2[($totallines/$layers)+1] Dim $part3[($totallines/$layers)+1] Dim $part4[($totallines/$layers)+1] _FileReadToArray($infile, $totalfile) if @error=1 Then MsgBox(0, "ERROR!", "Could not open " & $infile & " for reading. Please make sure the file exist's and is not Open.") Exit EndIf For $i=1 to $totallines/$layers $part1[$i] = $totalfile[$i] $part2[$i] = $totalfile[$i+($totallines/$layers)] If $layers>2 then $part3[$i] = $totalfile[$i+(($totallines/$layers)*2)] EndIf If $layers>3 then $part4[$i] = $totalfile[$i+(($totallines/$layers)*3)] EndIf Next ;####################################################################### Dim $newfile[($totallines/$layers)+2] for $i=1 to $totallines/$layers $newfile[$i] = StringFormat("%-260s",$part1[$i]) & StringFormat("%-260s",$part2[$i]) & StringFormat("%-260s",$part3[$i]) & $part4[$i] Next _FileWriteFromArray($outfile, $newfile, 1) if @error=1 Then MsgBox(0, "ERROR!", "Could not open " & $outfile & " for writing. Please make sure the file is not open.") Exit EndIf MsgBox(0, "Success! ", $infile & " has been converted to " & $outfile & ".") Note - the input file format is not actually like Line 1 Line 1 cont Line 2 Line 2 cont Line 3 Line 3 cont but rather like Line 1 Line 2 Line 3 Line 1 cont Line 2 cont Line 3 cont Edited July 10, 2007 by bdb4269 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