Folder/Sub-folder contents to a DB

Hello all, just wondering if I can can get some help here.
My company just purchased an EMR (Electronic Medical Records) system and we
need to perform a mass scanning offsite. The vendor is asking us to scan
patient files this way. The top folder is the patient account number the sub
folders contains the tabs where we want the files to be stored on the EMR
database and then inside of these sub-folders we will be scanning all the
files. What I need to do is to create a script that can monitor a Hard Drive
for all top folders (Account #'s) and all sub-folders and files and store the
folder names and file names into a databse for each top folder (account #).
Each database entry will need to have the account#, Tabname, filename. Once
Everything is scanned and the databse knows the location of all the files we
will then give an .csv file to the vendor with all the databse entries plus
the hard drive for a complete import to their system. I've looked everywhere
to see how can I accomplish this. So basically I will be creating folders
manually on the Hard Drive conataining the patients account#, the sub-folders
containing the tab names and then inside of the sub folders that's where the
scanned files will be. As for my database for the mass scanning it will be
an Access or SQL db containing only a table with pt_account,pt_tab,pt_image.

Any help would be greatly appreciated.

Thanks,

CarlosSantos
Tue Mar 14 07:35:04 CST 2006

For the database the file name must include the full path location of the
file for example s:\1234\lab results\test1.pdf
So when we give the .csv file plus the harddrive to the vendor all they
need to make sure is that the harddrive as the same drive letter s:\ in this
case when they do an import.

Thanks

"Carlos Santos" wrote:

> Hello all, just wondering if I can can get some help here.
> My company just purchased an EMR (Electronic Medical Records) system and we
> need to perform a mass scanning offsite. The vendor is asking us to scan
> patient files this way. The top folder is the patient account number the sub
> folders contains the tabs where we want the files to be stored on the EMR
> database and then inside of these sub-folders we will be scanning all the
> files. What I need to do is to create a script that can monitor a Hard Drive
> for all top folders (Account #'s) and all sub-folders and files and store the
> folder names and file names into a databse for each top folder (account #).
> Each database entry will need to have the account#, Tabname, filename. Once
> Everything is scanned and the databse knows the location of all the files we
> will then give an .csv file to the vendor with all the databse entries plus
> the hard drive for a complete import to their system. I've looked everywhere
> to see how can I accomplish this. So basically I will be creating folders
> manually on the Hard Drive conataining the patients account#, the sub-folders
> containing the tab names and then inside of the sub folders that's where the
> scanned files will be. As for my database for the mass scanning it will be
> an Access or SQL db containing only a table with pt_account,pt_tab,pt_image.
>
> Any help would be greatly appreciated.
>
> Thanks,

McKirahan
Tue Mar 14 08:35:38 CST 2006

"Carlos Santos" <CarlosSantos@discussions.microsoft.com> wrote in message
news:E52DA028-78EF-4311-BFFE-094720FD4080@microsoft.com...
> Hello all, just wondering if I can can get some help here.
> My company just purchased an EMR (Electronic Medical Records) system and
we
> need to perform a mass scanning offsite. The vendor is asking us to scan
> patient files this way. The top folder is the patient account number the
sub
> folders contains the tabs where we want the files to be stored on the EMR
> database and then inside of these sub-folders we will be scanning all the
> files. What I need to do is to create a script that can monitor a Hard
Drive
> for all top folders (Account #'s) and all sub-folders and files and store
the
> folder names and file names into a databse for each top folder (account
#).
> Each database entry will need to have the account#, Tabname, filename.
Once
> Everything is scanned and the databse knows the location of all the files
we
> will then give an .csv file to the vendor with all the databse entries
plus
> the hard drive for a complete import to their system. I've looked
everywhere
> to see how can I accomplish this. So basically I will be creating folders
> manually on the Hard Drive conataining the patients account#, the
sub-folders
> containing the tab names and then inside of the sub folders that's where
the
> scanned files will be. As for my database for the mass scanning it will
be
> an Access or SQL db containing only a table with
pt_account,pt_tab,pt_image.
>
> Any help would be greatly appreciated.
>
> Thanks,

Sounds like you need a consultant .... Anyway:

Which of these do you want the script to do?
""... into a databse .." [sic]
and/or
"... .csv file ..."
and/or
"... I will be creating folders manually ..."

The first step would be to rewrite your request into a "spec".

Start by declaring the inputs and the desired outputs.

It's hard to discern your needs from one long paragraph.

CarlosSantos
Tue Mar 14 15:36:29 CST 2006

We will be creating the folder/sub-folders and files manually. We just want
the contents path to be stored in the db. So basically I want to monitor a
directory called "scan" for example within scan we will create individual
folders for the patients named with their account number "1234" within these
folders we have sub-folders "lab results", "notes" etc... and within these
sub-folders that's where we will store the scanned documents. So what we
need is a script that reads anything below the "scan" folder and creates an
entry in the db for every file found within the sub-folders making an entry
on the db starting with the patient account number "1234" etc...

"McKirahan" wrote:

> "Carlos Santos" <CarlosSantos@discussions.microsoft.com> wrote in message
> news:E52DA028-78EF-4311-BFFE-094720FD4080@microsoft.com...
> > Hello all, just wondering if I can can get some help here.
> > My company just purchased an EMR (Electronic Medical Records) system and
> we
> > need to perform a mass scanning offsite. The vendor is asking us to scan
> > patient files this way. The top folder is the patient account number the
> sub
> > folders contains the tabs where we want the files to be stored on the EMR
> > database and then inside of these sub-folders we will be scanning all the
> > files. What I need to do is to create a script that can monitor a Hard
> Drive
> > for all top folders (Account #'s) and all sub-folders and files and store
> the
> > folder names and file names into a databse for each top folder (account
> #).
> > Each database entry will need to have the account#, Tabname, filename.
> Once
> > Everything is scanned and the databse knows the location of all the files
> we
> > will then give an .csv file to the vendor with all the databse entries
> plus
> > the hard drive for a complete import to their system. I've looked
> everywhere
> > to see how can I accomplish this. So basically I will be creating folders
> > manually on the Hard Drive conataining the patients account#, the
> sub-folders
> > containing the tab names and then inside of the sub folders that's where
> the
> > scanned files will be. As for my database for the mass scanning it will
> be
> > an Access or SQL db containing only a table with
> pt_account,pt_tab,pt_image.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks,
>
> Sounds like you need a consultant .... Anyway:
>
> Which of these do you want the script to do?
> ""... into a databse .." [sic]
> and/or
> "... .csv file ..."
> and/or
> "... I will be creating folders manually ..."
>
> The first step would be to rewrite your request into a "spec".
>
> Start by declaring the inputs and the desired outputs.
>
> It's hard to discern your needs from one long paragraph.
>
>
>

McKirahan
Tue Mar 14 17:07:21 CST 2006

"Carlos Santos" <CarlosSantos@discussions.microsoft.com> wrote in message
news:072BCC8B-AEF0-4312-BA5C-075CCED4E30C@microsoft.com...
> We will be creating the folder/sub-folders and files manually. We just
want
> the contents path to be stored in the db. So basically I want to monitor a
> directory called "scan" for example within scan we will create individual
> folders for the patients named with their account number "1234" within
these
> folders we have sub-folders "lab results", "notes" etc... and within these
> sub-folders that's where we will store the scanned documents. So what we
> need is a script that reads anything below the "scan" folder and creates
an
> entry in the db for every file found within the sub-folders making an
entry
> on the db starting with the patient account number "1234" etc...
>
Would this format be an example of what you want:

Given:
C:\scan\1234\notes\profile.doc
insert a row into a database table that contains:
1234\notes\profile.doc

If so, then you don't really need a script to do it.
(At least not to traverse the folder structure.)

You can use "attrib /s" from the Cmd prompt
and pipe the output to a file. For example:
C:\scan>attrib /s > EMR.txt

Then cleanup the result and import it into a database.

The cleanup, to delete the first 28 bytes of each line plus the
unwanted path info, could be scripted.

Will that work for you?

CarlosSantos
Tue Mar 14 22:38:29 CST 2006

That would work for us can you give me an example on how to accomplish this?
Deleting the first 28 bytes of each line on the text file?


"McKirahan" wrote:

> "Carlos Santos" <CarlosSantos@discussions.microsoft.com> wrote in message
> news:072BCC8B-AEF0-4312-BA5C-075CCED4E30C@microsoft.com...
> > We will be creating the folder/sub-folders and files manually. We just
> want
> > the contents path to be stored in the db. So basically I want to monitor a
> > directory called "scan" for example within scan we will create individual
> > folders for the patients named with their account number "1234" within
> these
> > folders we have sub-folders "lab results", "notes" etc... and within these
> > sub-folders that's where we will store the scanned documents. So what we
> > need is a script that reads anything below the "scan" folder and creates
> an
> > entry in the db for every file found within the sub-folders making an
> entry
> > on the db starting with the patient account number "1234" etc...
> >
> Would this format be an example of what you want:
>
> Given:
> C:\scan\1234\notes\profile.doc
> insert a row into a database table that contains:
> 1234\notes\profile.doc
>
> If so, then you don't really need a script to do it.
> (At least not to traverse the folder structure.)
>
> You can use "attrib /s" from the Cmd prompt
> and pipe the output to a file. For example:
> C:\scan>attrib /s > EMR.txt
>
> Then cleanup the result and import it into a database.
>
> The cleanup, to delete the first 28 bytes of each line plus the
> unwanted path info, could be scripted.
>
> Will that work for you?
>
>
>

McKirahan
Tue Mar 14 23:37:42 CST 2006

"Carlos Santos" <CarlosSantos@discussions.microsoft.com> wrote in message
news:57BF4B80-5041-41FB-973A-4A9A61C4855D@microsoft.com...
> That would work for us can you give me an example on how to accomplish
this?
> Deleting the first 28 bytes of each line on the text file?

This script will do that. A couple of notes though:
1) you may want remove the path prefix too; that is,
C:\scan\ (if that's your root folder).
2) The "attrib" command may only generate 8.3 filenames.

Option Explicit
'*
'* Declare Constants
'*
Const cVBS = "attrib.vbs"
Const cOT1 = "attrib1.txt"
Const cOT2 = "attrib2.txt"
'*
Const ForReading = 1
Const ForWriting = 2
'*
'* Declare Variables
'*
Dim arrOT1
Dim intOT1
Dim strOT1
Dim intOT2
intOT2 = 0
Dim strOT2
'*
'* Declare Objects
'*
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objOT1
Set objOT1 = objFSO.OpenTextFile(cOT1,ForReading)
Dim objOT2
Set objOT2 = objFSO.OpenTextFile(cOT2,ForWriting,True)
'*
'* Read and Write records starting at position 28
'*
strOT1 = objOT1.ReadAll()
arrOT1 = Split(strOT1,vbCrLf)
For intOT1 = 0 To UBound(arrOT1)
strOT2 = arrOT1(intOT1)
strOT2 = Mid(strOT2,28)
objOT2.WriteLine(strOT2)
intOT2 = intOT2 + 1
Next
'*
'* Destroy Objects
'*
Set objOT1 = Nothing
Set objOT2 = Nothing
Set objFSO = Nothing
'*
'* Finished
'*
MsgBox FormatNumber(intOT2,0) & " records",vbInformation,cVBS

CarlosSantos
Thu Mar 16 17:40:28 CST 2006

Thanks McKirahan for your help it works like a charm. I created a batch file
that runs the attrib /s and saves it to attrib1.txt and then calls this vbs
script (I just added -1 to the line "For intOT1 = 0 To UBound(arrOT1) -1" so
it doesn't create a empty line at the end of the attrib2.txt file) and then
imported the attib2.txt file to the database using dtsrun and a dtspackage.
It all works great.

I just need a way to create folders/subfolders according to the input the
user enters for the top folder. For example they run the script the script
will ask the user for folder name the user enters the folder name and the
script creates the folder plus a group of subfolders (the group subfolders
are all the same for all top folders) the script needs to keep looping (stay
open after the folder/subfolders get created at the prompt where it asks for
the next folder name) is there a way to accomplish this? The root where all
top folders will be created will be the same c:\root\**user specified
foldername**\sub1
c:\root\**user specified foldername**\sub2 etc....


Thanks again for all of your help, you saved me alot of work.

"McKirahan" wrote:

> "Carlos Santos" <CarlosSantos@discussions.microsoft.com> wrote in message
> news:57BF4B80-5041-41FB-973A-4A9A61C4855D@microsoft.com...
> > That would work for us can you give me an example on how to accomplish
> this?
> > Deleting the first 28 bytes of each line on the text file?
>
> This script will do that. A couple of notes though:
> 1) you may want remove the path prefix too; that is,
> C:\scan\ (if that's your root folder).
> 2) The "attrib" command may only generate 8.3 filenames.
>
> Option Explicit
> '*
> '* Declare Constants
> '*
> Const cVBS = "attrib.vbs"
> Const cOT1 = "attrib1.txt"
> Const cOT2 = "attrib2.txt"
> '*
> Const ForReading = 1
> Const ForWriting = 2
> '*
> '* Declare Variables
> '*
> Dim arrOT1
> Dim intOT1
> Dim strOT1
> Dim intOT2
> intOT2 = 0
> Dim strOT2
> '*
> '* Declare Objects
> '*
> Dim objFSO
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Dim objOT1
> Set objOT1 = objFSO.OpenTextFile(cOT1,ForReading)
> Dim objOT2
> Set objOT2 = objFSO.OpenTextFile(cOT2,ForWriting,True)
> '*
> '* Read and Write records starting at position 28
> '*
> strOT1 = objOT1.ReadAll()
> arrOT1 = Split(strOT1,vbCrLf)
> For intOT1 = 0 To UBound(arrOT1)
> strOT2 = arrOT1(intOT1)
> strOT2 = Mid(strOT2,28)
> objOT2.WriteLine(strOT2)
> intOT2 = intOT2 + 1
> Next
> '*
> '* Destroy Objects
> '*
> Set objOT1 = Nothing
> Set objOT2 = Nothing
> Set objFSO = Nothing
> '*
> '* Finished
> '*
> MsgBox FormatNumber(intOT2,0) & " records",vbInformation,cVBS
>
>
>

McKirahan
Thu Mar 16 18:24:05 CST 2006

"Carlos Santos" <CarlosSantos@discussions.microsoft.com> wrote in message
news:FBFEC0C0-EF54-4C8B-B238-349A44BB177F@microsoft.com...
> Thanks McKirahan for your help it works like a charm. I created a batch
file
> that runs the attrib /s and saves it to attrib1.txt and then calls this
vbs
> script (I just added -1 to the line "For intOT1 = 0 To UBound(arrOT1) -1"
so
> it doesn't create a empty line at the end of the attrib2.txt file) and
then
> imported the attib2.txt file to the database using dtsrun and a
dtspackage.
> It all works great.

You're wecome.

> I just need a way to create folders/subfolders according to the input the
> user enters for the top folder. For example they run the script the
script
> will ask the user for folder name the user enters the folder name and the
> script creates the folder plus a group of subfolders (the group subfolders
> are all the same for all top folders) the script needs to keep looping
(stay
> open after the folder/subfolders get created at the prompt where it asks
for
> the next folder name) is there a way to accomplish this? The root where
all
> top folders will be created will be the same c:\root\**user specified
> foldername**\sub1
> c:\root\**user specified foldername**\sub2 etc....

Will this help? atch for word-wrap.

Chnage "cFOL" to modify list of subfolders created.

'****
'* This Visu