#108979 - 2003-11-26 02:37 PM
read from csv file and report disk size and space
|
Grutte Pier
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
|
|
|
|
#108981 - 2003-11-26 02:48 PM
Re: read from csv file and report disk size and space
|
Kdyer
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
|
Top
|
|
|
|
#108985 - 2003-11-26 10:26 PM
Re: read from csv file and report disk size and space
|
Kdyer
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
|
Top
|
|
|
|
#108986 - 2003-11-27 02:56 PM
Re: read from csv file and report disk size and space
|
Grutte Pier
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
|
|
|
|
#108988 - 2004-01-23 09:46 AM
Re: read from csv file and report disk size and space
|
Grutte Pier
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
|
|
|
|
#108990 - 2004-01-27 10:54 AM
Re: read from csv file and report disk size and space
|
Grutte Pier
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
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 331 anonymous users online.
|
|
|