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.
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)
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
SELECT * FROM OPENROWSET(BULK 'E:\Geek.txt', SINGLE_CLOB) AS Contents;
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.
CREATE DATABASE geeks;
Step 2: Using the Database
Use the below SQL statement to switch the database context to 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.
BULK INSERT dbo.table_name FROM 'file_path' WITH ( ROWTERMINATOR ='\n' )
BULK INSERT dbo.demo FROM 'E:\Geek.txt' WITH ( ROWTERMINATOR ='\n' );
Step 5: See the content of the table
SELECT * FROM demo;
Here, NULL represents an empty line.