Microsoft Business Intelligence: Reading the files (PDF, word, excel text etc.) From folder and loading it into the table using sql script

Wednesday 30 June 2021

Reading the files (PDF, word, excel text etc.) From folder and loading it into the table using sql script

We have a requirement that we need to read the files (PDF, word, excel text, etc.) from the folder and insert them into the database.

Below is the table

CREATE TABLE dbo.file_details
  (
     fileid    INT IDENTITY(1, 1) NOT NULL,
     file_nm   VARCHAR(250) NULL,
     file_data VARBINARY(max) NULL
  )

Below files are need to load in this table.

Let’s see how to load these files into the table.

Below is the sql script to load the files.

With the help of the below script we need to configure the below setting on the server.

USE MASTER

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'Ole Automation Procedures', 1;

RECONFIGURE;

-- Add 'bulkadmin' to the correct user

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]

Now with the help of below script we will load the files from the folder.

DECLARE @file_nm     VARCHAR(250),
        @Sql_Query   NVARCHAR(4000),
        @Source_Path VARCHAR(500) ='G:\Books\SSIS',
        @file_path   VARCHAR(500),
        @cmdstr      NVARCHAR(400),
        @n           INT=1,
        @cnt         INT;
DECLARE @files TABLE
  (
     id      INT IDENTITY,
     file_nm VARCHAR(500)
  )

SET @cmdstr = 'dir ' + @Source_Path + ' /b';

INSERT INTO @files
EXECUTE Xp_cmdshell
  @cmdstr

SET @cnt=(SELECT Count(*)
          FROM   @files
          WHERE  file_nm IS NOT NULL)

SELECT @cnt

WHILE @n <= @cnt
  BEGIN
      SET @file_nm =(SELECT file_nm
                     FROM   @files
                     WHERE  id = @n)
      SET @file_path= @Source_Path + '\' + @file_nm;
      SET @Sql_Query = 'INSERT INTO File_details(File_Nm,File_data) '
                       + 'SELECT ' + '''' + @file_nm + '''' + ', * '
                       + '  FROM Openrowset( Bulk ' + '''' + @file_path
                       + '''' + ', Single_Blob) as book'

      EXEC (@Sql_Query)

      SET @n=@n + 1;
  END