Tranfer data into text file
-
Hi i have a job scheduled that runs every week, this job finally dumps the data in the text file. Now what i need to do is, once we get data in these text files i want to save a copy of those text files in another folder with the date extension. This should be done automatically by the job itself. I think i can do it with the active x script. Can anyone help me out.
Thanks
-
FileSystemObject is what you want to look at.
Have a look at this:
-
Hi thanks for your reply. Is there a way that the files get copied with the date extension to the name. I have tested the activex scripts mentioned in that url that you have mentioned but its giving me an error. This is what i did
Option Explicit
Function Main()
Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "C:\Documents and Settings\XXX\Desktop\Test1\test1.txt"
sDestinationFile = "C:\Documents and Settings\XXX\Desktop\Test2\test2.txt"
oFSO.CopyFile sSourceFile, sDestinationFile
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
-
What is the error?
Do those directories exist?
As far as including the date, you'd need the ActiveX equivalent of getdate() (which I believe is Now()), cast to a string and add that to the file name.
-
Hi when i am tried to Execute this is what i am seeing
The number of failing rows exceeds the maximum specified
Error Code 0
Error Source: Microsoft VB Script Runtime error
Error on line 13
-
This is what I could find on that:
http://blogs.msdn.com/sql_protocols/archive/2005/12/19/505372.aspx
Best guess from where I'm sitting is to make sure the source file exists and that the destination directory(FileSystemObject.FolderExists 
exists. You might also want to look at this:
http://support.microsoft.com/kb/q240221/
-
This is how I am naming a file with the datetime stamp and moving it to a different directory:
Function Main()
' Declare variables
Dim oFSO
Dim sSourceFile, sDestinationFile
Dim sArchiveFile, dtMonth, dtDay, dtYear, dtHr, dtMIN, dtSec
Dim TempFile, sWork, CurrentDateTime
' Set variables to current date/time
dtMonth = month(now())
dtDay = day(now())
dtYear = year(now()) ' Always 4 digits
dtHr = hour(now())
dtMin = minute(now())
dtSec = second(now())
CurrentDateTime = Now()
' Make sure variables are using 2 digits.
if len(dtMonth) = 1 then dtMonth = "0" & dtMonth
if len(dtDay) = 1 then dtDay = "0" & dtDay
if len(dtHr) = 1 then dtHr = "0" & dtHr
if len(dtMin) = 1 then dtMin= "0" & dtMin
if len(dtSec) = 1 then dtSec = "0" & dtSec
' Turn on Scripting
Set oFSO = CreateObject("Scripting.FileSystemObject")
' Set the archive file name to the current date/time
sArchiveFile = dtMonth & dtDay & dtYear & dtHr & dtMin & dtSec & ".txt"
sSourceFile = "\\Server\adt_recv$\cache_db_adt.txt"
sDestinationFile = "\\Server\adt_recv$\cache_archive\" & sArchiveFile
' Move the file
oFSO.MoveFile sSourceFile, sDestinationFile
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
-
Thanks a lot for your reply. This code works perfect but have a question. actually its deleting the source file after copying to the destination folder. I want that source file to be present there with out getting deleted. can you please let me know.
Thanks
-
Can you do the copy command instead of the move command?
oFSO.CopyFile
-
Great !!!!!!!! Thank you so much for your help. This worked.
-
No prob...glad it helped you.
-
If you need to delete the archived files after a certain time period...I am doing that too. I only need to keep them one week.
You could easily modify this to keep them for 90 days or whatever.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
'Delete the files that are older than 1 week. JMR
Option Explicit
Function Main()
Dim oFile ' Declare Variables
Dim oFSO, InFolder, InFile, sqlFile, StartFolder
StartFolder = "\\Server\sharename\directory\"
Set oFSO = CreateObject("Scripting.FileSystemObject") 'Turn on Object Scripting
Set InFolder = oFSO.GetFolder(StartFolder)
Set InFile = Infolder.Files
'Loop Through The Directory to delete files that are more than 1 week old
For Each sqlFile in InFile
'MsgBox StartFolder & sqlFile.name
If sqlFile.DateLastModified < (Date - 7) Then
sqlFile.Delete
End If
Next 'Get next file
' End of Directory Loop
Set oFSO = Nothing ' Clean Up
Main = DTSTaskExecResult_Success
End Function
-
When using dates and times in the filenames, I have found it convenient to have a format like this: YYYY-MM-DD HH:MM:SS. This makes it much easier to sort the files by date and time. Here is a function that may help:
Private Function FormatMyDateTime(dt)
' format as per the ISO standard: yyyy-mm-dd hh:mm:ss
' The 'Right' function helps to ensure 2-digit values
FormatMyDateTime = Year(dt) & "-" _
& Right("0" & Month(dt), 2) & "-" _
& Right("0" & Day(dt), 2) & " " _
& Right("0" & Hour(dt), 2) & ":" _
& Right("0" & Minute(dt), 2) & ":" _
& Right("0" & Second(dt), 2)
End Function
-
Nice!
Thanks, I am a noob that is still learning.
I appreciate your tips.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply