read csv file for data

Hello, I am looking for a script that will find data from a .mdb file if the
data in the first colmn match's the data that I am looking for, if it finds
stnumber, then assign the data from the corrisponding colums from that row
back to Variables in the script, the table would look like:


stnumber storename 1sto 2nd0 3rdo
100 grocery 100 120 204

there will be 200 or so rows, and if it finds the stnumber that was inputed
I need the next 4 colums brought back to the script to make a IP address.


Thanks to anyone that takes the time to read this.

Ato
Wed Apr 06 21:31:53 CDT 2005

I'm assuming you want to read an MDB file and not a CSV file as specified in
your subject line.

Try the following code snippet. You have to change dbName to your MDB
filename and
you also have to add the code for inputting the stnumber:
'---------------------------------------------------------------------------
-------
set dbEngine = createobject("dao.dbengine.36")
dbName = "C:\yourdir\yourdbname.mdb"
set db = dbEngine.opendatabase(dbName)

stnumber = 100 '<-- this is your input variable
sql = "SELECT stnumber, storename, 1sto, 2ndo, 3rdo, 4tho FROM
IP_ADDRESSES WHERE stnumber = " & CStr(stnumber)
set rs = db.OpenRecordSet(sql)
rs.MoveFirst
If Not rs.EOF Then
Wscript.echo rs("1sto"), rs("2ndo"), rs("3rdo"), rs("4tho")
Else
Wscript.echo "record not found"
End If

rs.Close
Set rs = Nothing
Set db = nothing
Set dbEngine = Nothing

"Terry" <tester@mts.net> wrote in message
news:eqUap2uOFHA.576@TK2MSFTNGP15.phx.gbl...
> Hello, I am looking for a script that will find data from a .mdb file if
the
> data in the first colmn match's the data that I am looking for, if it
finds
> stnumber, then assign the data from the corrisponding colums from that row
> back to Variables in the script, the table would look like:
>
>
> stnumber storename 1sto 2nd0 3rdo
> 100 grocery 100 120 204
>
> there will be 200 or so rows, and if it finds the stnumber that was
inputed
> I need the next 4 colums brought back to the script to make a IP address.
>
>
> Thanks to anyone that takes the time to read this.
>
>

Rafael
Wed Apr 06 21:37:12 CDT 2005

Terry,

Your post is somewhat confusing because your subject line reads "CSV file"
but thepost mentions an mdb file.
Usually an MDB file saves data in a different format.

Assuming you want a CSV file:
- Read the file a line at a time
- Check if the first block of text is the one you are looking for (find from
0 to first ,)
- If it matches look for the second , and grab from there till the end of
the line
- put the whole thing together and show it

Here is a sample:
dim fileName
fileName=inputbox("Type path and file name","Enter info")
set objFS=CreateObject ("Scripting.FileSystemObject")

dim line
dim IP
set listFile = objFS.OpenTextFile (fileName)
do while listFile.AtEndOfStream <> True
line=listFile.ReadLine

'this line looks for the match
if left(line,instr(line,",")-1)="200" then

'thisline concatenates the first column with the last 3
IP=left(line,instr(line,",")-1) &
right(line,instr(instr(line,",")+1,line,","))
msgbox IP
end if
Loop

*you can later on change the , with a . by doing lefts and instr or easier
with RegularExpressions.

Assuming you are working on an Access mdb file
Use VBA and a SQL query which will make things really easy

select stnumber,1sto,2nd0, 3rd0 from table where stnumber=100

you can receive the result from the query in a variable and then work with
it.

hope this helps,
RT
"Terry" <tester@mts.net> wrote in message
news:eqUap2uOFHA.576@TK2MSFTNGP15.phx.gbl...
> Hello, I am looking for a script that will find data from a .mdb file if
> the data in the first colmn match's the data that I am looking for, if it
> finds stnumber, then assign the data from the corrisponding colums from
> that row back to Variables in the script, the table would look like:
>
>
> stnumber storename 1sto 2nd0 3rdo
> 100 grocery 100 120 204
>
> there will be 200 or so rows, and if it finds the stnumber that was
> inputed I need the next 4 colums brought back to the script to make a IP
> address.
>
>
> Thanks to anyone that takes the time to read this.
>

Ato
Wed Apr 06 21:38:23 CDT 2005

I forgot - you also need to change the SQL statement table name
"IP_ADDRESSES" to whatever table name you're using to store the IP address
octets.

"Ato Bisda" <atobisda@gmail.com> wrote in message
news:OAiO4nxOFHA.524@TK2MSFTNGP09.phx.gbl...
> I'm assuming you want to read an MDB file and not a CSV file as specified
in
> your subject line.
>
> Try the following code snippet. You have to change dbName to your MDB
> filename and
> you also have to add the code for inputting the stnumber:
>
'---------------------------------------------------------------------------
> -------
> set dbEngine = createobject("dao.dbengine.36")
> dbName = "C:\yourdir\yourdbname.mdb"
> set db = dbEngine.opendatabase(dbName)
>
> stnumber = 100 '<-- this is your input variable
> sql = "SELECT stnumber, storename, 1sto, 2ndo, 3rdo, 4tho FROM
> IP_ADDRESSES WHERE stnumber = " & CStr(stnumber)
> set rs = db.OpenRecordSet(sql)
> rs.MoveFirst
> If Not rs.EOF Then
> Wscript.echo rs("1sto"), rs("2ndo"), rs("3rdo"), rs("4tho")
> Else
> Wscript.echo "record not found"
> End If
>
> rs.Close
> Set rs = Nothing
> Set db = nothing
> Set dbEngine = Nothing
>
> "Terry" <tester@mts.net> wrote in message
> news:eqUap2uOFHA.576@TK2MSFTNGP15.phx.gbl...
> > Hello, I am looking for a script that will find data from a .mdb file if
> the
> > data in the first colmn match's the data that I am looking for, if it
> finds
> > stnumber, then assign the data from the corrisponding colums from that
row
> > back to Variables in the script, the table would look like:
> >
> >
> > stnumber storename 1sto 2nd0 3rdo
> > 100 grocery 100 120 204
> >
> > there will be 200 or so rows, and if it finds the stnumber that was
> inputed
> > I need the next 4 colums brought back to the script to make a IP
address.
> >
> >
> > Thanks to anyone that takes the time to read this.
> >
> >
>
>

Terry
Wed Apr 06 22:58:01 CDT 2005

thanks for your help, I actually meant mdb.


"Rafael T" <okinawapro@hotmail.com> wrote in message
news:OBokopxOFHA.244@TK2MSFTNGP12.phx.gbl...
> Terry,
>
> Your post is somewhat confusing because your subject line reads "CSV file"
> but thepost mentions an mdb file.
> Usually an MDB file saves data in a different format.
>
> Assuming you want a CSV file:
> - Read the file a line at a time
> - Check if the first block of text is the one you are looking for (find
> from 0 to first ,)
> - If it matches look for the second , and grab from there till the end of
> the line
> - put the whole thing together and show it
>
> Here is a sample:
> dim fileName
> fileName=inputbox("Type path and file name","Enter info")
> set objFS=CreateObject ("Scripting.FileSystemObject")
>
> dim line
> dim IP
> set listFile = objFS.OpenTextFile (fileName)
> do while listFile.AtEndOfStream <> True
> line=listFile.ReadLine
>
> 'this line looks for the match
> if left(line,instr(line,",")-1)="200" then
>
> 'thisline concatenates the first column with the last 3
> IP=left(line,instr(line,",")-1) &
> right(line,instr(instr(line,",")+1,line,","))
> msgbox IP
> end if
> Loop
>
> *you can later on change the , with a . by doing lefts and instr or easier
> with RegularExpressions.
>
> Assuming you are working on an Access mdb file
> Use VBA and a SQL query which will make things really easy
>
> select stnumber,1sto,2nd0, 3rd0 from table where stnumber=100
>
> you can receive the result from the query in a variable and then work with
> it.
>
> hope this helps,
> RT
> "Terry" <tester@mts.net> wrote in message
> news:eqUap2uOFHA.576@TK2MSFTNGP15.phx.gbl...
>> Hello, I am looking for a script that will find data from a .mdb file if
>> the data in the first colmn match's the data that I am looking for, if it
>> finds stnumber, then assign the data from the corrisponding colums from
>> that row back to Variables in the script, the table would look like:
>>
>>
>> stnumber storename 1sto 2nd0 3rdo
>> 100 grocery 100 120 204
>>
>> there will be 200 or so rows, and if it finds the stnumber that was
>> inputed I need the next 4 colums brought back to the script to make a IP
>> address.
>>
>>
>> Thanks to anyone that takes the time to read this.
>>
>
>

Terry
Wed Apr 06 22:58:25 CDT 2005

worked like a charm thanks for everything.


"Ato Bisda" <atobisda@gmail.com> wrote in message
news:OGnjgrxOFHA.1176@TK2MSFTNGP12.phx.gbl...
>I forgot - you also need to change the SQL statement table name
> "IP_ADDRESSES" to whatever table name you're using to store the IP address
> octets.
>
> "Ato Bisda" <atobisda@gmail.com> wrote in message
> news:OAiO4nxOFHA.524@TK2MSFTNGP09.phx.gbl...
>> I'm assuming you want to read an MDB file and not a CSV file as specified
> in
>> your subject line.
>>
>> Try the following code snippet. You have to change dbName to your MDB
>> filename and
>> you also have to add the code for inputting the stnumber:
>>
> '---------------------------------------------------------------------------
>> -------
>> set dbEngine = createobject("dao.dbengine.36")
>> dbName = "C:\yourdir\yourdbname.mdb"
>> set db = dbEngine.opendatabase(dbName)
>>
>> stnumber = 100 '<-- this is your input variable
>> sql = "SELECT stnumber, storename, 1sto, 2ndo, 3rdo, 4tho FROM
>> IP_ADDRESSES WHERE stnumber = " & CStr(stnumber)
>> set rs = db.OpenRecordSet(sql)
>> rs.MoveFirst
>> If Not rs.EOF Then
>> Wscript.echo rs("1sto"), rs("2ndo"), rs("3rdo"), rs("4tho")
>> Else
>> Wscript.echo "record not found"
>> End If
>>
>> rs.Close
>> Set rs = Nothing
>> Set db = nothing
>> Set dbEngine = Nothing
>>
>> "Terry" <tester@mts.net> wrote in message
>> news:eqUap2uOFHA.576@TK2MSFTNGP15.phx.gbl...
>> > Hello, I am looking for a script that will find data from a .mdb file
>> > if
>> the
>> > data in the first colmn match's the data that I am looking for, if it
>> finds
>> > stnumber, then assign the data from the corrisponding colums from that
> row
>> > back to Variables in the script, the table would look like:
>> >
>> >
>> > stnumber storename 1sto 2nd0 3rdo
>> > 100 grocery 100 120 204
>> >
>> > there will be 200 or so rows, and if it finds the stnumber that was
>> inputed
>> > I need the next 4 colums brought back to the script to make a IP
> address.
>> >
>> >
>> > Thanks to anyone that takes the time to read this.
>> >
>> >
>>
>>
>
>