SQL Server - How to import text files into database (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)

Views: 4.572
Reading Time: 7 minutes

Hello people,
Good Morning!

In this post I would like to show you different ways to import content into a text file for a variable in SQL Server, so you can use this information in a way that meets your needs.

I've posted some ways to do this here on the blog, but on different topics for different purposes, so I'd like to repost this solution, as well as include some more that I haven't mentioned here and thus centralize all of these approaches.

OLE Automation

How to import text files into bank with OLE Automation

For those unfamiliar with this feature, it allows the DBA or Developer to perform a series of actions on the database using OLE DB, such as read / write / move / copy / delete files, Excel spreadsheet creation and more things. The syntax is somewhat similar to VBA and uses the Windows API for these operations.

For this purpose, we will use the functions fncLer_Archive_FSO and fncLer_Archive_String_FSO, as shown below:

FncLer_FileFile function source code

FncLer_String_FSO Function Source Code

Example of use:
SQL Server - How to import text files with OLE Automation

Would you like to know a little more about OLE Automation?
Enabling OLE Automation via T-SQL on SQL Server
File Operations Using OLE Automation in SQL Server
Consuming the Google Maps API using OLE Automation in SQL Server
How to calculate shipping amount and lead time using Post Office WebService in SQL Server
Querying SQL Server object tracking
Consuming the Google Maps API to get address or zip code information in SQL Server
How to query information from a zip code in SQL Server

Common Language Runtime (CLR)

How to import text files into bank with CLR

The CLR allows you to be able to create routines (stored prodecures, functions, triggers, etc.) written in C #, F # and VB.NET, compile and execute them in the database natively, extending DBMS capabilities, as it is possible. create a multitude of things that would not be possible using just Transact-SQL, such as file manipulation, FTP file upload and download, aggregate functions, Webservices integration, and more.

For this purpose, we will use the functions fncFile_Ler and fncFile_Ler_Return_String, as shown below:

Source code for the fncFile_Ler function

Fnc function source codeFile_Ler_Return_String

Example of use:
SQL Server - How to import text files with CLR (C #)

Would you like to know more about CLR?
Introduction to SQL Common Language Runtime (CLR) in SQL Server
SQL Server - How to list, read, write, copy, delete and move files with CLR (C #)
Performing POST and GET requests using CLR (C #) in SQL Server

BCP (Bulk Copy)

How to import text files into bank with BCP

This is one of the options most commonly used by DBAs, as it is simple, it is already installed with SQL Server and can be executed in both SSIS packages and stored procedures (using xp_cmdshell)

Remember that to use BCP, you will need to enable the xp_cmdshell feature. I particularly don't like to use xp_cmdshell or leave it enabled in one instance as it allows for numerous vulnerabilities and any Windows Prompt command can be run with this feature enabled.

To enable the feature and enable xp_cmdshell, run the following commands:

Example of use:
SQL Server - How to import text files with BCP

Where:
BCP Help

BULK INSERT

How to import text files into bank with BULK INSERT

A solution that is not as well known as the other but equally useful and powerful, BULK INSERT is a native SQL Server command that lets you import text and CSV files without needing any external features or enabling anything on your instance. This is the simplest way to start importing files into your database.

Usage examples:
SQL Server - How to import text files with BULK INSERT

Note: As you may notice, blank lines are imported as NULL by BULK INSERT.

OPENROWSET (BULK)

How to import text files into bank with OPENROWSET (BULK)

Another very fast native database solution is the use of OPENROWSET together with the BULK option to upload text files to the database. It is widely used for importing XML files and I will show you an easy way to import text files:

Usage examples:
SQL Server - Import text txt file openrowset bulk

Where:

  • SINGLE_BLOB Returns File Contents as varbinary (max) - Recommend for XML Files
  • SINGLE_CLOB returns file contents as varchar (max)
  • SINGLE_NCLOB returns file contents as nvarchar (max)

For text files, I usually use SINGLE_CLOB, but when the file encoding is UNICODE, you must use SINGLE_NCLOB, otherwise you will encounter this error message:

SINGLE_CLOB requires a double-byte character set (DBCS) (char) input file. The file specified is Unicode.

OPENROWSET (OLEDB)

How to import text files into database with OPENROWSET (OLEDB)

Another alternative to importing text files is to use OPENROWSET again, but now using the Microsoft ACE OLEDB provider. This solution is for importing both text files, returning each file line as a table record, and importing CSV files.

Note that the provider “Microsoft.ACE.OLEDB.12.0” is not installed by default on SQL Server. It needs to be installed manually. To learn more about this, see my post. SQL Server - How to Install the Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0 Drivers.

Usage examples:
SQL Server - Import text txt file openrowset microsoft ace oledb 12.0

To import CSV files, you must use the parameters HDR = Yes; FORMAT = Delimited (;). HDR = Yes means that the first line of the file is the header and FORMAT = Delimited (;) means that the columns will be separated by the character “;”.

One of the advantages of CLR and OLE Automation is that they are functions rather than SP, allowing them to use the feature within other functions. In addition, empty lines are returned as empty strings, while in the other two solutions are returned as NULL.

If you want to know how to export database data to text files, learn more by visiting the post. SQL Server - How to export database data to text file (CLR, OLE, BCP)

That's it folks!
Thanks for stopping by and see you in the next post.

sql import txt text file data to database import data from text files to database

sql import txt text file data to database import data from text files to database