| Grutte Pier |
| (Getting the hang of it) |
| 2003-11-26 02:37 PM |
|
read from csv file and report disk size and space
|
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
Shawn
|
| (KiX Supporter) |
| 2003-11-26 02:45 PM |
|
|
Re: read from csv file and report disk size and space
|
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 ?
| Kdyer |
| (KiX Supporter) |
| 2003-11-26 02:48 PM |
|
|
Re: read from csv file and report disk size and space
|
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
| Grutte Pier |
| (Getting the hang of it) |
| 2003-11-26 03:25 PM |
|
|
|
Re: read from csv file and report disk size and space
|
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
| Grutte Pier |
| (Getting the hang of it) |
| 2003-11-26 03:28 PM |
|
|
|
Re: read from csv file and report disk size and space
|
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
|
Shawn
|
| (KiX Supporter) |
| 2003-11-26 03:48 PM |
|
|
|
Re: read from csv file and report disk size and space
|
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])
| Kdyer |
| (KiX Supporter) |
| 2003-11-26 10:26 PM |
|
|
|
Re: read from csv file and report disk size and space
|
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
| Grutte Pier |
| (Getting the hang of it) |
| 2003-11-27 02:56 PM |
|
|
|
Re: read from csv file and report disk size and space
|
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
| Grutte Pier |
| (Getting the hang of it) |
| 2003-11-27 03:06 PM |
|
|
|
Re: read from csv file and report disk size and space
|
Kent,
I'll keep this (linking the csv file) in mind.
Thanks,
GP
| Grutte Pier |
| (Getting the hang of it) |
| 2004-01-23 09:46 AM |
|
|
|
Re: read from csv file and report disk size and space
|
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
| Sealeopard |
| (KiX Master) |
| 2004-01-23 04:57 PM |
|
|
Re: read from csv file and report disk size and space
|
See my answer at the KiXscripts BBS under http://www.kixscripts.com/forum/tm.asp?m=3819
| Grutte Pier |
| (Getting the hang of it) |
| 2004-01-27 10:54 AM |
|
|
|
Re: read from csv file and report disk size and space
|
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
| Sealeopard |
| (KiX Master) |
| 2004-01-27 04:13 PM |
|
|
|
Re: read from csv file and report disk size and space
|
You also might want to considere using the DB*() UDfs and removal of all the GOTOs in your code by optimizing the logics.