gcue Posted August 12, 2008 Share Posted August 12, 2008 i imagine this can be done with stringsplit? i think the spacing between each column is a tab but it might be a space. i am querying a text file essentially it is a hardware inventory database which has been exported into a text file. column 14 has the persons account name and column 1 has the computer serial number and to make matters more complicated column 4 has the type of computer it is laptop or desktop (which would place an L or D in front of the serial number) so if the person queries a particular account name i will have to find the row(s) that have that account name and report back the computers the person has. what would this mess look like? Link to comment Share on other sites More sharing options...
Kerros Posted August 12, 2008 Share Posted August 12, 2008 (edited) My thought would be to read the text file to an array, and then redo the array with stringsplit into another array, something like #include<array.au3> #include<file.au3> Local $file,$AvArray,$InvArray[1] _FileReadToArray($file,$AvArray) For $icc=1 To UBound($AvArray)-1 _ArrayAdd($InvArray,StringSplit($AvArray[$icc],Chr(0x20)&Chr(0x09))) Next Edit: fixed code error Edited August 12, 2008 by Kerros Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance. Link to comment Share on other sites More sharing options...
Kerros Posted August 12, 2008 Share Posted August 12, 2008 huh...Ok. I've never seen _filereadtoarray not do the entire file, but ok.. As for all the records being put into a single column, that StringSplit($AvArray[$icc],Chr(0x20)&Chr(0x09)) should have done both tabs and spaces...you could use some type of regex function to parse the data, but I'm not a master at that... do you have a sample of the textfile that you wouldn't mind posting so we could play with it? Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance. Link to comment Share on other sites More sharing options...
Kerros Posted August 12, 2008 Share Posted August 12, 2008 Ok This worked for your sample file. My mistake before is that you can't add an array to an existing array with _arrayadd(). #include<array.au3> #include<file.au3> Local $file,$AvArray,$InvArray[20][30] $file = @ScriptDir&'\sample.txt' _FileReadToArray($file,$AvArray) For $icc=1 To UBound($AvArray)-1 $Temp = StringSplit($AvArray[$icc]," ") For $idd = 1 To UBound($Temp)-1 $InvArray[$icc][$idd] = $temp[$idd] Next Next _ArrayDisplay($InvArray) You will need to increase the size of the array to hold all of your records. Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance. Link to comment Share on other sites More sharing options...
gcue Posted August 12, 2008 Author Share Posted August 12, 2008 hmm this isnt working for me.. i get an error: "array variable has incorrect number of subscripts or subscript dimension range exceeded also, what do you mean increase the size of the array.. do you mean here? [20][30]? Ok This worked for your sample file. My mistake before is that you can't add an array to an existing array with _arrayadd(). #include<array.au3> #include<file.au3> Local $file,$AvArray,$InvArray[20][30] $file = @ScriptDir&'\sample.txt' _FileReadToArray($file,$AvArray) For $icc=1 To UBound($AvArray)-1 $Temp = StringSplit($AvArray[$icc]," ") For $idd = 1 To UBound($Temp)-1 $InvArray[$icc][$idd] = $temp[$idd] Next Next _ArrayDisplay($InvArray) You will need to increase the size of the array to hold all of your records. Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 12, 2008 Share Posted August 12, 2008 (edited) My mistake before is that you can't add an array to an existing array with _arrayadd().There was a UDF posted some time ago, by a rakishly good-looking bird I must admit, called __ArrayConcatenate() which can work with mixed 1D/2D arrays. BTW: Is it really required to work with this text file? AutoIt can access a database directly, with ADODB for example. Edited August 12, 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 Link to comment Share on other sites More sharing options...
gcue Posted August 12, 2008 Author Share Posted August 12, 2008 yea i need to use the text file bc my company wont allow me to query our production database - so they export to a txt file... whats the adodb thing? is that another UDF? what sort of databases are queryable? (sql, mysql,... i wanted to query an NSF - lotus notes database at one point) Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 12, 2008 Share Posted August 12, 2008 whats the adodb thing? is that another UDF? what sort of databases are queryable? (sql, mysql,... i wanted to query an NSF - lotus notes database at one point)Microsoft ActiveX Data Objects (ADO) ADODB connections can be made to various database sources, but I have no idea if an "NSF - lotus notes database" is one of them.Search the forum for many examples with SQL server, MySQL, MS Access, MS Excel, etc. 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 Link to comment Share on other sites More sharing options...
JFee Posted August 12, 2008 Share Posted August 12, 2008 If it still gives you problems, just generate an array manually with a couple nested for loops, and I would hope that the file is TAB delimited not space delimited Regards,Josh Link to comment Share on other sites More sharing options...
Kerros Posted August 12, 2008 Share Posted August 12, 2008 There was a UDF posted some time ago, by a rakishly good-looking bird I must admit, called __ArrayConcatenate() which can work with mixed 1D/2D arrays. BTW: Is it really required to work with this text file? AutoIt can access a database directly, with ADODB for example. Now that is going to be handy.Thanks Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance. Link to comment Share on other sites More sharing options...
gcue Posted August 13, 2008 Author Share Posted August 13, 2008 (edited) not sure how to do that JFee. If it still gives you problems, just generate an array manually with a couple nested for loops, and I would hope that the file is TAB delimited not space delimited Edited August 13, 2008 by gcue Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 13, 2008 Share Posted August 13, 2008 C:\Temp\Test.txt: Line 1 1-2 1-3 Line 2 Line 3 3-2 3-3 3-4 Line 4 4-2 Line 5 5-2 5-3 5-4 5-5 Line 6 6-2 6-3 Line 8 8-2 8-3 8-4 Script to create 2D array based on @TAB delimited lines: #include <File.au3> #include <Array.au3> Global $DELIM = @TAB; Change delimiter here Global $avLines[1] Global $sFile = "C:\Temp\Test.txt" _FileReadToArray($sFile, $avLines) Global $avOut[$avLines[0] + 1][2] = [[$avLines[0], ""]]; Start out with minimum two columns ; First loop - for each row For $n = 1 To $avLines[0] ; Split the line $avSplit = StringSplit($avLines[$n], $DELIM) ; Test if more columns are needed If $avSplit[0] > UBound($avOut, 2) Then ReDim $avOut[$avLines[0] + 1][$avSplit[0]] ; Second loop for each column For $x = 1 To $avSplit[0] $avOut[$n][$x - 1] = $avSplit[$x] Next Next _ArrayDisplay($avOut, "$avOut") 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 Link to comment Share on other sites More sharing options...
gcue Posted August 13, 2008 Author Share Posted August 13, 2008 works wonderfully!! thank you!! BUT it only does 13 records instead of the 55,000 that are there dont see anywhere on the script that sets this limit... hmm Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 13, 2008 Share Posted August 13, 2008 works wonderfully!! thank you!!BUT it only does 13 records instead of the 55,000 that are theredont see anywhere on the script that sets this limit...hmm There is no such limit. Perhaps the file has a stray null, or Chr(0) in it, which flags EOF unless you parse it in binary mode.AutoIt has a limit for array elements, but it's in the millions not thousands. (See the help file.) 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 Link to comment Share on other sites More sharing options...
gcue Posted August 13, 2008 Author Share Posted August 13, 2008 ur right!! i do see some nulls.so what do you mean to parse it in binary mode?There is no such limit. Perhaps the file has a stray null, or Chr(0) in it, which flags EOF unless you parse it in binary mode.AutoIt has a limit for array elements, but it's in the millions not thousands. (See the help file.) Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 13, 2008 Share Posted August 13, 2008 ur right!! i do see some nulls. so what do you mean to parse it in binary mode? Read the file in binary mode, do BinaryToString() then StringReplace() to change the nulls. Like this: #include <File.au3> #include <Array.au3> Global $DELIM = @TAB; Change delimiter here Global $avLines[1] Global $sFile = "C:\Temp\Test.txt", $hFile, $binFileData, $sFileData ; Read file in binary mode $hFile = FileOpen($sFile, 16) $binFileData = FileRead($hFile) FileClose($hFile) ; Change nulls $sFileData = BinaryToString($binFileData, 1); <=== flag may have to be changed if Unicode $sFileData = StringReplace($sFileData, Chr(0), "<null>") ; Split lines $avLines = StringSplit($sFileData, @CRLF); <=== newline could be @LF (Unix), or @CR (Mac) Global $avOut[$avLines[0] + 1][2] = [[$avLines[0], ""]]; Start out with minimum two columns ; First loop - for each row For $n = 1 To $avLines[0] ; Split the line $avSplit = StringSplit($avLines[$n], $DELIM) ; Test if more columns are needed If $avSplit[0] > UBound($avOut, 2) Then ReDim $avOut[$avLines[0] + 1][$avSplit[0]] ; Second loop for each column For $x = 1 To $avSplit[0] $avOut[$n][$x - 1] = $avSplit[$x] Next Next _ArrayDisplay($avOut, "$avOut") 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 Link to comment Share on other sites More sharing options...
gcue Posted August 13, 2008 Author Share Posted August 13, 2008 ahh ya it looks like its getting most if not all of the records now..interestingly enough tho.. the output changesat first it skips every other line then it looks like its garbled up into one columit also took pretty long to generate the array.. not sure if thats related.. prob just cause of all the records/linescheck it out:http://www.postyourimage.com/view_image.ph...ddc8Y1218657012 Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 15, 2008 Share Posted August 15, 2008 ahh ya it looks like its getting most if not all of the records now..interestingly enough tho.. the output changesat first it skips every other line then it looks like its garbled up into one columit also took pretty long to generate the array.. not sure if thats related.. prob just cause of all the records/linescheck it out: http://www.postyourimage.com/view_image.ph...ddc8Y1218657012I can't view that image (at least from here).You will have to dig into the specifics of the dump file. Is it ANSI or Unicode? Is it really just TAB delimited, or did those nulls indicate significant formatting of some kind too? You need a long talk with the DB Admin that dumped the file, and a careful analysis of where parsing the file seems to go wrong. I don't think we can help much with that remotely.Can you post a short dump file of just a few dozen records? Sanitized data, of course. It would need to be short enough for us to work with easily, but show your symptoms when my last code posted is used on it (or you'll have to also provide a short demo script of how you are parsing it). 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 22, 2008 Share Posted August 22, 2008 heres a sample of the text file. the first set shows some nulls (in the arraydisplay, every other line is blank during this section) the second set (line 9 is where all the fields are being garbled into one column seperated by a "|") (in the arraydisplay, every other line is "||||||||||" during this section) i asked the person who exported this file and he no idea what i was talking about *sigh* i reeeeally appreciate your help PSalty!!! I ran my code from post #18 with your sample2.txt file and it worked perfectly. The only things that I tweaked were the file name and I added flag = 1 to the StringSplit() so the @CRLF delimiter would not generate blank lines. Are you seeing the symptom you describe when you run this with the sample2.txt file you posted? #include <File.au3> #include <Array.au3> Global $DELIM = @TAB; Change delimiter here Global $avLines[1] Global $sFile = "C:\Temp\Sample2.txt", $hFile, $binFileData, $sFileData ; Read file in binary mode $hFile = FileOpen($sFile, 16) $binFileData = FileRead($hFile) FileClose($hFile) ; Change nulls $sFileData = BinaryToString($binFileData, 1) $sFileData = StringReplace($sFileData, Chr(0), "<null>") ; Split lines $avLines = StringSplit($sFileData, @CRLF, 1) Global $avOut[$avLines[0] + 1][2] = [[$avLines[0], ""]]; Start out with minimum two columns ; First loop - for each row For $n = 1 To $avLines[0] ; Split the line $avSplit = StringSplit($avLines[$n], $DELIM) ; Test if more columns are needed If $avSplit[0] > UBound($avOut, 2) Then ReDim $avOut[$avLines[0] + 1][$avSplit[0]] ; Second loop for each column For $x = 1 To $avSplit[0] $avOut[$n][$x - 1] = $avSplit[$x] Next Next _ArrayDisplay($avOut, "$avOut") 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 Link to comment Share on other sites More sharing options...
gcue Posted August 22, 2008 Author Share Posted August 22, 2008 it works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! what was confusing me actually was the arraydisplay where all the records after 4000 were being garbled up into the first column.. then i looked at the help file and saw the limitations on the arraydisplay (4000 rows) many many many thanks PSsalty!!! you have truly helped! 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