December 8, 2014 at 5:03 pm
Hello Everyone.
I need to load images from folders into a SQL Server table and I have done it successfully for individual images, however I need to load all the names of the folders and sub folders names in separate columns + load all images.
So the folders look like as in the screenshot and the final result of the table in SQL Server should look like the second screenshot.
Does anyone have a TSQL code that could it? If so could anyone help with this information?
Thanks in advance for the time.
December 8, 2014 at 6:47 pm
Can we see that separate code that works for one image, please?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2014 at 7:30 pm
Hello Jeff, thanks for the reply and help.
This is the code below, I would need to change this code or come up with a new one that would allow me to load into the table the folder and subfolder name + all images.
CREATE TABLE LoadedImage
(
Id int,
FolderName----All folder names should be added to the table
SubFolder-----All subfolder names should be added to the table as well
Name varchar(50),
Photo varbinary(max)
)
INSERT INTO LoadedImage(Id, Name, Photo) ---Need to add the Folder and subfolder columns here as well
SELECT 1, 'Car', BulkColumn
FROM Openrowset( Bulk 'C:\car.jpg', Single_Blob) as image --- it brings only one image, I would need to load all images
December 8, 2014 at 7:36 pm
This would be super simple to do if you were allowed to use xp_CmdShell. Can we do that or do we need to avoid it?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2014 at 7:50 pm
Yeap, why not. As long as I will be able to load into the table the folder and sub folder names as well images that would be great.
Could y provide the code?
Once again thanks for the help!!
December 8, 2014 at 7:59 pm
Yes. I'm curious though... why isn't the full path a file name contained in the table? Without esoteric controls, you wouldn't be able to work your way back to the source document (image) without knowing the drive and path or , perhaps, the UNC.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2014 at 8:10 pm
So the reason why it should be that way it is because I will join this table that will contain the folders and subfolders names, images with another table.
So it is important to capture the folder and subfolder name only(Not the path). As you could see the folders are named with numbers as such 90050 etc.. so this column will be used to join another table.
Robert
December 8, 2014 at 9:53 pm
Got it. Working on it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2014 at 9:55 pm
Thanks man!!
December 8, 2014 at 11:38 pm
Ok, here we go.
First, we need a couple of functions to make our lives easier. This first one is a modification of the DelimitedSplit8K function that I've been working on. It's a string splitter.
/****** Object: UserDefinedFunction [dbo].[DelimitedSplit8K] Script Date: 12/09/2014 01:27:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
/**********************************************************************************************************************
Purpose:
Given a string containing multiple elements separated by a single character delimiter and that single character
delimiter, this function will split the string and return a table of the single elements (Item) and the element
position within the string (ItemNumber).
Notes:
1. Performance of this function approaches that of a CLR.
2. Note that this code implicitly converts NVARCHAR to VARCHAR and that conversion may NOT be faithful.
Revision History:
Note that this code is a modification of a well proven function created as a community effort and initially documented
at the following URL (http://www.sqlservercentral.com/articles/Tally+Table/72993/). This code is still undergoing
tests. Although every care has certainly been taken to ensure its accuracy, you are reminded to do your own tests to
ensure that this function is suitable for whatever application you might use it for.
--Jeff Moden, 01 Sep 2013
**********************************************************************************************************************/
--===== Define I/O parameters
(@pString VARCHAR(8000) , @pDelimiter CHAR(1)) --DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000).
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d), --10E+4 or 10,000 rows max
cteTally(N) AS ( --=== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS ( --=== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT CASE WHEN SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN THEN t.N+1 END --added short circuit for casting
FROM cteTally t
WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN
),
cteLen(N1,L1)AS ( --=== Return start and length (for use in substring).
-- The ISNULL/NULLIF combo handles the length for the final of only element.
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter ,@pString COLLATE Latin1_General_BIN,s.N1) ,0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
Up next is a function to help prevent DOS/SQL Injection because we'll need to concatenate some things that we get from the outside world as a parameter...
/****** Object: UserDefinedFunction [dbo].[CheckForDosInjection] Script Date: 12/09/2014 01:31:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CheckForDosInjection]
/**********************************************************************************************************************
Purpose:
Checks a string for unauthorized characters which could be used for DOS injection. Returns 'Clean' if no unauthorized
characters exist and 'Dirty' if any unauthorized characters or the word "REM" followed by a space exists or a double
colon exists (possible replacement for "REM").
Usage:
SELECT Status
FROM dbo.CheckForDosInjection(@pString)
;
Revision History:
Rev 00 - 03 Jul 2013 - Jeff Moden - Initial Creation and Unit Test.
**********************************************************************************************************************/
--===== Declare the I/O for the function
(
@pString VARCHAR(8000)
)
RETURNS TABLE AS
RETURN
SELECT Status = CASE --When no unauthorized characters are present... (note the "double-negative" is essential)
WHEN @pString NOT LIKE '%[^a-zA-Z0-9:\*. __]"%' ESCAPE '_'
AND CHARINDEX('REM ',@pString) = 0
AND CHARINDEX('::',@pString) = 0
THEN 'CLEAN'
ELSE 'DIRTY' --otherwise,
END
;
And, finally, a stored procedure that does what you want. Details are in the header including example usage. This loaded 3,747 photos with an average size of 154,101 bytes each in 4 minutes and 32 seconds (total of 577,419,251 bytes in the photos). A little slow for my liking but, it was free and didn't go back to tweak it. 😉 I was also reading from a USB memory stick rather than a hard drive.
CREATE PROCEDURE dbo.LoadFiles
/*****************************************************************************************
Purpose:
Given a valid file path that SQL Server can "see" and an extension (can be a wildcard),
load all of the files as images.
------------------------------------------------------------------------------------------
Programmer notes:
1. If either parameter contains signs of DOS Injection, the proc will return with no
error or any indication of what might be wrong so as to not give an attacker any
hints.
------------------------------------------------------------------------------------------
Example Usage:
--===== Simple syntax
EXEC dbo.LoadFiles @pFullPath, @pExtension
;
--===== Example to load all JPG files from C:\Temp and all of its subdirectories.
EXEC dbo.LoadFiles 'C:\Temp', 'jpg'
;
------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 08 Dec 2014 - Jeff Moden
- Initial creation and unit test
*****************************************************************************************/
--===== Declare the paramters for this proc
@pFullPath VARCHAR(2000)
,@pExtension VARCHAR(500)
AS
--========================================================================================
-- Presets
--========================================================================================
--===== Setup the environment
SET NOCOUNT ON;
--===== Local Variables
DECLARE @Cmd VARCHAR(2000)
,@BulkCmd VARCHAR(MAX)
;
--===== Create the temp table to hold all the full file path names
CREATE TABLE #FileName
(
FileNameID INT IDENTITY(1,1)
,FUllPathName VARCHAR(2000)
,Folder VARCHAR(128)
,SubFolder VARCHAR(128)
,[FileName] VARCHAR(128)
)
;
--========================================================================================
-- Get the desired file names from the given directory on down
--========================================================================================
--===== Create the DOS command to get ALL of the files with the correct extensions
-- in all directories from the given @pFullPath on down.
SELECT @Cmd = REPLACE(REPLACE(
'DIR "<<@pFullPath>>\*.<<@pExtension>>" /s /b'
,'<<@pFullPath>>',@pFullPath)
,'<<@pExtension>>',@pExtension)
;
--===== Delouse the command to help prevent DOS injection
IF 'Dirty' = (SELECT Status FROM dbo.CheckForDosInjection(@Cmd))
RETURN -- We're intentionally exiting early with no error to not give an attacker hints
;
--===== Get the full file path names
INSERT INTO #FileName (FUllPathName)
EXEC xp_CmdShell @Cmd
;
--===== Split out the "Folder", "SubFolder", and "FileName" parts and remember them
WITH
cteSplit AS
( --=== This splits the full file path name into parts based on backslashes
-- and numbers them in descending order.
SELECT FileNameID
,RowNum = ROW_NUMBER() OVER (PARTITION BY FileNameID ORDER BY ItemNumber DESC)
,Item
FROM #FileName
CROSS APPLY dbo.DelimitedSplit8K(FUllPathName,'\')
WHERE FUllPathName > '' --NOT NULL AND NOT BLANK
)
,
ctePivot AS
( --=== This takes the last 3 parts of the full file path name and assigns
-- them to the desired columns
SELECT FileNameID
,Folder = MAX(CASE WHEN RowNum = 3 THEN cte.Item ELSE '' END)
,SubFolder = MAX(CASE WHEN RowNum = 2 THEN cte.Item ELSE '' END)
,[FileName] = MAX(CASE WHEN RowNum = 1 THEN cte.Item ELSE '' END)
FROM cteSplit cte
GROUP BY cte.FileNameID
) --=== Then, we update the file name table with that info.
UPDATE tgt
SET tgt.Folder = pvt.Folder
,tgt.SubFolder = pvt.SubFolder
,tgt.[FileName] = pvt.[FileName]
FROM ctePivot pvt
JOIN #FileName tgt
ON tgt.FileNameID = pvt.FileNameID
;
--========================================================================================
-- Load all the files that we've gotten names for
--========================================================================================
--===== Create the BULK command
SELECT @BulkCmd = ''
SELECT @BulkCmd = @BulkCmd + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
INSERT INTO dbo.LoadedImage
(Folder, SubFolder, Name, Photo)
SELECT <<Folder>>,<<SubFolder>>,<<Name>>,blob.BulkColumn
FROM OPENROWSET(BULK "<<FUllPathName>>", SINGLE_BLOB) blob
;'
,'"' ,'''')
,'<<FUllPathName>>',FUllPathName)
,'<<Folder>>',QUOTENAME(Folder,''''))
,'<<SubFolder>>',QUOTENAME(SubFolder,''''))
,'<<Name>>',QUOTENAME([FileName],''''))
FROM #FileName
WHERE FUllPathName IS NOT NULL
;
--===== Load all the files along with all the amplifying information
EXEC (@BulkCmd)
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2014 at 10:35 pm
Did this work out for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2014 at 10:38 am
Hello Jeff,
Sorry for the late reply, too much snow up here in Canada and network issues.
So I will test it now, but before I got a question for you. On this part of the query should I change anything or leave the way it is
SELECT <<Folder>>,<<SubFolder>>,<<Name>>,blob.BulkColumn
FROM OPENROWSET(BULK "<<FUllPathName>>", SINGLE_BLOB) blob
;'
,'"' ,'''')
,'<<FUllPathName>>',FUllPathName)
,'<<Folder>>',QUOTENAME(Folder,''''))
,'<<SubFolder>>',QUOTENAME(SubFolder,''''))
,'<<Name>>',QUOTENAME([FileName],''''))
For example: Should I add the info in the query or just leave like you left in the example above, what would I need to add here?
SELECT 'Folder,','Subfolder',blob.BulkColumn
FROM OPENROWSET(BULK "c:\Folder\Subfolder", SINGLE_BLOB) blob
;'
,'"' ,'''')
,'C:\Folder\Subfolder',FUllPathName)
,'Folder',QUOTENAME(Folder,''''))
,'Subfolder',QUOTENAME(SubFolder,''''))
,'Tes',QUOTENAME([FileName],''''))
Thanks and apology for the late reply
December 11, 2014 at 11:39 am
robertdba (12/11/2014)
Hello Jeff,Sorry for the late reply, too much snow up here in Canada and network issues.
So I will test it now, but before I got a question for you. On this part of the query should I change anything or leave the way it is
SELECT <<Folder>>,<<SubFolder>>,<<Name>>,blob.BulkColumn
FROM OPENROWSET(BULK "<<FUllPathName>>", SINGLE_BLOB) blob
;'
,'"' ,'''')
,'<<FUllPathName>>',FUllPathName)
,'<<Folder>>',QUOTENAME(Folder,''''))
,'<<SubFolder>>',QUOTENAME(SubFolder,''''))
,'<<Name>>',QUOTENAME([FileName],''''))
For example: Should I add the info in the query or just leave like you left in the example above, what would I need to add here?
SELECT 'Folder,','Subfolder',blob.BulkColumn
FROM OPENROWSET(BULK "c:\Folder\Subfolder", SINGLE_BLOB) blob
;'
,'"' ,'''')
,'C:\Folder\Subfolder',FUllPathName)
,'Folder',QUOTENAME(Folder,''''))
,'Subfolder',QUOTENAME(SubFolder,''''))
,'Tes',QUOTENAME([FileName],''''))
Thanks and apology for the late reply
The REPLACES in the original dynamic SQL should replace the "<<somethinghere>>" tokens with the correct information.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2014 at 8:55 am
Hello Jeff,
I have insert the info in this part of the query, but something is not working. Is there any wrong that inserted in the query? also <<Name>> what should insert there, can provide an example?
Thanks a lot
SELECT @Cmd = REPLACE(REPLACE(
'DIR "C:\Car\CarSubfolder\*.jpg" /s /b'
,'C:\Car\CarSubfolder\',@pFullPath)
,'jpg',@pExtension)
;
INSERT INTO dbo.LoadedImage
(Folder, SubFolder, Name, Photo)
SELECT Car,CarSubfolder,<<Name>>,blob.BulkColumn
FROM OPENROWSET(BULK "C:\MyFolder\MySubfolder\", SINGLE_BLOB) blob
;'
,'"' ,'''')
,'C:\Car\CarSubfolder\',FUllPathName)
,'Car',QUOTENAME(Folder,''''))
,'CarSubfolder',QUOTENAME(SubFolder,''''))
,'<<Name>>',QUOTENAME([FileName],''''))
December 12, 2014 at 8:57 am
Sorry that is the write one.
SELECT @Cmd = REPLACE(REPLACE(
'DIR "C:\Car\CarSubfolder\*.jpg" /s /b'
,'C:\Car\CarSubfolder\',@pFullPath)
,'jpg',@pExtension)
;
INSERT INTO dbo.LoadedImage
(Folder, SubFolder, Name, Photo)
SELECT Car,CarSubfolder,<<Name>>,blob.BulkColumn
FROM OPENROWSET(BULK "C:\Car\CarSubfolder\", SINGLE_BLOB) blob
;'
,'"' ,'''')
,'C:\Car\CarSubfolder\',FUllPathName)
,'Car',QUOTENAME(Folder,''''))
,'CarSubfolder',QUOTENAME(SubFolder,''''))
,'<<Name>>',QUOTENAME([FileName],''''))
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply