Trying to replace local file links with actual file contents

J Snape

I've been trying to do this for while and haven't made any progress. I have a tab-delimited file with records for a database file. One of the fields has a local file link for a text file. Each record points to a different text file and there are 151,806 rows, so replacing the file names ("C:\files\LAW_SECTION_TBL_1.lob", "C:\files\LAW_SECTION_TBL_2.lob", ..., "C:\files\LAW_SECTION_TBL_151806.lob") one-by-one with the file text will take a decade or so. The .lob files are utf-8 plain text files. I'd like to load the text files into the individual records as I insert all the data, but I'm not sure how to do it. The file I have to load it into MySQL has the following lines (shortened to essentials only):

...
,HISTORY
,@var1 // Where the "C:\files\LAW_SECTION_TBL_1.lob" is stored
,ACTIVE_FLG
,TRANS_UID
,TRANS_UPDATE
)
SET CONTENT_XML=LOAD_FILE(concat('c:\\files\\',@var1))

I just don't know how to do it for MS SQL Server. Any help or links to webpages that will help me write this INSERT statement are extremely appreciated!

J Snape

I figured it out in Excel using the following code on a macro:

Sub Button1_Click()
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String

Dim i As Long
For i = 2 To 151807
    
    FilePath = "C:\\pubinfo\\" & Cells(i, 8).Value
    
        TextFile = FreeFile
        Open FilePath For Input As TextFile
        FileContent = Input(LOF(TextFile), TextFile)
        Close TextFile
        
    FileContent = Right(FileContent, Len(FileContent) - 74)
    FileContent = Left(FileContent, Len(FileContent) - 15)
    
    Cells(i, 9).Value = FileContent
Next i

End Sub

It took about two hours to pull in all the text, by the way!

jschell

Just noting.... "lob", from the file name suggests binary data. In general, programming languages differentiate between binary and text data. Your code used a "String" type. That works under one or both of the following conditions. 1. The files wear in fact text 2. Excel did not attempt to translate them. You can validate the above by exporting at least one file and verifying that it matches the input both in length and content.