How to read a Text file using T-SQL?

Question

What's the best way to read a text file using T-SQL? I've seen the BULK INSERT and many different functions but non of them are what I'm looking for.

I need to read each line in the text file and then insert it into a table with some other information like filename, filelocation, status, record date & time created, etc.

The BULK INSERT does not allow me to add extra field unless I'm missing something on this.

Any help or pointing the right direction will be really appreciate it.

Solution 1

You could probably do bulk insert into a temp table and then do another insert joining 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

Does this not do what you'd like?

Solution 2

I use a very simple CLR procedure that reads the entire file and separates the lines into rows -- returning a one column table of values. Like 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 like this:

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

Register the CLR DLL like this:

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 the CLR support in the db:

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

Whenever you change the DLL, you have to drop the procedure and the assembly and run the code from above again to re-register it.

Solution 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

Solution 4

If OLE Automation is enabled in SQL Server (and it's a big if as many sites disable it for security reasons), you can create an instance of the Scripting FileSystemObject using sp_OACreate and related functions.

Solution 5

This can be done using FORMATFILE .When using format file you can skip the columns. there are many more advantages of using format file.

Below query will bulk load the lines into field Line.

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')

Format File used in the above query is :

<?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>

View more solutions

Comments

[DiverseAndRemote.com]

changed to temp table as you cant bulk insert into table variables for some reason