Jump to content

how to query a specific column


gcue
 Share

Recommended Posts

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

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 by Kerros

Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Link to comment
Share on other sites

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

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

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

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.

:P

BTW: Is it really required to work with this text file? AutoIt can access a database directly, with ADODB for example.

:P

Edited 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

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

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.

:P

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

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.

:P

BTW: Is it really required to work with this text file? AutoIt can access a database directly, with ADODB for example.

:P

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

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 by gcue
Link to comment
Share on other sites

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")

:P

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

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 :P

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.)

:P

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

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.)

:P

Link to comment
Share on other sites

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")

:P

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

ahh ya it looks like its getting most if not all of the records now..

interestingly enough tho.. the output changes

at first it skips every other line then it looks like its garbled up into one colum

it also took pretty long to generate the array.. not sure if thats related.. prob just cause of all the records/lines

check it out:

http://www.postyourimage.com/view_image.ph...ddc8Y1218657012

Link to comment
Share on other sites

ahh ya it looks like its getting most if not all of the records now..

interestingly enough tho.. the output changes

at first it skips every other line then it looks like its garbled up into one colum

it also took pretty long to generate the array.. not sure if thats related.. prob just cause of all the records/lines

check it out: http://www.postyourimage.com/view_image.ph...ddc8Y1218657012

I 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).

:P

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...