Reading a Text File With SQL Server

Here we will see, how to read a text file with SQL Server. We can read the text file using the OPENROWSET(BULK ) function.

OPENROWSET(BULK)

It is a table-valued function that can read data from any file. This function returns a single column table having all the contents of the file. This single large value is known as a   SINGLE_CLOB (single character large object)

Syntax:

SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name;

This query will read the content of the text file and return it as a single column in a table named Bulkcolumn. The correlation name is mandatory to specify. We have the text file named “Geek.txt”. Content of text file:

Hii!! My name is Romy kumari.
I am an engineer.

Welcome to GeeksforGeeks platform.

Read text file from SQL server

Query:

SELECT * FROM OPENROWSET(BULK 'E:\Geek.txt', SINGLE_CLOB) AS Contents;

Output:

The entire content is returned as a single column.

Read text file from SQL server such that one row represents data in one line from a text file

Step to read each line of the text file in a single row:

• Create a table in your database.
• Insert data from a text file into the table using the ‘INSERT’ keyword.
• Using WITH clause set ROWTERMINATOR as ‘\n’ (represents newline character). This split the content of the file into separate rows as soon as the new line is encountered in the file.

Step 1: Create Database

Use the below SQL statement to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Using the Database

Use the below SQL statement to switch the database context to geeks.

Query:

USE geeks;

Step 3: Table definition

We have the following demo table in our geek’s database.

CREATE TABLE demo(
Content VARCHAR(1000)
);

Step 4: Insert data from a text file into the table

Use the following command to insert data from a text file.

Syntax:

BULK INSERT dbo.table_name
  FROM 'file_path'
  WITH  
     (
        ROWTERMINATOR ='\n'
     )

Query:

BULK INSERT dbo.demo
  FROM 'E:\Geek.txt'
  WITH  
     (
        ROWTERMINATOR ='\n'
     );

Step 5: See the content of the table

Query:

SELECT * FROM demo;

Output:

Here, NULL represents an empty line.