Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Load data from PDF file into SQL Server 2017 with R


By:   |   Last Updated: 2018-05-25   |   Comments (6)   |   Related Tips: More > SQL Server 2017

Problem

Portable Document Format (PDF) is one of the most prominent office document file formats apart from other formats like Word, Excel and PowerPoint and needs no introduction. Almost everyone who works with office documents would have worked with PDFs at least once. A very common usage pattern is that documents are generally authored in Microsoft Word, then at a certain stage they are converted into a PDF document and circulated / archived. This is done generally when these documents are no longer required to be edited frequently. Over a period of time, many organizations have accumulated a large repository of such documents.

These documents hold a large repository of data which can be very useful to different information processing applications like text mining, data archiving, data warehousing, etc. Usually content management systems hold PDF documents. When a need arises for performing some search or analytics on these documents, they often need to be processed by some data processing tools or technologies. This requires reading of such PDF documents and loading them into a database in an automated fashion.

In this tip, we will learn how to extract textual data from PDF documents and load the data into a SQL Server table without the use any external front-end or integration tools. Instead we will accomplish this just by using SQL Server 2017 and R.

Solution

R can be used to read PDF documents and extract the textual information, which can be loaded into SQL Server tables. SQL Server 2017 ships with an option to install and use the R language inside T-SQL to analyze data. We will use R with T-SQL for the above-mentioned purpose.

Before we start with the implementation, first let's briefly understand the exercise we will perform in this tip to demo the loading of data from PDF in SQL Server. We will be using a sample PDF file that would contain text as well as a graphic. This file will be stored on the same machine as the SQL Server instance. Generally, it is not advisable to have a file server and database server on the same disk subsystem. You can store the file on a different system then the database server, but ensure that the machine on which the SQL Server instance as well as R server is installed, has network connectivity and accessibility to the machine on which the file is stored. We will be reading the text from this file and loading the text into a SQL Server table. Follow the below steps to perform this exercise.

Download the sample PDF file from here. This file contains some text as well as a graphic. You can use any PDF files that you may have. This sample PDF looks as shown below.

Sample PDF - Description: Sample PDF

We need to create a table in SQL Server in which we will load the data. The table need not be too complex. We just need a field in which we will load large textual values. So preferably we will have the datatype of this field to be varchar(max). Open a new query window in SSMS, point to the database of your choice, and create a new table using the code shown below.

create table test (id int identity, PDFText varchar(max))			

It is assumed that you have installed and configured R on this instance of SQL Server. If not, you can follow the installation section of the R tutorial, and install or configure R on SQL Server.

To read the PDF files, we need to use a package named pdftools on the R installation. You can read the instructions from this link on how to install packages on a R server.

Read PDF File Using R and SQL Server

Once you have installed these packages, open a new query window and type the following code.

EXECUTE sp_execute_external_script 
@language = N'R', 
@script = N'  

# load packages
library(pdftools)

# Read PDF
text <- pdf_text("C://temp//samplecert.pdf")

# Return Output
OutputDataSet = data.frame(text)
'
with result sets (("PDF Text" varchar(max)))

Let’s try to understand this code.

  • sp_execute_external_script is the store procedure which external scripts can be executed in SQL Server.
  • @language parameter signifies the script language being used, which is R in this case.
  • @script parameter contains the actual R script which will be executed. In the script, the first part uses the library R function to load library from the package that we installed in the previous step. Then we are executing the pdf_text function of the pdftools library and passing the path of the PDF file that we wish to read. The output of this function is assigned to “text” variable. In the last step, we are converting this variable to a data frame which is the required format to emit any data output from R to SQL Server.
  • with result sets clause defines the schema in which the output result will be received by SQL Server.

You can use this approach to read PDF files in bulk by wrapping this code inside a loop. Once you execute this code, the output would look as shown below.

Read PDF in SQL Server - Description: Read PDF in SQL Server

When you expand the output field, you will find that the text in the graphic is not captured, which is expected. But the contents in text have been captured and returned successfully.

Read PDF File and Break Apart Lines Using R and SQL Server

In the above code, the entire output is in a single line, which is desirable while loading data in a table where we may want to save the entire output from a given file in a single field of a single record. But there can be cases where we may want to preserve the format too. In this case, we can use the strsplit function with a new-line character to break down the extracted text in different lines and preserve the same order as it is in the original document.

Type and execute the following code:

EXECUTE sp_execute_external_script 
@language = N'R', 
@script = N'  
 
# load packages
library(pdftools)
 
# Read PDF
text <- pdf_text("C://temp//samplecert.pdf")
 
# Split new lines
text2 <- strsplit (text, "\n")
 
# Return Output
OutputDataSet = data.frame(text2)
'
with result sets (("PDF Text" varchar(max)))
			

After you execute this code, the output should look as shown below:

Read PDF in SQL Server - Description: Read PDF in SQL Server

Read PDF File and Load to a Table Using R and SQL Server

Now that we can extract the data from PDF, itís now time to insert this data in the test table that we created earlier. This can be done with a simple insert command as shown below.

Insert into Test
EXECUTE sp_execute_external_script 
@language = N'R', 
@script = N'  
 
# load packages
library(pdftools)
 
# Read PDF
text <- pdf_text("C://temp//samplecert.pdf")
 
# Return Output
OutputDataSet = data.frame(text)
',
@output_data_1_name=N'OutputDataSet'
 
select * from test			

Executing this code would insert the extracted data from the PDF file into the table as shown below.

Read PDF in SQL Server - Description: Read PDF in SQL Server

In this way, using SQL Server 2017 and R, you can perform a bulk load of data from PDF files into SQL Server. Once this data repository is created, you can perform free text search and text mining related processing tasks on this data. The beauty of this approach is that we can load data from a PDF file to a SQL Server table with just a couple of lines of code, without the need of using any external data extraction and integration tools.

Next Steps
  • Try bulk testing your PDF files containing diverse types of textual data, wrap the code in a Stored Procedure or Function, and test it on a variety of volumes.


Last Updated: 2018-05-25


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, December 21, 2018 - 8:20:04 AM - ali Back To Top

Hello,

I have one question, How if get the image on file pdf?

thanks


Monday, June 25, 2018 - 11:32:20 AM - steve Back To Top

 To answer my own question, 1.2! :)

 


Monday, June 25, 2018 - 9:05:30 AM - steve Back To Top

Hi, what version of pdftools did you install to get this working? 1.7 or 1.8?   I am running into issue with pdftools where it wants Rcpp, but that is not working.  Thanks!


Monday, June 11, 2018 - 10:43:34 PM - Nosa Osayamwen Back To Top

 I have six columns in my pdf document, when I ran the insert code using the exact insert statement like you did, I get the following error:

Msg 213, Level 16, State 7, Line 0
Column name or number of supplied values does not match table definition.

 

 


Monday, June 04, 2018 - 3:24:57 AM - Zanoni Labuschagne Back To Top

 Very nice!! Still busy setting up my test environment to do this, but will it work where the pdf is a scanned document, which is upside-down, or a landscape document scanned sideways?

 


Sunday, May 27, 2018 - 10:02:32 AM - Jeff Moden Back To Top

Nice Tip.  Thanks for posting it.  Are there any end-of-line characters left in what's returned to the table from this?


Learn more about SQL Server tools