Results 1 to 1 of 1

Thread: BULK INSERT Text Files into SQL/Oracle

  1. #1

    BULK INSERT Text Files into SQL/Oracle

    Good Morning All,

    I have a table in SQL called [RU].[MainTab]. It contains the following fields:

    CKale (nvarchar, 5)
    Checkdt (date)
    RecDate (date)
    PersonID (nvarchar, 6)

    I have a folder on my hard drive containing numerous delimited text files, each one with data that corresponds to the SQL table. Example:


    The code posted below is intended to take all those text files, combine then into one (called "Combined.txt"), and then do a BULK INSERT to append the contents of Combined.txt into the SQL table.

    The combining of the text files works great. The BULK INSERT errors out, only because I can't grant myself BULKADMIN server permissions (I think).

    Here are my questions:

    1. Can someone look at the code, focusing on the part after the text files are combined, and tell me if you think it should work if I were to have BULKADMIN permission to SQL Server? If not, what needs changed?

    2. How would this code differ if I were connecting to an Oracle database versus SQL Server? I'm assuming the DRIVER in the Connection String would change, but once the connection is open, would there be any difference in the BULK INSERT statement?

    [VBA]Sub CombineTextFiles()

    Set fso = CreateObject("Scripting.FileSystemObject")
    'Tools Menu, choose References - check box for "Microsoft Scripting Runtime" and "Microsoft ActiveX Data Objects 2.8 Library"
    Dim fsoObj As Scripting.FileSystemObject
    Dim fsoFolder As Scripting.Folder
    Dim fsoFile As Scripting.File
    Dim TSIn
    Dim TSMain
    Dim Path

    Set fsoObj = New Scripting.FileSystemObject

    'Location of text files
    Path = "C:\OracleTest"

    'Text files will be combined into one for import
    OutputFile = "C:\OracleTest\Combined\Combined.txt"

    'Location of the backup folder that will house original text files after import
    BackupFolder = "C:\OracleTest\Archive"

    Set TSMain = fsoObj.OpenTextFile(OutputFile, ForWriting, True)

    Set fsoFolder = fsoObj.GetFolder(Path)

    For Each fsoFile In fsoFolder.Files
    If fsoFile Like "*.txt" Then
    FullPath = fsoFolder & "\" & fsoFile.Name
    Set TSIn = fsoObj.OpenTextFile(FullPath, ForReading, False)
    Temp = TSIn.ReadAll
    TSMain.Write Temp
    fsoFile.Move (BackupFolder & "\" & fsoFile.Name)
    End If

    Set TSMain = Nothing
    Set TSIn = Nothing

    Set fsoObj = Nothing
    Set fsoFile = Nothing
    Set fsoFolder = Nothing

    'MsgBox "Done!"

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER=SQL Server;DATABASE=DbMain;SERVER=serv1\pro"

    Dim strSQL As String

    strSQL = "BULK INSERT [RU].[MainTab] FROM 'C:\OracleTest\Combined\Combined.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n') GO"
    conn.Execute (strSQL)

    End Sub[/VBA]


    Last edited by firebirdta84; 07-04-2013 at 04:56 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts