DROP TABLE Query to the Event Handlers tab or else it will error the second time it is reRun.
' Currently this VBScript is set to replace-commas-with-pipes for every file that is
'in the folder where this VBScript sits:
' Before run it do the following:
' 1. Open VBScript in text editor
' 2. Set the parameters:
' cFolder_Name = "." ' current directory
' cOnlyOneFile = False ' set to True if I want to process only one file with name set below
' cOnlyFileName = "thisfile.csv"
' 3. Place the VBScript into the target folder
' 4. Run it by double-click
' Notes: http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/79ddd204ebb88287/be5d195da9241df9%23be5d195da9241df9
' -------------------------------------------------------
On Error Resume Next
const cFolder_Name = "." ' current directory
const cOnlyOneFile = False ' False, So all files in the folder.
const cOnlyFileName = "thisfile.csv" ' Uses this if the above was True (only wanted to process 1 file).
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Get folder:
Set oFolder = oFSO.GetFolder(cFolder_Name)
If oFolder Is Nothing Or Err.Number <> 0 Then
MsgBox "Can't get a folder for search files. " + vbCRLF + "Please check the folder name." + vbCRLF + Err.Description, vbCritical, "Fatal error!" ' I will probably comment this out when this VBScript runs automatically as a scheduled job.
End If
Set oFiles = oFolder.Files
If Err.Number <> 0 Or oFiles Is Nothing Then
MsgBox "Can't get a list of files of folder." + vbCRLF + "" + vbCRLF + Err.Description, vbCritical, "Fatal error!" ' I will probably comment this out when this VBScript runs automatically as a scheduled job.
End If
Cnt = CInt(0)
For Each oFile In oFiles
If oFSO.GetExtensionName(oFile.Name) = "csv" Then ' Checks for "csv" file extension.
If (cOnlyOneFile And (oFile.Name = cOnlyFileName)) Or (Not cOnlyOneFile) Then
Call ReplaceSymbols(oFile.Name)
Cnt = Cnt + 1
End If
End If
Next
Senmail()
MsgBox "Replacing is done. Total number of files had been processed: " + CStr(Cnt), vbExclamation, "Message..." ' I will probably comment this out when this VBScript runs automatically as a scheduled job.
' cleanup
Set oFiles = Nothing
Set oFSO = Nothing
' Find-and-replace:
Sub ReplaceSymbols(oFileName) ' AKA "File.Name".
If oFSO.FileExists(oFileName) Then
Set oTextFile = oFSO.OpenTextFile(oFileName, 1, False)
sFileContents = oTextFile.ReadAll
oTextFile.Close
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Global = True
.IgnoreCase = False
.Pattern = "," ' Or vbTab or "|" etc...
sFileContents = .Replace(sFileContents, "|")
' .Pattern = "\|" ' reverse replacing
' sFileContents = .Replace(sFileContents, ",")
End With
Set oNewFile = oFSO.CreateTextFile(oFileName, True) ' Maybe oNewFile is not needed--it just could have been called oTextFile.
oNewFile.Write sFileContents
oNewFile.Close
End If
End Sub
' NOTE: This first way would only work on my PC, not on the other user's PCs; so I must use the second snippet, Senmail(), at the bottom. NOTE: If I made the changes marked "future testing" this might work.
' Send email to Jon Doe. --If the PC this runs on is not set right, this email will not get sent.
'Set oMessage = CreateObject("CDO.Message")
'oMessage.From = "Generated automatically for AWARE " ' NOTE: If future testing: Comment this out.
'oMessage.To = "jon.doe@jd.com,jon.doe2@jd.com" ' NOTE: If future testing: Should be ";".
'oMessage.Subject = "AWARE: Extracts done."
'oMessage.Sender = "jon.doe@jd.com" ' NOTE: If future testing: Comment this out.
'oMessage.TextBody = "AWARE: Commas replaced with pipes."
'oMessage.Send
' NOTE : Is using MS Outlook API to send e-mails. So check If MS Outlook installed on a workstation before a using of this script.
Sub Senmail()
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(0)
With objOutlookMsg
' This line caused it not to even popup warning, so leave it off: .From = "jon.doe@jd.com " ' "Generated automatically for AWARE "
.To = "jon.doe@jd.com; jon.doe2@jd.com; jon.doe3@jd.com; jon.doe4@jd.com"
.Subject = "AWARE: Extracts done."
' This line caused it not to even popup warning, so leave it off: .Sender = "jon.doe@jd.com" ' NOTE: jon.doe2's email is automatically inserted here because it is sent from here Outlook.
.Body = "AWARE: Commas replaced with pipes. [Note: This message was generated and sent automatically.]"
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
An SSIS to copy tables from a Source to a Destination. Directions:
Note 1: Your Properties for the Transfer SQL Server Objects Task will now look like this:
Note 2: A DB "restore" by the DBA would nix Destination tables that do not exist in Source tables; but not this technique.