Quality Control: Check file names in a folder against filenames in a table
0
votes
1
answer
273
views
I have a records system in an Oracle database. The records system is used to retrieve pdf, jpeg, video documents, etc.
The design is quite simple: there is a table called
infrastructure_records
that has a column with hyperlinks to files:
+------------+------------------------------------------+
| project_id | file_path |
+------------+------------------------------------------+
| Project 1 | X:\Records_System\Folder A\Project 1.pdf |
| Project 2 | X:\Records_System\Folder B\Project 2.jpg |
| Project 3 | X:\Records_System\Folder C\Project 3.mpg |
+------------+------------------------------------------+
We use a front-end that creates a clickable hyperlink out of the file paths. Using the hyperlink, users can navigate to the files.
**Question:**
I want to check the files on the network drive against the list of files in the database table.
If I could get a list of files from the network into a database table, then I could easily query for errors.
However, I'm not sure what the best way would be to create the list of files in an Oracle table.
- There are approximately 60,000 files on the network drive, nestled in a variety of subfolders
- The files don't exist on the same server as the Oracle database (I'm not an I.T. guy, so I'm pretty clueless about this sort of thing). However, I do have the drive mapped in Windows Explorer on the computer that I would be running the query from.
- This QC operation would be run about once per month.
- Performance isn't a huge concern. The list of files doesn't need to be "live", although that would certainly be a plus.
- I can think of quick-and-dirty ways to do this using VBA scripting in MS Office or the like. But, it has occurred to me that there might be a more professional or out-of-box way to do this (perhaps using built-in Oracle functionality).
**How can a I elegantly insert a list of files on a network drive into an Oracle table?**
Asked by User1974
(1527 rep)
Jan 9, 2018, 08:58 PM
Last activity: Jan 13, 2018, 04:12 PM
Last activity: Jan 13, 2018, 04:12 PM