[RBASE-L] - Re: Counting items in a folder

Jason Kramer Mon, 19 Dec 2011 11:34:32 -0800

I have a few bits of code that may help. I took an approach similar to Karen, but I used _vbscript_ to get the file listing. This will allow you to collect file names and extensions that are longer than 8 and 3 characters respectively.

The first part is a SP that returns the count and creates a TEMP (or fills if the table already exists) table named FCount:
FName NOTE - The file name.
FExt NOTE - The file extension, may be NULL
FCDate DATETIME - When the file was created.
FMDate DATETIME - When the file was last changed
FSize INTEGER - The size of the file.
FType NOTE - The Windows filetype.

You can use FCount to sort/filter the files.

The next part is the _vbscript_ that gets the file list. It will only return files, directories are ignored.

The last part if another SP I wrote to check for the existence of a table since there was no built in R:Base command and I use the code frequently.

I'll paste all three files below. Please let me know if you have any questions.

************************************
-- FileCount.RMD
-- Jason Kramer
-- 12-19-2011

-- This file is used by the FileCount Stored procedure. It calls the
-- getfcount _vbscript_ file and then reads the resulting FCount.csv file into
-- the FCount TEMP table, and deletes the FCount csv file. It assumes the
-- getfcount and FCount.csv file will be in the same folder as the calling
-- database. It requires the name of the directory/folder in which to count
-- files as a paramter. Only files are counted, sub directories/folders and
-- any files in them are not counted.

-- Required Parameter:
-- vfctgtfol TEXT - The name of the target folder.

-- Return vaule - vfccount INTEGER - the count of files in the folder.
-- In addition, FCount is created/filled.

-- FCOUNT has the format:
-- FName NOTE - The file name.
-- FExt NOTE - The file extension, may be NULL
-- FCDate DATETIME - When the file was created.
-- FMDate DATETIME - When the file was last changed
-- FSize INTEGER - The size of the file.
-- FType NOTE - The Windows filetype.

SET VAR vfctchk INTEGER = NULL
SET VAR vfclcmd TEXT = NULL
SET VAR vfccount INTEGER = NULL

SET VAR vfctchk = (CALL chktbl ('FCount'))
IF vfctchk = 0 THEN
CREATE TEMP TABLE fcount (fname NOTE,fext NOTE,fcdate DATETIME,fmdate DATETIME,fsize INTEGER,ftype NOTE)
ELSE
DELETE FROM fcount
ENDIF

SET VAR vfclcmd = 'LAUNCH getfcount.vbs|' + .vfctgtfol + '|w'

&vfclcmd

GATEWAY IMPORT CSV fcount.csv REPLACE fcount OPTION SPECIFICATION_FILE_NAME fcount.rgw

DELETE fcount.csv

SELECT COUNT fname INTO vfccount FROM fcount

CLEAR VAR vfctchk,vfclcmd

RETURN .vfccount
********************************************
'getfcount.vbs
'Jason Kramer
'12-19-2011

'This _vbscript_ returns the count of all files in a folder as
'the SYTEM environment variable FCount.

option explicit

Dim iArgCount,folBaseFolder,fleCurFile,fso
Dim fleFCount,iExtStart,sFleExt
'Argument count,folder to in which to count files,the current file
'file system object,the output file,the file extention start position,the file extension

iArgCount = WScript.Arguments.Count
Set fso = CreateObject("Scripting.FileSystemObject")
Set fleFCount = fso.CreateTextFile("FCount.csv", True)

If iArgCount <> 1 Then
    MsgBox "Invalid number of arguments." + CStr(iArgCount),vbOkOnly,"ERROR"
Else
    If fso.FolderExists(WScript.Arguments(0)) Then
        Set folBaseFolder = fso.GetFolder(WScript.Arguments(0))
        For Each fleCurFile in folBaseFolder.Files
            iExtStart = InStrRev(fleCurFile.Name,".")
            If iExtStart = 0 Then
                sFleExt = ""
            Else
                sFleExt = Right(fleCurFile.Name,Len(fleCurFile.Name)-iExtStart)
            End If
            fleFCount.WriteLine fleCurFile.Name + "," + sFleExt + "," + CStr(fleCurFile.DateCreated) + "," + CStr(fleCurFile.DateLastModified) + "," + CStr(fleCurFile.Size) + "," + fleCurFile.Type
        Next
    Else
        MsgBox "Folder not found.",vbOkOnly,"ERROR"
    End If
End If

fleFcount.Close

MsgBox "Done",vbOkOnly,"DONE"
*******************************************
-- CHKTBL Stored Procedure
-- Jason Kramer
-- 12-12-2011

-- This RMD file is used by the CHKTBL stored procedure to check for the
-- exisitance of a table. It takes one parameters, the table name to check
-- for, and returns an integer.

-- Parameters - 1 required:
-- vctname TEXT (Len = 128) - The name of the table to check for.

-- Return Value - An integer with one of the following values:
-- 0 - No table found.
-- 1 - Table found.

SET VAR vctretval INTEGER = NULL

SELECT COUNT sys_table_name INTO vctretval FROM sys_tables WHERE sys_table_name = .vctname

RETURN vctretval
***************************************

Jason Kramer
University Archives and Records Management
002 Pearson Hall
(302) 831 - 3127 (voice)
(302) 831 - 6903 (fax)

On 12/19/2011 2:10 PM, Michael J. Sinclair wrote:

Would it make sense to ask RBase for a Plugin that would do this ?

I have some very crude code that works....but it's not very "elegant"

--my patient id number

SET VAR vpnum REAL
IF vpnum IS NULL THEN
RETURN
ENDIF

--convert to a text value, use the value to create the path to evaluate
SET VAR vpnumtxt = (CTXT(INT(.vpnum)))
SET VAR vpath = ('H:\' + .vpnumtxt + '\')
SET VAR vdocfolder TEXT

--use the run using command to change the document type such as ekg, labs, etc
SET VAR vdoctype = .%1
CLEAR VAR %1
SET VAR vdocfolder = (.vpath + .vdoctype)
OUTPUT c:\scratch\countfil.txt
DIR .vdocfolder
OUTPUT SCREEN

--the table countfile has 1 column, 80 characters wide, text
DELETE FROM countfile
LOAD countfile FROM c:\scratch\countfil.txt AS FORMATTED USING linedata 1 80
--from here, you need custome code, you can delete every line that does not contain '.'

--to get a count of how many files there are total

--after the extra rows are gone, you can use sget, slen etc to extract the size of the file

--in this example, I want count the number of pdf files in the folder

DELETE FROM countfile WHERE linedata NOT CONTAINS '.pdf'
SELECT COUNT linedata INTO vcount FROM countfile
SET VAR vcounttxt = (CTXT(.vcount))
SET VAR vcomponentid = ('id_' + .vdoctype)
--now update the appearance of the buttons, green for active, red for no pdf files

IF vcount > 0 THEN
SET VAR vcaption = (LUC(.vdoctype & .vcounttxt))
PROPERTY .vcomponentid caption .vcaption
PROPERTY .vcomponentid color GREEN
ELSE
SET VAR vcaption = (ULC(.vdoctype))
PROPERTY .vcomponentid color RED
PROPERTY .vcomponentid caption .vcaption
ENDIF
RETURN

From: Mike Byerley <[email protected]>
To: RBASE-L Mailing List <[email protected]>
Sent: Monday, December 19, 2011 1:25 PM
Subject: [RBASE-L] - Re: Counting items in a folder

You can check for existance with RBase ChkFile function.

Already have a 97% finished DLL that has a variety of File functions
including FileSize, FileCount (based on one or a series of File extensions),
FileVersionInfo and so on. Maybe in a few days or a week (depending on how
much interruption holiday stuff does) I can get it done sufficiently to put
in the wild for use. I do know it's been long enough since I worked on it
that the source code doesn't match what I started in the Help File, but like
I said, it's close to done.

----- Original Message -----
From: "Michael J. Sinclair" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Monday, December 19, 2011 12:08 PM
Subject: [RBASE-L] - Re: Counting items in a folder

Hi Mike,

There are a few scenarios where this would be helpful.

First checking for the existance of any file in the folder would be helpful
for error handling.

Sometimes files are created which should have a minmum size if the data is
good. Corrupt data ends up creating a file that is smaller than a certain
size. Being able to check for the existance of any files with a filesize
that is smaller than a predetermined limit would help the application to
call for help, call a different app, send an email etc.

Looking for the existance of a certain number of files such as jpg files
could trigger an app to create a single pdf file out of the group of jpg
files.

I would like to be able to count the number of files using various criteria
and display them by changing the properties (color and text) of certain
buttons that do things to the files, ie red for no files, green plus a
number to indicate the presence of a certain number of files in a folder.

Mike

________________________________
From: Mike Byerley <[email protected]>
To: RBASE-L Mailing List <[email protected]>
Sent: Monday, December 19, 2011 9:39 AM
Subject: [RBASE-L] - Re: Counting items in a folder

Mike,
Could you elaborate a bit on what you are trying to do? Filesize or
Filecount are pretty easy, but why do you want to search or display a file
that is of a certain size?

----- Original Message -----
From: "Michael J. Sinclair" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Sunday, December 18, 2011 8:38 AM
Subject: [RBASE-L] - Counting items in a folder

: Hi All,
: How would you count the number of files in a folder and capture that value
into a variable (assume no subfolders)?
: (If there were a PLUGIN for that, it would be great if the PLUGIN had the
ability to specify file characteristics that would limit the count, such as
count only files that are a certain size or created on or after a certain
date, had a certain extension, etc.)
: Mike

[RBASE-L] - Re: Counting items in a folder

Reply via email to