Index Server Faq

Loading text data into your database using TextCopy

you can also use textcopy and use the stream object. Here are examples of both.

Set objConn = CreateObject("ADODB.Connection")

Set objShell = CreateObject("Wscript.Shell")

objConn.Open "Provider=SQLOLEDB;data Source=ServerName;Initial Catalog=TEXT;User Id=Account;Password=Password"

set objFileSystem=createobject("Scripting.FileSystemObject")

set objDir=objFileSystem.GetFolder("c:\txt")

for each objFile in objDir.Files

count=count+1

wscript.echo objFile.name

objConn.Execute "insert into TextTable (TextCol) values ('gibberish')"

UpdateString="c:\winnt\system32\cmd.exe /C c:\progra~1\MI6841~1\mssql\binn\textcopy.exe /s ServerName /u Account /p Password /d

Text /t texttable /c TextCol /f " & objFile.Path & " /i /k

5000 /Z /w " & chr(34) & " where PK="& count & chr(34)

wscript.echo UpdateString

objShell.Run UpdateString

next

objConn.Close

msgbox "Wait for all windows to close before clicking this

button to terminate this script"

Set objShell =nothing

Set objConn=nothing

Set objFileSystem=nothing

Set objDir=nothing

This script works for loading both text and image data.

Note how we have to input a value into the text column on

the first insert as the TextCopy binary needs a non null

text pointer. Your location of the textcopy executable

may vary. You might want to copy it to c:\

If you are using this script to load text files into text

column uncomment the commented sections and commend the

lines beneath them. The text files you load using this

method must be Unicode.

Allan Mitchell (allan@SQLDTS.com) has provided me

with this stored procedure that you can use to load

text/image files as well.

CREATE PROCEDURE usp_extFiles @FilePath varchar(255)

AS

SET NOCOUNT ON

BEGIN

DECLARE @ExecString varchar(200)

declare @IdentVal int

INSERT TextTable(textCol) Values('')

select @IdentVal = @@identity

select @ExecString

= 'C:\textcopy /SMF5713 /DText /TTextTable /W"WHERE PK = ' + cast(@identval as varchar(3)) + '" /F"' +@filePath + '" /I /CtextCol /Usa /PYou_Wish' exec master..xp_cmdshell @Execstring

The VB code that Allan uses to call this is as follows

Dim cn As ADODB.Connection

Dim fil As file

Dim fold As Folder

Dim fso As FileSystemObject

Private Sub cmdImport_Click()

Dim cnString As String

Set fso = New FileSystemObject

Set cn = New ADODB.Connection

cnString = "Provider = SQLOLEDB;Data Source =

IISFAQ;initial catalog =Text

;Integrated Security = SSPI"

cn.Open cnString

Set fold = fso.GetFolder("c:\files")

For Each fil In fold.Files

cn.usp_TextFiles fil.Path

Next fil

End Sub