Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to read a Text file using T-SQL?

Tags:

sql

tsql

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.

like image 343
jorame Avatar asked Sep 19 '12 20:09

jorame


People also ask

How do I read a text file in SQL query?

Syntax: SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name; This query will read the content of the text file and return it as a single column in a table named Bulkcolumn. The correlation name is mandatory to specify.

How do I run a text file in SQL?

command: mysql> source file_name mysql> \. For this you can insert statements like this: SELECT '<info_to_display>' AS ' '; The statement shown outputs <info_to_display> . You can also invoke mysql with the --verbose option, which causes each statement to be displayed before the result that it produces.

How do I run a Notepad file in SQL?

Create a script file To create a simple Transact-SQL script file by using Notepad, follow these steps: Click Start, point to All Programs, point to Accessories, and then click Notepad. Save the file as myScript. sql in the C drive.


3 Answers

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?

like image 168
DiverseAndRemote.com Avatar answered Oct 23 '22 17:10

DiverseAndRemote.com


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.

like image 37
James L. Avatar answered Oct 23 '22 18:10

James L.


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

like image 24
Aghilas Yakoub Avatar answered Oct 23 '22 16:10

Aghilas Yakoub