Reading a text file with SQL Server

Reading Text Files with SQL Server: Methods and Best Practices

Explore various techniques for importing data from text files into SQL Server, from basic BULK INSERT to advanced OPENROWSET and xp_cmdshell methods, including considerations for security and performance.

Importing data from text files into SQL Server is a common task for database administrators and developers. Whether you're dealing with CSV files, tab-delimited data, or custom flat files, SQL Server offers several robust methods to achieve this. This article will guide you through the most popular techniques, discussing their advantages, disadvantages, and practical implementation details. We'll cover BULK INSERT, OPENROWSET, and the xp_cmdshell approach, providing code examples and best practices for each.

Method 1: BULK INSERT for Efficient Data Loading

BULK INSERT is a Transact-SQL statement designed for high-performance data loading from a data file into a table or view in SQL Server. It's often the preferred method for its speed and simplicity when dealing with well-structured text files. This command allows you to specify various options for parsing the file, such as field and row terminators, error handling, and data format.

BULK INSERT YourTableName
FROM 'C:\Path\To\YourFile.txt'
WITH
(
    FIELDTERMINATOR = ',',  -- Or '\t' for tab-delimited
    ROWTERMINATOR = '\n',   -- Or '\r\n' for Windows line endings
    FIRSTROW = 2,           -- Skip header row
    TABLOCK                 -- For better performance with large imports
);

Basic BULK INSERT statement for a comma-separated file.

Method 2: OPENROWSET for Ad-Hoc Queries and Flexible Formats

OPENROWSET provides a flexible way to access remote data from OLE DB data sources, including text files, as if they were linked servers. This method is particularly useful for ad-hoc queries or when you need more control over the data format using a format file. It requires the 'Ad Hoc Distributed Queries' option to be enabled on your SQL Server instance.

flowchart TD
    A[Start] --> B{"Enable 'Ad Hoc Distributed Queries'"}
    B -- Yes --> C[Create Format File (Optional)]
    C --> D[Execute OPENROWSET Query]
    D --> E[Process Data]
    E --> F[End]
    B -- No --> G[Error: Feature Disabled]
    G --> F

Workflow for using OPENROWSET to read text files.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

SELECT a.* FROM OPENROWSET(
    BULK 'C:\Path\To\YourFile.csv',
    FORMATFILE = 'C:\Path\To\YourFormatFile.fmt'
) AS a;

Enabling Ad Hoc Distributed Queries and using OPENROWSET with a format file.

Method 3: xp_cmdshell for Advanced File Operations

The xp_cmdshell extended stored procedure allows SQL Server to execute operating system commands directly. While powerful, it's generally considered a last resort due to significant security implications. It can be used to read text files by piping the output of command-line tools (like TYPE or MORE) into a temporary table. This method offers extreme flexibility for complex file parsing or when interacting with external scripts.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

CREATE TABLE #TempFileData (LineContent NVARCHAR(MAX));

INSERT INTO #TempFileData
EXEC xp_cmdshell 'TYPE "C:\Path\To\YourFile.txt"';

SELECT * FROM #TempFileData WHERE LineContent IS NOT NULL;

DROP TABLE #TempFileData;

GO

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

Using xp_cmdshell to read a text file into a temporary table.