1

I need to insert all .pdf files from a folder, in a table, according to its number.

I could get a list of files ( I have all .pdfs inside a folder ):

exec master.sys.Xp_dirtree 'The_Folder_where_PDFS_are',0,1

And I could insert it inside a table:

CREATE TABLE PDFTest(NumExample integer,image_data VARBINARY(MAX));

DECLARE @NumExample integer
SET @NumExample = 2456789

INSERT INTO PDFTest(NumExample ,image_data)
SELECT @NumExample ,image_data
FROM OPENROWSET(BULK N'C:\DBA\3937052016_Requisição.pdf',SINGLE_BLOB) AS ImageSource(image_data);

Select * from PDFTest

But as you can see, files are named after a number ( 3937052016 ). This number is the ID of another table, so, if the PDF file starts with 123, I need to insert it into the ID 123. I just would like a start point. I'm trying to create a cursor, looping throug all files, and using left() or substring () function to get the number, inserting it into a variable, and comparing it , like insert this pdf into table where left/substring(pdf_name)=id.

I need something like a string like this :

Insert into tablex ( pdffield ) 
values ( 123_PdfFIle.pdf ( obviously using openrowset )
 where left/substring(123_pdf to get the pdf's code )) = id`

I'm on the right patch? Is there a way to do this with less efforts?

2

I created a directory called c:\TestPdf and created two txt files with some text in them (you would use your pdf files)

123456789.txt
987654321.txt

From your example, my assumption is you want file 123456789.txt inserted as ID 123 and you want file 987654321.txt inserted as ID 987.

This dynamic SQL worked for me - you would need to test against your data

set nocount on
IF OBJECT_ID('dbo.PDFTest', 'U') IS NOT NULL 
  DROP TABLE dbo.PDFTest; 
Create table PDFTest (NumExample integer,image_data VARBINARY(MAX))
DECLARE @FileTable TABLE (FileName VARCHAR(max),DepthColumn INT,FileColumn INT)

INSERT INTO @FileTable
EXEC master.sys.Xp_dirtree 'c:\testpdf',0,1

DECLARE @cmd VARCHAR(max)
DECLARE @FileName VARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT FileName
FROM @FileTable

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @FileName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = '
INSERT INTO PDFTest(NumExample ,image_data)
SELECT ' + substring(@FileName, 1, 3) + ',image_data
FROM OPENROWSET(BULK N''c:\testpdf\' + @FileName + ''',SINGLE_BLOB) AS ImageSource(image_data)
'

    PRINT @cmd

    EXEC (@cmd)

    FETCH NEXT
    FROM _CURSOR
    INTO @FileName
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR

SELECT *
FROM pdftest
  • Well I make a lot of changes and a lot of tables and inserts and joins to fit what i need but this cursor helped me 90%. Thanks. – Racer SQL Dec 13 '16 at 18:05
  • Glad to hear it help a little anyway :) – Scott Hodgin Dec 13 '16 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.