tabla - ¿Cómo leer un archivo de texto usando T-SQL?

importar txt a sql server 2012

Question

¿Cuál es la mejor manera de leer un archivo de texto usando T-SQL? He visto BULK INSERT y muchas funciones diferentes, pero ninguna de ellas es lo que estoy buscando.

Necesito leer cada línea en el archivo de texto y luego insertarlo en una tabla con otra información como el nombre de archivo, ubicación de archivo, estado, fecha y hora de grabación creada, etc.

BULK INSERT no me permite agregar campo adicional a menos que me falta algo sobre esto.

Cualquier ayuda o señalando la dirección correcta será realmente apreciada.

Answer 1

Probablemente podría hacer una inserción masiva en una tabla temporal y luego hacer otra inserción uniéndose con los datos que desea agregar. Aquí hay un ejemplo

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

¿Esto no hace lo que te gustaría?

Answer 2

Puede usar los Integration Services (SSIS)

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

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

Answer 3

Si la automatización OLE está habilitada en SQL Server (y es una gran cantidad de sitios si la desactivan por razones de seguridad), puede crear una instancia de Scripting FileSystemObject utilizando sp_OACreate y las funciones relacionadas.

Answer 4

Utilizo un procedimiento CLR muy simple que lee todo el archivo y separa las líneas en filas, devolviendo una tabla de valores de una columna. Como dije, el código CLR es muy 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

Ejemplos

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

Más detalles

Compile la CLR DLL de esta manera:

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

Registre la CLR DLL así:

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

Si obtiene un error, es posible que necesite habilitar el soporte de CLR en el archivo db:

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

Siempre que cambie la DLL, debe abandonar el procedimiento y el ensamblaje, y ejecutar nuevamente el código desde arriba para volver a registrarlo.

Answer 5

Esto se puede hacer usando FORMATFILE . Al usar el archivo de formato, puede omitir las columnas. hay muchas más ventajas de usar archivos de formato.

Debajo de la consulta se cargarán las líneas en el campo 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'')

El archivo de formato utilizado en la consulta anterior es:

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