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