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>