Get file names from directory??
-
Hello,
I have a photo table in SQL Server with photo name for the main photo and photo's ID. All photos are stored on some drive on disk on some server. Now subsequent photo names for photos are found only in the directory (not stored in any SQL Server table like main photo names are). I have a table that needs to be populated with a PhotoID, PhotNames (however many photos there are) for that PhotoID and photo path. PhotoID and path I can get from a SQL Server table, but only can get the main photo name from a SQL Server table and the rest will have to be looked up in the directory where photos reside based on photo's ID. What would be the best way to do this in DTS? How can I get main photo name from SQL Server table and subsequent photo names from a directory based on a photo ID?
If anyone has any suggestions, please help.
Thanks.
JN
-
It will probably be something like
if NOT exists (select * FROM dbo.sysobjects where id = object_id(N'[TRC_TEMP_TABLE1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [TRC_TEMP_TABLE1] ( [Input_Data] [varchar] (300) , [FileDate] [datetime] NULL , [FileDateTime] [datetime] NULL ) ON [PRIMARY] END INSERT into TRC_TEMP_TABLE1 (Input_Data) exec xp_cmdshell 'dir ?photo path?\*.* /S'And then parse it out to completion.
Not pretty, but the only way I can think of. Hopefully the PhotoID and photo path has some relation to the file ID.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Yeah, I also came up with that solution from talking to a co-worker, but now another issue. First, following is my syntax, which works fine:
Exec master..xp_cmdShell 'dir \\web-jnn-iisjnn1\mn\public\106-jnn\jnn_ID_PHOTOS /s /b /w /a-d'
The command above takes a long-long time to run, as there are over a million photos and some 2000 subdirectories to browse through. Let's say I let it run as long as it takes and populate my temp table with returned values. Once I populate the temp table with all returned values, I will keep it. Now, the next time I run this command I only want to return the NEW photo names that have been stored in this directory/subdirectories since my last INSERT? Or I need to find a way to insert only the newly saved photo names from this directory or photo names that are NOT already found in the temp table after the initial insert of all photo names. Any suggestion on this?
Thanks.
JN
-
Aaaaagggghhhhh!
Are you using the same path name for everything? Or until you known the actual location of the photo's file?
Are they broken into sub-directories?
Does it go faster if you do a search such as "dir \\web-jnn-iisjnn1\mn\public\106-jnn\jnn_ID_PHOTOS\*photoID* /S"
or
"dir \\web-jnn-iisjnn1\mn\public\106-jnn\jnn_ID_PHOTOS\*.* /S"
And then run something like
DELETE FROM TRC_TEMP_TABLE1 WHERE Input_Data NOT LIKE '%.jpg%' OR Input_Data IS NULL
My thinking is take the work off the DOS command and put it in SQL Server. That is how I'm doing it.
And then follow on with:
UPDATE TRC_TEMP_TABLE1 SET FileDate = CAST (LEFT(Input_Data,10) AS DATETIME ), FileDateTime = CAST (LEFT(Input_Data,10) + ' ' + SUBSTRING(Input_Data, 12,6) AS DATETIME )This way you can just look for file create times greater than the last run time.
Just my $0.02.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Yeah, the photo names are actually in various sub-directories. There are 2005 sub-directories containnig photo names for over a million photos. Yes, the path is same + a subdirectory. This is how it goes:
PATH = path + RIGHT(PhotoID, 3)
PhotoName = PhotoID*.jpg
Photo 123456.jpg will be found in sub-directory 456.
I have to write a complicated process to do all these so let's see if I ever get to the final result!
Thanks for your help and suggestions.
JN
-
Try this thought on.....
Can you add a date field to the table that defaults to the time added. Then you go and strip out and group the photos/directories that have been changed since last run:
Declare @String as Varchar(300) Declare Cursor SrchFldrs AS Select left(photoid,3) from photo_table where DfltTime > (select LastRun From RunTable) group left(photoid,3) Fetch 'dir ?photo path?\ ' + SrchFldrs + ' /S' Into @String WHILE (@@fetch_status = 0) begin INSERT into TRC_TEMP_TABLE1 (Input_Data) exec xp_cmdshell @String Fetch 'dir ?photo path?\ ' + SrchFldrs + ' /S' Into @String end close SrchFldrs deallocate SrchFldrsThat way you are only searching a limited number of directories.
You may want to do your processing in and clear the table in the cursor loop. It all depends on how your data lays out.
Just throwing this out there.
It may fly
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I found a command line solution to what I need to do, but it's not completely working...
Exec master..xp_cmdShell 'xcopy \\web-jnn-iisjnn1\metros\public\106-colo\JNN_ID_PHOTOS\*.jpg C:\TEMP\*.* /D:3-21-05 /EXCLUDE:\Thumbnails\ /S /L'
If I remove the EXCLUDE:\Thumbnails\ then it seems to work, but if I have Exclude... then I get following error:
Output
Can't read file: \Thumbnails
NULL
0 File(s)
NULL
Anyone know if my commandline above is not written correctly or if there is a different command for what I am trying to do?
Thanks.
JN
-
Are you trying to exclude a directory(ies) named "\Thumbnails\" or the "Thumbs.db" files.
I would try it as just "/S /L /EXCLUDE:\Thumbnails"
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I am trying to exclude the directory \Thumbnails. I tried your suggestion, but get the same error. See following that I got from DOS... but it doesn't work for me.
XCOPY source [destination] [/A | /M] [/D[:date]] [/P] [/S [/E]] [/V] [/W]
[/C] [/I] [/Q] [/F] [/L] [/H] [/R] [/T] [/U]
[/K] [/N] [/O] [/X] [/Y] [/-Y] [/Z]
[/EXCLUDE:file1[+file2][+file3]...]
source Specifies the file(s) to copy.
destination Specifies the location and/or name of new files.
/A Copies only files with the archive attribute set,
doesn't change the attribute.
/M Copies only files with the archive attribute set,
turns off the archive attribute.
/D:m-d-y Copies files changed on or after the specified date.
If no date is given, copies only those files whose
source time is newer than the destination time.
/EXCLUDE:file1[+file2][+file3]...
Specifies a list of files containing strings. When any of the
strings match any part of the absolute path of the file to be
copied, that file will be excluded from being copied. For
example, specifying a string like \obj\ or .obj will exclude
all files underneath the directory obj or all files with the
.obj extension respectively.
/P Prompts you before creating each destination file.
/S Copies directories and subdirectories except empty ones.
/E Copies directories and subdirectories, including empty ones.
Same as /S /E. May be used to modify /T.
/V Verifies each new file.
/W Prompts you to press a key before copying.
/C Continues copying even if errors occur.
/I If destination does not exist and copying more than one file,
assumes that destination must be a directory.
/Q Does not display file names while copying.
/F Displays full source and destination file names while copying.
/L Displays files that would be copied.
/H Copies hidden and system files also.
/R Overwrites read-only files.
/T Creates directory structure, but does not copy files. Does not
include empty directories or subdirectories. /T /E includes
empty directories and subdirectories.
/U Copies only files that already exist in destination.
/K Copies attributes. Normal Xcopy will reset read-only attributes.
/N Copies using the generated short names.
/O Copies file ownership and ACL information.
/X Copies file audit settings (implies /O).
/Y Suppresses prompting to confirm you want to overwrite an
existing destination file.
/-Y Causes prompting to confirm you want to overwrite an
existing destination file.
/Z Copies networked files in restartable mode.
-
Try puttin quotes around it such as
/EXCLUDE:"\Thumbnails"
Another coudl be
/EXCLUDE:\Thumbnails\*.*
And try to making it the last in the string so that the /S /L is before it.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Yeah, try all your suggestions already, but same error, Can't read file: \Thumbnails\ OR Can't read file: "\Thumbnails\" OR Can't read file: \Thumbnails\*.*
I am looking at using XXCOPY from http://www.xxcopy.com to see if that can be used for my purpose.
I also came across robocopy.exe provided by Microsoft as part of server resource kit, but I don't have it installed and can't wait to buy it so will give xxcopy a try and see how it goes.
JN
-
The following command works now:
XCOPY \\WEB-JNN-IISJNN1\METROS\PUBLIC\106-COLO\JNN_ID_PHOTOS\*.JPG C:\TEMP\*.* /D:3-21-2005 /S /L /EXCLUDE:\\WEB-JNN-IISJNN1\METROS\PUBLIC\106-COLO\JNN_ID_PHOTOS\ExcludeFolder.txt'
If you read about this command in Windows Help then it specifies that the exclusions must be provided in a separate file such as text file listing one exclusion per line!! The whole time I was putting the actual exclusion values in the command while it was looking for a file to read the exclusion list from! Anyway, just thought I'd post it here for your information.
Thanks for all your help.
JN
-
There is an undocumented extended proc that does this:
exec master..xp_dirtree 'c:\', 2
The 2nd variable sets the "depth" the tree is navigated....
cl
PS: Some form of "exec master..xp_cmdshell 'tree c:\Snapshots'" may be useful, too...
Signature is NULL
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply