如何使用T-SQL读取文本文件是最好的方法?我看过BULK INSERT和许多不同的函数,但它们都不是我要找的。
我需要读取文本文件中的每一行,然后将其插入到一个表中,同时还需要添加一些其他信息,例如文件名、文件位置、状态、记录日期和时间等。
BULK INSERT不允许我添加额外的字段,除非我在此方面有所遗漏。
任何帮助或指向正确方向的指导都将不胜感激。
如何使用T-SQL读取文本文件是最好的方法?我看过BULK INSERT和许多不同的函数,但它们都不是我要找的。
我需要读取文本文件中的每一行,然后将其插入到一个表中,同时还需要添加一些其他信息,例如文件名、文件位置、状态、记录日期和时间等。
BULK INSERT不允许我添加额外的字段,除非我在此方面有所遗漏。
任何帮助或指向正确方向的指导都将不胜感激。
你可以将数据批量插入到一个临时表中,然后再通过与要添加的数据进行连接来进行另一次插入。下面是一个示例:
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
这不是你想要的吗?
我使用一个非常简单的CLR过程,它读取整个文件并将行分离成行 - 返回一个值的一列表。就像我说的那样,CLR代码非常简单:
[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')
按照以下方式编译CLR DLL:
c:\windows\microsoft.net\framework\v3.5\vbc.exe /target:library MyFileIO.vb
按照以下方式注册CLR DLL:
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
如果您遇到错误,可能需要在数据库中启用CLR支持:
ALTER DATABASE [dbname] SET trustworthy ON
go
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Integration Services
(SSIS)。可以使用FORMATFILE
来完成此操作。使用格式文件时,您可以跳过列。使用格式文件有许多其他优点。
以下查询将批量加载行到字段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')
以上查询中使用的文件格式为:
<?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>
如果在SQL Server中启用了OLE Automation(许多站点因安全原因都会禁用它),则可以使用sp_OACreate和相关函数创建Scripting FileSystemObject
的实例。