BULK INSERT Text Files into SQL/Oracle

[firebirdta84]

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:

12345,4/5/2013,4/6/2013,123456

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
TSIn.Close
TSMain.Write Temp
fsoFile.Move (BackupFolder & "\" & fsoFile.Name)
End If
Next

TSMain.Close
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"
conn.Open

Dim strSQL As String

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

End Sub[/VBA]

Thanks!

Joe