How to read a text file using T-SQL?

Question

What is the best way to read a text file using T-SQL? I have seen BULK INSERT and many different functions, but I am not looking for them.

I need to read each line in a text file and then insert it into a table with other information such as file name, file name, status, date and time of recording, etc.

BULK INSERT does not allow me to add an extra field unless I miss something on this.

Any help or direction in the right direction will really appreciate it.

Answer 1

You can probably do a bulk insert into the temp table and then add another insert with the data you want to add. Here is an example

CREATE TABLE #TEXTFILE_1( FIELD1 varchar(100) , FIELD2 varchar(100) , FIELD3 varchar(100) , FIELD4 varchar(100)); BULK INSERT #TEXTFILE_1 FROM 'C:\STUFF.TXT' WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' \n') /*You now have your bulk data*/ insert into yourtable (field1, field2, field3, field4, field5, field6) select txt.FIELD1, txt.FIELD2, txt.FIELD3, txt.FIELD4, 'something else1', 'something else2' from #TEXTFILE_1 txt drop table #TEXTFILE_1 

Doesn't that do what you want?

Answer 2

I use a very simple CLR procedure that reads the entire file and splits the lines into lines - it returns one table of value columns. As I said, the CLR code is very simple:

 [MyFileIO.vb] Imports System Imports System.IO Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Collections Imports System.Runtime.InteropServices Partial Public Class TextFiles <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="GetNextSplitString")> _ Public Shared Function FileToTable(ByVal FileName As String) As IEnumerable Dim s() As String Using sr As New StreamReader(FileName) s = Split(sr.ReadToEnd, vbCrLf) End Using Return s End Function Public Shared Sub GetNextSplitString(ByVal Value As Object, <Out()> ByRef Data As SqlChars) Data = New SqlChars(CType(Value, String)) End Sub End Class 

Examples

 select *, getdate() as [CreateDate], 1 as [AnotherColumn], 'xyz' as [ETC] from dbo.FileToTable('c:\file.ext') select line, left(line, 10), right(line, 10) from dbo.FileToTable('c:\file.ext') select ... into [tablename] from dbo.FileToTable('c:\file.ext') 

More details

Compile the CLR DLL as follows:

 c:\windows\microsoft.net\framework\v3.5\vbc.exe /target:library MyFileIO.vb 

Register the CLR DLL as follows:

 create assembly MyFileIO from 'c:\MyFileIO.dll' with permission_set = unsafe go create function dbo.FileToTable (@FileName nvarchar(255)) returns table (line nvarchar(max)) as external name MyFileIO.TextFiles.FileToTable go 

If you get an error, you may need to enable CLR support in db:

 ALTER DATABASE [dbname] SET trustworthy ON go sp_configure 'clr enabled', 1 GO RECONFIGURE GO 

Whenever you change a DLL, you need to drop the procedure and assembly and run the code again from above to reregister it.

Answer 3

You can use Integration Services (SSIS)

Link: http://msdn.microsoft.com/en-us/library/ms141026.aspx

Link: http://technet.microsoft.com/en-us/library/ms169917%28v=sql.105%29.aspx

Answer 4

If OLE Automation is included in SQL Server (and this is large if so many sites have disabled it for security reasons), you can create an instance of Scripting FileSystemObject using sp_OACreate and its related functions.

Answer 5

This can be done using FORMATFILE . When using a format file, you can skip the columns. There are many more advantages to using a file format.

Below the request will load the lines in the Line field.

 CREATE TABLE TextFile ( [Line] varchar(500) , [FileName] varchar(100) , [RecordDate] DATETIME DEFAULT GETDATE(), [RecordID] INT IDENTITY(1,1) , ) BULK INSERT TextFile FROM 'C:\FILE.TXT' WITH (FORMATFILE = 'C:\FILEFORMAT.XML') 

File format used in the above request:

 <?xml version="1.0"?> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="500" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="Line" xsi:type="SQLVARYCHAR"/> </ROW> </BCPFORMAT>