July 23, 2011 at 8:42 am
Hello All
I have a situation to load tons of documents(all types) into varbinary(max) filed. I am using using following code in Cursor to load thousands of documents. It works perfect until it hits with filename with characters like & ' etc. I have no option to change file names since they are in tens of thousands. I must force SQL to accept those names. How can I achieve this. I tried to load through SSIS, you have to use expression in that. It also faces same issue.
--Sample File Names (Put some files into C:\Docs\ with following names)
-- guez_sica_I9&OE04-29-2011ybj1006_1.tif
-- erson_red_OE'S&I905-04-20110181_1.tif
-- arte_Marc_PO'S&I905-05-20110251_1.pdf
---Table
CREATE TABLE [dbo].[Documents](
[FileId] [int] IDENTITY(1,1) NOT NULL,
[OrignalFileName] [varchar](350) NULL,
[FileName] [varchar](350) NULL,
[CreationDate] [date] NULL,
[file_blob] [varbinary](max) NULL,
) ON [PRIMARY]
GO
--SQL which I am using
DECLARE @PathName VARCHAR(256),
@CMD VARCHAR(512),
@SQLstr VARCHAR(1000),
@err int
CREATE TABLE #CmdShell ( Title VARCHAR(512) )
SET @PathName = 'C:\DOCS\\*.*'
SET @CMD = 'DIR ' + @PathName + ' /TC'
INSERT INTO #CmdShell
EXEC MASTER..xp_cmdshell @CMD
DELETE FROM #CmdShell
WHERE Title NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %' OR Title LIKE '%<DIR>%' OR Title IS NULL
DECLARE CUR_DOC CURSOR FAST_FORWARD
FOR SELECT Title AS OrignalFileName,
REVERSE(LEFT(REVERSE(Title),CHARINDEX(' ', REVERSE(Title)) - 1)) AS FileName,
LEFT(Title, 10) AS CreationDate
FROM #CmdShell
OPEN CUR_DOC
DECLARE @OrignalFileName VARCHAR(200),@FileName VARCHAR(200),@CreationDate DATETIME
FETCH NEXT FROM CUR_DOC INTO @OrignalFileName, @FileName, @CreationDate
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT @SQLstr = ''
SELECT @SQLstr = N'INSERT INTO dbo.Documents(OrignalFileName,FileName,CreationDate,file_blob)
SELECT ''' + @OrignalFileName + ''',
''' + @FileName + ''',
'''
+ CAST(@CreationDate AS VARCHAR(50))
+ ''',
*
FROM OPENROWSET(BULK ''D:\DOCS2011\'+ @FileName + ''', SINGLE_BLOB) AS imagesource'
PRINT @SQLstr
EXEC ( @SQLstr)
SELECT @SQLstr = ''
SELECT @SQLstr = 'move '+'C:\DOCS\'+@FileName+' C:\DOCSLOADED\'
EXEC @err = master..xp_cmdshell @SQLstr
FETCH NEXT FROM CUR_DOC INTO @OrignalFileName, @FileName,@CreationDate
END
CLOSE CUR_DOC
DEALLOCATE CUR_DOC
GO
This is the way insertion statement looks with error
INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)
SELECT '07/14/2011 11:57 AM 99,840 sley_Lisa_sley'sCustomerService&Admin_1.doc',
'sley_Lisa_sley'sCustomerService&Admin_1.doc',
'Jul 14 2011 12:00AM',
*
FROM OPENROWSET(BULK 'C:\DOCS\sley_Lisa_sley'sCustomerService&Admin_1.doc', SINGLE_BLOB) AS imagesource
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '&'.
July 23, 2011 at 12:38 pm
Encapsulating the entire path (drive, path, filename) in double quotes should do the trick.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2011 at 1:29 pm
Jeff
I did as you said as following, see error in second box
FROM OPENROWSET(BULK "''D:\DOCS2011\'+ @FileName + ''' ", SINGLE_BLOB) AS imagesource'
Just I used PRINT statement to get what is going , you can see error.
INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)
SELECT '07/14/2011 11:20 AM 31,232 ton_ula_ula'sMarch19,2011_2.doc',
'Hilton_Paula_paula'sresumeMarch19,2011clerical_2.doc',
'Jul 14 2011 12:00AM',
*
FROM OPENROWSET(BULK "'D:\DOCS2011\ton_ula_ula'sMarch19,2011_2.doc' ", SINGLE_BLOB) AS imagesource
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
July 23, 2011 at 1:52 pm
{sorry, duplicate post removed}
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2011 at 1:56 pm
Not quite what I had in mind. You put the double quotes on the wrong side of the single quotes. I believe you want to end up with something like the following...
FROM OPENROWSET(BULK '"D:\DOCS2011\ton_ula_ula''sMarch19,2011_2.doc"', SINGLE_BLOB) AS imagesource
Also notice how I doubled up on the embedded single quote. Not sure that's the fix but that's what I'd try.
As a side bar, I'd locate my favorite my favorite high-velocity, calicium knob impression tool (baseball bat :-P) and find the, ummmm.... moroff that created file names like that and explain the facts of life to him/her. :hehe: Those are just terrible filenames.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2011 at 2:09 pm
Jeff,
those are not real file names, they are edited for privacy. Just they are not real. But real file names also consists these kind of characters. They are done by many people on the field, we can't control how they name it.
July 23, 2011 at 2:21 pm
I observed you inserted another apostrophe near '. You can do that if you know the file name. Bu tin my case they are dynamic,Files names are supplied by variable. How do I insert another apostrophe [ ' ] if there is any apostrophe ' in file name.
Thank you for your help
July 23, 2011 at 2:24 pm
Yep... I know. My apologies... Since you're doing relatively advanced work, I made the bad assumption you might know about the "REPLACE" function. Here's how to do it.
REPLACE(@FileName, '''', '''''')
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2011 at 2:26 pm
Sridoc2020 (7/23/2011)
Jeff,those are not real file names, they are edited for privacy. Just they are not real. But real file names also consists these kind of characters. They are done by many people on the field, we can't control how they name it.
Sure you can... tell them it's part of their job and that they'll be fired on the second offense. 😉
Just kidding. I agree that you have to take about anything there might be for a file name when humans create them.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2011 at 2:51 pm
Sorry no luck yet, see below
INSERT INTO dbo.MyFiles(OrignalFileName,FileName,CreationDate,file_blob)
SELECT '07/14/2011 11:20 AM 31,232 ton_ula_ula''sMarch19,2011_2.doc',
'ton_ula_ula''sMarch19,2011_2.doc',
'Jul 14 2011 12:00AM',
*
FROM OPENROWSET(BULK '"D:\DOCS2011\ton_ula_ula''sMarch19,2011_2.doc"' , SINGLE_BLOB) AS imagesource
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
This is how my code looks now
SELECT @SQLstr = ''
SELECT @SQLstr = N'INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)
SELECT ''' + REPLACE(@OrignalFileName, '''', '''''') + ''',
''' + REPLACE(@FileName, '''', '''''') + ''',
'''
+ CAST(@CreationDate AS VARCHAR(50))
+ ''',
*
FROM OPENROWSET(BULK ''"D:\DOCS2011\'+ REPLACE(@FileName, '''', '''''') + '"'' , SINGLE_BLOB) AS imagesource'
PRINT @SQLstr
EXEC ( @SQLstr)
July 23, 2011 at 3:15 pm
I just tried both of the following on my machine and they both work just fine. You don't need the double quotes after all. You do, however, need to double up all apostrophes as we've done...
SELECT '07/14/2011 11:20 AM 31,232 The''Shadow.jpg',
'The''Shadow.jpg',
'Jul 14 2011 12:00AM',
*
FROM OPENROWSET(BULK 'C:\Documents and Settings\Admin\My Documents\My Pictures\The''Shadow.jpg' , SINGLE_BLOB) AS imagesource
SELECT '07/14/2011 11:20 AM 31,232 The & Shadow.jpg',
'The & Shadow.jpg',
'Jul 14 2011 12:00AM',
*
FROM OPENROWSET(BULK 'C:\Documents and Settings\Admin\My Documents\My Pictures\The & Shadow.jpg' , SINGLE_BLOB) AS imagesource
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2011 at 4:41 pm
Hi, sorry to butt in like this, but can I make a couple of suggestions?
First, try to keep your dynamic code formatted nicely - it's so much easier to maintain that way.
Second, try using QUOTENAME instead of REPLACE - mostly to help with my first point...
Here is how your code looks this way:
SELECT @SQLstr = N'INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)
SELECT
' + QUOTENAME(@OrignalFileName, '''') + ',
' + QUOTENAME(@FileName, '''') + ',
' + QUOTENAME(CAST(@CreationDate AS VARCHAR(50)),'''') + ',
*
FROM OPENROWSET(BULK '+quotename('D:\DOCS2011\'+ @FileName, '''')+' , SINGLE_BLOB) AS imagesource'
When you compare that to the code you posted, I hope you will agree it is much easier to read, understand and maintain.
SELECT @SQLstr = N'INSERT INTO dbo.MyFiles (OrignalFileName,FileName,CreationDate,file_blob)
SELECT ''' + REPLACE(@OrignalFileName, '''', '''''') + ''',
''' + REPLACE(@FileName, '''', '''''') + ''',
'''
+ CAST(@CreationDate AS VARCHAR(50))
+ ''',
*
FROM OPENROWSET(BULK ''"D:\DOCS2011\'+ REPLACE(@FileName, '''', '''''') + '"'' , SINGLE_BLOB) AS imagesource'
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 23, 2011 at 5:15 pm
I will agree 100% with you.
July 24, 2011 at 10:09 am
So... enquiring minds want to know... did the latest code fix things for you or not? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2011 at 4:37 pm
No I didn't help yet. I wanted load first whatever I can. Thought I could revisit those files. Now I have another character comma(,) also in the file names.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply