Page 1 of 1 1
Topic Options
#108979 - 2003-11-26 02:37 PM read from csv file and report disk size and space
Grutte Pier Offline
Getting the hang of it

Registered: 2001-10-06
Posts: 52
Loc: Leeuwarden, The Netherlands
Hello,

I am trying to get a script working that reads from a MS Access db and writes values from that DB to a CSV file. This is working

But the second part of the script reads from the CSV file and should report (on screen) disksize and diskspace of the directory's from the CSV file. This does not work.

Eventualy I like to report back into the DB again but that has a second priority. The idea is to place an webinterface in front of the DB so directory's can be added and changed. And reports can be viewed in webpages.

Code:
Break on
$file = "C:\todo.csv"
$db = "C:\disksize.mdb"
Del "$file"
$connect = CreateObject("ADODB.Connection")
$connect_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$db"
$connect.Open($connect_str)
$query = "Select afdelingen.Afdelingsnaam, URL.URL, typeschijf.Type FROM typeschijf INNER Join (afdelingen INNER Join URL ON afdelingen.afdelingID = URL.afdelingID) ON typeschijf.typeId = URL.typeID;"
$query = $connect.Execute($query)

While NOT $query.EOF
$url=$query.Fields("URL").value
$OU=$query.Fields("afdelingsnaam").value
$type=$query.Fields("Type").value
$crlf = Chr(13) + Chr(10)
If Open ( 1, "$file" , 5 ) = 0
$rs = WriteLine ( 1, "$OU,$type,$URL,$crlf" )
$rs = Close ( 1 )
EndIf
$query.movenext
Loop


; Second part
If Exist("$DB") = 0 ? "Database Not Found. Aborting..." Sleep 3 GoTo end EndIf

$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DB;persist security info=false"
$CMDtxt = "Select afdelingen.Afdelingsnaam, URL.URL, typeschijf.Type FROM typeschijf INNER Join (afdelingen INNER Join URL ON afdelingen.afdelingID = URL.afdelingID) ON typeschijf.typeId = URL.typeID;"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring
$cn.Open
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd
$cmd.commandtext = $CMDtxt $rs.Open ($cmd)
If Open(1,$file) = 0
While @ERROR = 0
$record = ReadLine(1)
If NOT @error
If $record
$field = Split($record,",")
$objFSO = CreateObject("Scripting.FileSystemObject")
$objDrive = $objFSO.GetDrive("$field[2]")
"Drive " + $objDrive.DriveLetter + ": "+ $objDrive.VolumeName ?
"Total Size: " + FormatNumber($objDrive.TotalSize/1024, 0) ?
"Free Space: " + FormatNumber($objDrive.FreeSpace/1024, 0) ?
$objFSO=""
? $field[0]
? $field[1]
? $field[2]

EndIf
EndIf
Loop
Else
?"Error opening $file"
EndIf
$rs.update
$rs.Close





Thanks,

GP

Top
#108980 - 2003-11-26 02:45 PM Re: read from csv file and report disk size and space
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Hi GP, nice work! This line looks very suspicious though:

$objDrive = $objFSO.GetDrive("$field[2]")

Think you want to pass the value of $field[2], not a string containing the variable name ?

-Shawn

btw - can't make out your avatar ... what the heck is that ?

Top
#108981 - 2003-11-26 02:48 PM Re: read from csv file and report disk size and space
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Grutte,

You realize you can link your CSV (look under text ) into your Access DB? If you do that, you only have to create one object and work with it.

Kent
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#108982 - 2003-11-26 03:25 PM Re: read from csv file and report disk size and space
Grutte Pier Offline
Getting the hang of it

Registered: 2001-10-06
Posts: 52
Loc: Leeuwarden, The Netherlands
Hello Shawn,

I don't understand your remark on the code. Can you please give a example?

My avatar is a bad foto of al statue of a local freedom worior (from 1498).

Thanks for your response,

GP


Top
#108983 - 2003-11-26 03:28 PM Re: read from csv file and report disk size and space
Grutte Pier Offline
Getting the hang of it

Registered: 2001-10-06
Posts: 52
Loc: Leeuwarden, The Netherlands
Hello Kent,

No I don't realise I can link my CSV. Can you tell me some more? And what do you mean with "(look under text )".

Thaks for your response,

GP

Top
#108984 - 2003-11-26 03:48 PM Re: read from csv file and report disk size and space
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Well, it would seem your passing a string containing the characters "$field[2]" when what (I think) you want to do is pass the variable itself, without the quotes, like this:

$objDrive = $objFSO.GetDrive($field[2])

Top
#108985 - 2003-11-26 10:26 PM Re: read from csv file and report disk size and space
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Your Milage May Vary -
(1) Open your Access Database
(2) Go to Tables
(3) Right-Click and choose Link Tables...
(4) Under Files of Type, scroll down to choose "Text Files" (Access 97) You may need to type in the name.
(4a) Access 2003, you should see - Text Files (*.txt;*.csv;*.tab;*.asc)
(5) Click the Link Button
(6) Follow the link Text Wizard - It should pick it up as delimted
(7) Leave it using a Comma
(8) If you have headers on the first row, you should be set otherwise, you probably need to give some meaningful names
(9) Give the linked table a name and finish..
When properly linked, it should have a "notepad" icon for the table.

HTH,

Kent
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#108986 - 2003-11-27 02:56 PM Re: read from csv file and report disk size and space
Grutte Pier Offline
Getting the hang of it

Registered: 2001-10-06
Posts: 52
Loc: Leeuwarden, The Netherlands
Hello Shawn,

No that does not solve my problems.
Strange becouse I try to combine two working scripts.

The fist one reports diskize and space:
Code:

Break ON
$URLlist=url.txt
$scriptpath=H:\Projectjes\Disksize
If Open(1, $scriptpath + "\" + $URLlist) = 0
$URL = ReadLine(1)
While @ERROR = 0
$objFSO = CreateObject("Scripting.FileSystemObject")
$objDrive = $objFSO.GetDrive("$URL")
"Drive " + $objDrive.DriveLetter + ": "+ $objDrive.VolumeName ?
"Total Size: " + FormatNumber($objDrive.TotalSize/1024, 0) ?
"Free Space: " + FormatNumber($objDrive.FreeSpace/1024, 0) ?
$objFSO=""
$URL = ReadLine(1)
Loop
$ = Close (1)
Else
Beep
? "blah blah blah"
EndIf



And the second one reads from the DB:
Code:

Break on
$file = "C:\todo.csv"
$db = "C:\disksize.mdb"
Del "$file"
$connect = CreateObject("ADODB.Connection")
$connect_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$db"
$connect.Open($connect_str)
$query = "Select afdelingen.Afdelingsnaam, URL.URL, typeschijf.Type FROM typeschijf INNER Join (afdelingen INNER Join URL ON afdelingen.afdelingID = URL.afdelingID) ON typeschijf.typeId = URL.typeID;"
$query = $connect.Execute($query)

While NOT $query.EOF
$url=$query.Fields("URL").value
$OU=$query.Fields("afdelingsnaam").value
$type=$query.Fields("Type").value
$crlf = Chr(13) + Chr(10)
If Open ( 1, "$file" , 5 ) = 0
$rs = WriteLine ( 1, "$OU,$type,$URL,$crlf" )
$rs = Close ( 1 )
EndIf
$query.movenext
Loop

If Open(1,$file) = 0
While @ERROR = 0
$record = ReadLine(1)
If NOT @error
If $record
$field = Split($record,",")
? $field[0]
? $field[1]
? $field[2]

EndIf
EndIf
Loop
Else
?"Error opening $file"
EndIf



And both scripts work

but the combination of them does not work:
Code:

Break on
$file = "C:\todo.csv"
$db = "C:\disksize.mdb"
Del "$file"
$connect = CreateObject("ADODB.Connection")
$connect_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$db"
$connect.Open($connect_str)
$query = "Select afdelingen.Afdelingsnaam, URL.URL, typeschijf.Type FROM typeschijf INNER Join (afdelingen INNER Join URL ON afdelingen.afdelingID = URL.afdelingID) ON typeschijf.typeId = URL.typeID;"
$query = $connect.Execute($query)

While NOT $query.EOF
$url=$query.Fields("URL").value
$OU=$query.Fields("afdelingsnaam").value
$type=$query.Fields("Type").value
$crlf = Chr(13) + Chr(10)
If Open ( 1, "$file" , 5 ) = 0
$rs = WriteLine ( 1, "$OU,$type,$URL,$crlf" )
$rs = Close ( 1 )
EndIf
$query.movenext
Loop

If Open(1,$file) = 0
While @ERROR = 0
$record = ReadLine(1)
If NOT @error
If $record
$field = Split($record,",")
$objFSO = CreateObject("Scripting.FileSystemObject")
$objDrive = $objFSO.GetDrive($field[2])
"Drive " + $objDrive.DriveLetter + ": "+ $objDrive.VolumeName ?
"Total Size: " + FormatNumber($objDrive.TotalSize/1024, 0) ?
"Free Space: " + FormatNumber($objDrive.FreeSpace/1024, 0) ?
$objFSO=""
? $field[0]
? $field[1]
? $field[2]

EndIf
EndIf
Loop
Else
?"Error opening $file"
EndIf




Thanks,

GP

Top
#108987 - 2003-11-27 03:06 PM Re: read from csv file and report disk size and space
Grutte Pier Offline
Getting the hang of it

Registered: 2001-10-06
Posts: 52
Loc: Leeuwarden, The Netherlands
Kent,

I'll keep this (linking the csv file) in mind.

Thanks,

GP

Top
#108988 - 2004-01-23 09:46 AM Re: read from csv file and report disk size and space
Grutte Pier Offline
Getting the hang of it

Registered: 2001-10-06
Posts: 52
Loc: Leeuwarden, The Netherlands
Hello,

Sorry I've been away for a wile.

I've got a working script
Code:

Break on
$file = "C:\todo.csv"
$db = "C:\disksize.mdb"
Del "$file"
$connect = CreateObject("ADODB.Connection")
$connect_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$db"
$connect.Open($connect_str)
$query = "Select * FROM URL;"
$query = $connect.Execute($query)
While NOT $query.EOF
$URL=$query.Fields("URL").value
$URL_ID=$query.Fields("URL_ID").value
$crlf = Chr(13) + Chr(10)
If Open ( 1, "$file" , 5 ) = 0
$rs = WriteLine ( 1, "$URL_ID,$URL,$crlf" )
$rs = Close ( 1 )
EndIf

$query.movenext
Loop

; Second part
If Open(1,$file) = 0
While @ERROR = 0
$record = ReadLine(1)
If NOT @error
If $record
$field = Split($record,",")
$objFSO = CreateObject("Scripting.FileSystemObject")
$objDrive = $objFSO.GetDrive($field[1])
Break On CLS $StartTime = @DATE + " " + @TIME
If Exist("$DB") = 0 ? "Database Not Found. Aborting..." Sleep 3 GoTo end EndIf
$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DB;persist security info=false"
$CMDtxt = "Select * from schijfruimte"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring
$cn.Open
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd
$cmd.commandtext = $CMDtxt $rs.Open ($cmd)
$rs.addnew
$rs.fields.item("URL_ID").value = $field[0]
$rs.fields.item("totalsize").value = FormatNumber($objDrive.TotalSize/1024, 0)
$rs.fields.item("freesize").value = FormatNumber($objDrive.FreeSpace/1024, 0)
$rs.update
$rs.Close
? "drive is:" + $objDrive
$objFSO=""
Exit
EndIf
EndIf
Loop
Else
?"Error opening $file"
EndIf
:END



But it only works with drives and mapped drives. So I tried to map before I try to get the directory's size. But for some reason it does not work.
In this script:

Code:

Break on
$tmp_drive = "K:"
$tmp_file = "C:\in_between.csv"
$db = "C:\disksize2.mdb"
Del "$tmp_file"
$connect = CreateObject("ADODB.Connection")
$connect_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$db"
$connect.Open($connect_str)
$query = "Select * FROM URL;"
$query = $connect.Execute($query)
While NOT $query.EOF
$URL=$query.Fields("URL").value
$URL_ID=$query.Fields("URL_ID").value
$crlf = Chr(13) + Chr(10)
If Open ( 1, "$tmp_file" , 5 ) = 0
$rs = WriteLine ( 1, "$URL_ID,$URL,$crlf" )
$rs = Close ( 1 )
EndIf
$query.movenext
Loop

; Second part
If Open(1,$tmp_file) = 0
While @ERROR = 0
$record = ReadLine(1)
If NOT @error
If $record
$field = Split($record,",")
$objFSO = CreateObject("Scripting.FileSystemObject")
$objDrive = $objFSO.GetDrive($field[1])
Use $tmp_drive /delete
Use $tmp_drive $field[1]
Break On CLS $StartTime = @DATE + " " + @TIME
If Exist("$DB") = 0 ? "Database Not Found. Aborting..." Sleep 3 GoTo end EndIf
$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DB;persist security info=false"
$CMDtxt = "Select * from schijfruimte"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring
$cn.Open
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd
$cmd.commandtext = $CMDtxt $rs.Open ($cmd)
$rs.addnew
$rs.fields.item("URL_ID").value = $field[0]
$rs.fields.item("totalsize").value = FormatNumber($tmp_drive.TotalSize/1024, 0)
$rs.fields.item("freesize").value = FormatNumber($tmp_drive.FreeSpace/1024, 0)
$rs.update
$rs.Close
$objFSO=""
EndIf
EndIf
Loop
Else
?"Error opening $tmp_file"
EndIf

:END



Perhaps somone can tell me what goes wrong.

Thanks,

GP

Top
#108989 - 2004-01-23 04:57 PM Re: read from csv file and report disk size and space
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
See my answer at the KiXscripts BBS under http://www.kixscripts.com/forum/tm.asp?m=3819
_________________________
There are two types of vessels, submarines and targets.

Top
#108990 - 2004-01-27 10:54 AM Re: read from csv file and report disk size and space
Grutte Pier Offline
Getting the hang of it

Registered: 2001-10-06
Posts: 52
Loc: Leeuwarden, The Netherlands
Gread Jens,

Thanks.

The working code is:
Code:

Break on
$tmp_drive = "K:"
$tmp_file = "C:\in_between.csv"
$db = "C:\disksize2.mdb"
Del "$tmp_file"
$connect = CreateObject("ADODB.Connection")
$connect_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$db"
$connect.Open($connect_str)
$query = "Select * FROM URL;"
$query = $connect.Execute($query)
While NOT $query.EOF
$URL=$query.Fields("URL").value
$URL_ID=$query.Fields("URL_ID").value
$crlf = Chr(13) + Chr(10)
If Open ( 1, "$tmp_file" , 5 ) = 0
$rs = WriteLine ( 1, "$URL_ID,$URL,$crlf" )
$rs = Close ( 1 )
EndIf
$query.movenext
Loop

; Second part
If Open(1,$tmp_file) = 0
While @ERROR = 0
$record = ReadLine(1)
If NOT @error
If $record
$field = Split($record,",")
Use $tmp_drive /delete
Use $tmp_drive $field[1]
$objFSO = CreateObject("Scripting.FileSystemObject")
$objDrive = $objFSO.GetDrive($tmp_drive)
Break On CLS $StartTime = @DATE + " " + @TIME
If Exist("$DB") = 0 ? "Database Not Found. Aborting..." Sleep 3 GoTo end EndIf
$CNstring = "provider=microsoft.jet.oledb.4.0;data source=$DB;persist security info=false"
$CMDtxt = "Select * from schijfruimte"
$cn = CreateObject ("ADODB.Connection")
$cmd = CreateObject ("ADODB.Command")
$rs = CreateObject ("ADODB.RecordSet")
$cn.connectionstring = $CNstring
$cn.Open
$cmd.activeconnection = $cn
$rs.cursortype = 3
$rs.locktype = 3
$rs.activecommand = $cmd
$cmd.commandtext = $CMDtxt $rs.Open ($cmd)
$rs.addnew
$rs.fields.item("URL_ID").value = $field[0]
$rs.fields.item("totalsize").value = FormatNumber($objDrive.TotalSize/1024, 0)
$rs.fields.item("freesize").value = FormatNumber($objDrive.FreeSpace/1024, 0)
$rs.update
$rs.Close
$objFSO=""
EndIf
EndIf
Loop
Else
?"Error opening $tmp_file"
EndIf

:END



Now I can start working on my ASP code to make a interface for the database.

GP

Top
#108991 - 2004-01-27 04:13 PM Re: read from csv file and report disk size and space
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
You also might want to considere using the DB*() UDfs and removal of all the GOTOs in your code by optimizing the logics.
_________________________
There are two types of vessels, submarines and targets.

Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 248 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.107 seconds in which 0.064 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org