ByteScout Data Extraction Suite - VB.NET - Extract csv from pdf and fill database in sql server with pdf extractor sdk - ByteScout

ByteScout Data Extraction Suite – VB.NET – Extract csv from pdf and fill database in sql server with pdf extractor sdk

  • Home
  • /
  • Articles
  • /
  • ByteScout Data Extraction Suite – VB.NET – Extract csv from pdf and fill database in sql server with pdf extractor sdk

How to extract csv from pdf and fill database in sql server with pdf extractor sdk in VB.NET with ByteScout Data Extraction Suite

Step-by-step tutorial on how to extract csv from pdf and fill database in sql server with pdf extractor sdk in VB.NET

These sample source codes on this page below are displaying how to extract csv from pdf and fill database in sql server with pdf extractor sdk in VB.NET. ByteScout Data Extraction Suite: the bundle that includes three SDK tools for data extraction from PDF, scans, images and from spreadsheets: PDF Extractor SDK, Data Extraction SDK, Barcode Reader SDK. It can extract csv from pdf and fill database in sql server with pdf extractor sdk in VB.NET.

The SDK samples given below describe how to quickly make your application do extract csv from pdf and fill database in sql server with pdf extractor sdk in VB.NET with the help of ByteScout Data Extraction Suite. Simply copy and paste in your VB.NET project or application you and then run your app! Check VB.NET sample code samples to see if they respond to your needs and requirements for the project.

If you want to try other source code samples then the free trial version of ByteScout Data Extraction Suite is available for download from our website. Just try other source code samples for VB.NET.

On-demand (REST Web API) version:
 Web API (on-demand version)

On-premise offline SDK for Windows:
 60 Day Free Trial (on-premise)

Program.vb
      
Imports System.Data.SqlClient Imports Bytescout.PDFExtractor Module Program Sub Main() Try ' Step-1: Get Datatable Dim oDataTable = GetDataTableFromDocument("sample.pdf") ' PLEASE NOTE Please Replace With your connection String, You need to have "PersonData" table into your database. ' You can find that table from Scripts.sql file Dim connectionString As String = "Data Source=DESKTOP-92VMCQG\SQLEXPRESS;Initial Catalog=SampleDatabase;Persist Security Info=True;User ID=sa;Password=Hiren@009" ' Step-2: Insert into database InsertIntoSqlServerDatabase(oDataTable, connectionString) ' Step-3: Fetch from database and display results DisplayDatabaseResults(connectionString) Catch ex As Exception Console.WriteLine(ex.Message) End Try Console.WriteLine("Press enter key to exit...") Console.ReadLine() End Sub ''' <summary> ''' Inserts into Sql Server database ''' </summary> Private Sub InsertIntoSqlServerDatabase(ByVal oDataTable As DataTable, ByVal connectionString As String) Using con As SqlConnection = New SqlConnection(connectionString) ' Open connection con.Open() ' Sql query to insert data Dim cmdInsert As String = "Insert into PersonData (id, first_name, last_name, email, gender, ip_address) values (@id, @first_name, @last_name, @email, @gender, @ip_address)" For Each itmRow As DataRow In oDataTable.Rows ' Prepare sql command Dim cmd As SqlCommand = New SqlCommand(cmdInsert, con) cmd.CommandType = CommandType.Text cmd.Parameters.Add(New SqlParameter("@id", Convert.ToString(itmRow("id")))) cmd.Parameters.Add(New SqlParameter("@first_name", Convert.ToString(itmRow("first_name")))) cmd.Parameters.Add(New SqlParameter("@last_name", Convert.ToString(itmRow("last_name")))) cmd.Parameters.Add(New SqlParameter("@email", Convert.ToString(itmRow("email")))) cmd.Parameters.Add(New SqlParameter("@gender", Convert.ToString(itmRow("gender")))) cmd.Parameters.Add(New SqlParameter("@ip_address", Convert.ToString(itmRow("ip_address")))) ' Execute sql command cmd.ExecuteNonQuery() Next ' Close connection con.Close() End Using End Sub ' Displays inserted database results Private Sub DisplayDatabaseResults(ByVal connectionString As String) ' Person data holder Dim personDataTable As DataTable = New DataTable() Using con As SqlConnection = New SqlConnection(connectionString) ' Sql query to fetch data Dim cmdInsert As String = "SELECT id, first_name, last_name, email, gender, ip_address FROM PersonData;" ' Prepare sql command Dim cmd As SqlCommand = New SqlCommand(cmdInsert, con) cmd.CommandType = CommandType.Text ' Prepare DataAdapter Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd) ' Fill person dataTable dataAdapter.Fill(personDataTable) End Using ' Display all person data if any If personDataTable IsNot Nothing AndAlso personDataTable.Rows.Count > 0 Then ' Print all columns For Each column As DataColumn In personDataTable.Columns Console.Write("{0} | ", column.ColumnName) Next Console.WriteLine() ' Print all data For Each dataRow As DataRow In personDataTable.Rows For Each column As DataColumn In personDataTable.Columns Console.Write("{0} | ", dataRow(column.ColumnName)) Next Console.WriteLine() Next Else Console.WriteLine("No data retrieved..") End If End Sub ''' <summary> ''' Get DataTable from Document ''' </summary> Private Function GetDataTableFromDocument(ByVal fileName As String) As DataTable Dim oDataTable As DataTable = Nothing ' Initialise table detector Using tableDetector As TableDetector = New TableDetector("demo", "demo") Using CSVExtractor As CSVExtractor = New CSVExtractor("demo", "demo") ' Set table detection mode to "bordered tables" - best for tables with closed solid borders. tableDetector.ColumnDetectionMode = ColumnDetectionMode.BorderedTables ' We should define what kind of tables we should detect. ' So we set min required number of columns to 2 ... tableDetector.DetectionMinNumberOfColumns = 2 ' ... and we set min required number of rows to 2 tableDetector.DetectionMinNumberOfRows = 2 ' Load PDF document tableDetector.LoadDocumentFromFile(fileName) CSVExtractor.LoadDocumentFromFile(fileName) ' Get page count Dim pageCount As Integer = tableDetector.GetPageCount() If tableDetector.FindTable(0) Then ' Set extraction area for CSV extractor to rectangle received from the table detector CSVExtractor.SetExtractionArea(tableDetector.FoundTableLocation) ' Generate CSV data Dim allCsvData = CSVExtractor.GetCSV() ' Generate Datatable oDataTable = GetDataTableFromCSV(allCsvData) End If End Using End Using Return oDataTable End Function ''' <summary> ''' Get Datatable from CSV ''' </summary> Private Function GetDataTableFromCSV(ByVal allCsvData As String) As DataTable Dim oRetDataTable = New DataTable() oRetDataTable.Columns.Add("id") oRetDataTable.Columns.Add("first_name") oRetDataTable.Columns.Add("last_name") oRetDataTable.Columns.Add("email") oRetDataTable.Columns.Add("gender") oRetDataTable.Columns.Add("ip_address") Dim rows = allCsvData.Split(vbLf) For iRow As Integer = 1 To rows.Length - 1 ' Get all column data Dim columns = rows(iRow).Split(","c) If columns.Length >= 5 Then ' Prepare new row Dim oRow = oRetDataTable.NewRow() oRow("id") = columns(0) oRow("first_name") = columns(1) oRow("last_name") = columns(2) oRow("email") = columns(3) oRow("gender") = columns(4) oRow("ip_address") = columns(5) ' Add row back to datatable oRetDataTable.Rows.Add(oRow) End If Next ' Return DataTable Return oRetDataTable End Function End Module

ON-PREMISE OFFLINE SDK

60 Day Free Trial or Visit ByteScout Data Extraction Suite Home Page

Explore ByteScout Data Extraction Suite Documentation

Explore Samples

Sign Up for ByteScout Data Extraction Suite Online Training

ON-DEMAND REST WEB API

Get Your API Key

Explore Web API Docs

Explore Web API Samples

VIDEO

ON-PREMISE OFFLINE SDK

60 Day Free Trial or Visit ByteScout Data Extraction Suite Home Page

Explore ByteScout Data Extraction Suite Documentation

Explore Samples

Sign Up for ByteScout Data Extraction Suite Online Training

ON-DEMAND REST WEB API

Get Your API Key

Explore Web API Docs

Explore Web API Samples

Tutorials:

prev
next