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 AutomationHow 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE FUNCTION [dbo].[fncLer_Arquivo_FSO] ( @Ds_Arquivo VARCHAR(256) ) RETURNS @Tabela_Final TABLE ( Ds_Linha VARCHAR(8000) ) AS BEGIN DECLARE @OLEResult INT DECLARE @FileSystemObject INT DECLARE @FileID INT DECLARE @Message VARCHAR (8000) DECLARE @Tabela TABLE ( Ds_Linha varchar(8000) ) EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FileSystemObject OUT IF @OLEResult <> 0 BEGIN SET @Message = 'Scripting.FileSystemObject - Error code: ' + CONVERT (VARCHAR, @OLEResult) INSERT INTO @Tabela_Final SELECT @Message RETURN END EXEC @OLEResult = sp_OAMethod @FileSystemObject, 'OpenTextFile', @FileID OUT, @Ds_Arquivo, 1, 1 IF @OLEResult <> 0 BEGIN SET @Message = 'OpenTextFile - Error code: ' + CONVERT (VARCHAR, @OLEResult) INSERT INTO @Tabela_Final SELECT @Message RETURN END EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT WHILE (@OLEResult >= 0) BEGIN INSERT INTO @Tabela(Ds_Linha) VALUES( @Message ) EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT END EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FileSystemObject INSERT INTO @Tabela_Final SELECT Ds_Linha FROM @Tabela RETURN END |
FncLer_String_FSO Function Source Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
CREATE FUNCTION [dbo].[fncLer_Arquivo_String_FSO] ( @Ds_Arquivo VARCHAR(256) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @OLEResult INT DECLARE @FileSystemObject INT DECLARE @FileID INT DECLARE @Message VARCHAR (8000) DECLARE @Retorno VARCHAR(MAX) EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FileSystemObject OUT IF @OLEResult <> 0 BEGIN SET @Message = 'Scripting.FileSystemObject - Error code: ' + CONVERT (VARCHAR, @OLEResult) RETURN @Message END EXEC @OLEResult = sp_OAMethod @FileSystemObject, 'OpenTextFile', @FileID OUT, @Ds_Arquivo, 1, 1 IF @OLEResult <> 0 BEGIN SET @Message = 'OpenTextFile - Error code: ' + CONVERT (VARCHAR, @OLEResult) RETURN @Message END EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT SET @Retorno = ISNULL(@Retorno, '') + ISNULL(@Message, '') + CHAR(13) WHILE (@OLEResult >= 0) BEGIN SET @Message = NULL EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT SET @Retorno = ISNULL(@Retorno, '') + ISNULL(@Message, '') + CHAR(13) END EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FileSystemObject RETURN @Retorno END |
Example of use:
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
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
using System.IO; using System.Collections; using System.Data.SqlTypes; public partial class UserDefinedFunctions { private class ArquivoLer { public SqlInt32 Nr_Linha; public SqlString Ds_Texto; public ArquivoLer(SqlInt32 nrLinha, SqlString dsTexto) { Nr_Linha = nrLinha; Ds_Texto = dsTexto; } } [Microsoft.SqlServer.Server.SqlFunction( FillRowMethodName = "FillRow_Arquivo_Ler", TableDefinition = "Nr_Linha INT, Ds_Texto NVARCHAR(MAX)" )] public static IEnumerable fncArquivo_Ler(string Ds_Caminho) { var ArquivoLerCollection = new ArrayList(); if (string.IsNullOrEmpty(Ds_Caminho)) return ArquivoLerCollection; var contador = 1; using (var sr = new StreamReader(Ds_Caminho)) { while (sr.Peek() >= 0) { ArquivoLerCollection.Add(new ArquivoLer( contador, sr.ReadLine() )); contador++; } sr.Close(); } return ArquivoLerCollection; } protected static void FillRow_Arquivo_Ler(object objArquivoLer, out SqlInt32 nrLinha, out SqlString dsTexto) { var ArquivoLer = (ArquivoLer) objArquivoLer; nrLinha = ArquivoLer.Nr_Linha; dsTexto = ArquivoLer.Ds_Texto; } } |
Fnc function source codeFile_Ler_Return_String
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
using System.Data.SqlTypes; using System.IO; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString fncArquivo_Ler_Retorna_String(SqlString Ds_Caminho) { if (Ds_Caminho.IsNull) return SqlString.Null; if (!File.Exists(Ds_Caminho.Value)) return SqlString.Null; using (var sr = new StreamReader(Ds_Caminho.Value)) { return sr.ReadToEnd(); } } } |
Example of use:
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
How to import text files into bank with BCP
This is one of the options most used by DBA's, because it is simple, it is already installed with SQL Server and can be executed both in 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:
|
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO |
Example of use:
Where:
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:
Note: As you may notice, blank lines are imported as NULL by BULK INSERT.
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:
Where:
- SINGLE_BLOB returns the contents of the file 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.
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.
Remember that the provider “Microsoft.ACE.OLEDB.12.0” is not installed by default on SQL Server. It needs to be installed manually. To know more about it, see my post SQL Server - How to Install the Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0 Drivers.
Usage examples:
To import CSV files, you must use the parameters HDR = Yes; FORMAT = Delimited (;). HDR = Yes which 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
