vbscript - read .txt file every 10 minutes...if contains data send to sql server 2005

I have a txt file that has 3 columns one red, blue and green...
if the txt file fills with data...then
vbscript will launch that will take those 3 columns and insert into a table on a remote database that has 3 columns....then resets the .txt file by delete all data in the text file


f the.txt is empty then skip...
LVL 10
GlobaLevelAsked:
Who is Participating?
 
exactjbConnect With a Mentor Commented:
OK, I could not test this fully, but this should be very close to what you want. You will see you have to define the folder names, the column definitions of the flat file, all the SQL Server connection info, and the Table name and the real column names.

You also might want to all a SQL column for the timestamp of the file (fileID in the code) and insert that into the row as well. I have found that such information is valuable for answering tha age old question "Why in the world is that row in the table?"

The replace of the apostrophe with double apostrophes is a quick and dirty way to deal with the SQL Insertion threat, not to mention the innocent apostrophe sent in the data.

Let me know how it works out.
John


Dim colRedStart ' as long
Dim colRedEnd ' as long
Dim colBlueStart ' as long
Dim colBlueEnd ' as long
Dim colGreenStart ' as long
' No Green end - goes till End of Line
Dim folderPublic ' as String
Dim folderProcessing ' as String
Dim folderArchive ' as String
Dim fileNamePublic ' as String

Sub Main()
	colRedStart = 0
	colRedEnd = 19
	colBlueStart = 20
	colBlueEnd = 49
	colGreenStart = 50
	' for the following three folders, we need full read and write rights
	folderPublic = "c:\pubfolder\"
	folderProcessing = "c:\procfolder\"
	folderArchive = "c:\archiveFolder\"
	fileNamePublic = "contents.txt"
	'1) Check if the file exists. If not, create an empty file and go to sleep again.
	Dim fileIsHere ' as bool
	Dim fso
	Set fso = WScript.CreateObject("Scripting.Filesystemobject")
	fileIsHere = fso.FileExists(folderPublic & fileNamePublic)
	If Not fileIsHere Then
		fso.CreateTextFile (folderPublic & fileNamePublic)
		Set fso = Nothing
		Exit Sub
	End If
	'2) Is the file zero bytes? If so, simply go to sleep again.
	Dim theFile ' as File
	Set theFile = fso.GetFile(folderPublic & fileNamePublic)
	If theFile.Size = 0 Then
		Set theFile = Nothing
		Set fso = Nothing
		Exit Sub
	End If
	Set theFile = Nothing
	Set fso = Nothing
	'3) Attempt to rename the text file. If not successful, wait five seconds and return to this step.
	Dim RenameWorked ' as bool
	Do
		Set fso = WScript.CreateObject("Scripting.Filesystemobject")
		Set theFile = fso.GetFile(folderPublic & fileNamePublic)
		Dim fileID ' as string
		Dim nowTime ' as time
		nowTime = Now
		fileID = Right("000" & FormatNumber(Year(nowTime), 0, TristateFalse, TristateFalse, TristateFalse), 4) & Right("0" & FormatNumber(Month(nowTime), 0), 2) & Right("0" & FormatNumber(Day(nowTime), 0), 2) & Right("0" & FormatNumber(Hour(nowTime), 0), 2) & Right("0" & FormatNumber(Minute(nowTime), 0), 2) & Right("0" & FormatNumber(Second(nowTime), 0), 2)
		On Error Resume Next
		theFile.Name = fileID & fileNamePublic
		On Error Goto 0
		RenameWorked = (Err.Number = 0)
		If Not RenameWorked Then
			' try later
			Set theFile = Nothing
			Set fso = Nothing
			WScript.Sleep 5000
		End If
	Loop Until RenameWorked
	'4) After the rename is successful, put a new blank file in place so that others can write to it.
	fso.CreateTextFile (folderPublic & fileNamePublic)
	'5) Now the renamed file can be processed, the contents written to the SQL Server and the renamed file either erased or archived.
	theFile.Move (folderProcessing & fileID & fileNamePublic)
	' Connect to a SQL Server Database
	Dim objConnection ' As ADODB.Connection
	Set objConnection = CreateObject("ADODB.Connection")
	objConnection.Open _
	"Provider=SQLOLEDB;Data Source=SomeServer;" & _
	"Trusted_Connection=Yes;Initial Catalog=SomeDatabase;" & _
	"User ID=whatever\name;Password=obvious;"
	' write the records
	Dim ts ' as TextStream
	Set ts = theFile.OpenAsTextStream(ForReading, TristateFalse) ' ASCII mode - Use TristateTrue for UniCode
	Do While Not ts.AtEndOfStream
		Dim thisLine ' as String
		thisLine = ts.ReadLine
		' now get the individual column values
		Dim thisRed, thisBlue, thisGreen
		thisRed = Mid(thisLine, colRedStart + 1, colRedEnd - colRedStart + 1)
		thisBlue = Mid(thisLine, colBlueStart + 1, colBlueEnd - colBlueEnd + 1)
		thisGreen = Mid(thisLine, colGreenStart + 1)
		objConnection.Execute "INSERT INTO SomeTable (Red, Green, Blue) VALUES ('" & Replace(thisRed, "'", "''") & "','" & Replace(thisBlue, "'", "''") & "','" & Replace(thisGreen, "'", "''") & "')"
	Loop
	ts.Close
	Set ts = Nothing
	' close the db connection
	objConnection.Close
	Set objConnection = Nothing
	' archive the file
	theFile.Move (folderArchive & fileID & fileNamePublic)
	Set theFile = Nothing
	Set fso = Nothing
End Sub

Open in new window

0
 
chadckuCommented:
Do you have an example of some of the lines from the text file? What is the column delimiter?
0
 
GlobaLevelAuthor Commented:
RED:                                       Blue:                               Green:
____                                      _____                              ______

Tomatoes @123.                   Blueberries&3r5tf)            Tomatoes and blueberries...
Beets @w34rf.                       Eggplant&drkfkk)             Eggplants and beets.....


Green is a very lengthy column
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
exactjbCommented:
When you say "Green is a very lengthy column", do you mean thousands, millions or billions of characters? Also, is the text file Unicode?

From your example, I assume you mean that each column will start at a fixed position. I would also assume you mean that each record ends with a CRLF. Whatever the end of record flag is, you have to be CERTAIN it will not occur in one of your fields.

So based on this, I would do something like this:
1) Check if the file exists. If not, create an empty file and go to sleep again.
2) Is the file zero bytes? If so, simply go to sleep again.
3) Attempt to rename the text file. If not successful, wait five seconds and return to this step.
4) After the rename is successful, put a new blank file in place so that others can write to it.
5) Now the renamed file can be processed, the contents written to the SQL Server and the renamed file either erased or archived.

Once we have a few more details, this can be fleshed out...
John
0
 
GlobaLevelAuthor Commented:
I will keepit as simple as possible and follow the format of an email where
Red= "From"
Blue = "Subject"
Green = "body of message"


as far as CRLF...the records themselves dont contain this....but really important to preserve the columns...
exactly the same setup...just imagine it as email from g mail or somthing...


this will give you all that you need : )
0
 
GlobaLevelAuthor Commented:
So based on this, I would do something like this:
1) Check if the file exists. If not, create an empty file and go to sleep again.
2) Is the file zero bytes? If so, simply go to sleep again.
3) Attempt to rename the text file. If not successful, wait five seconds and return to this step.
4) After the rename is successful, put a new blank file in place so that others can write to it.
5) Now the renamed file can be processed, the contents written to the SQL Server and the renamed file either erased or archived.

>>>>>>WOW outstanding!! making a temp copy is really a great idea...remember the sql db is on a different computer...

hopefully an example of an email will help same data cast and everything... : )
0
 
GlobaLevelAuthor Commented:
really nice...I will try over the weekend..I was curious though ...how much a performance ding will it be with the wscript.sleep....I understand its almost a infinite loop that will loop 24 7 as this txt file will need to be watch all day long...I was going to put it on a dedicated client pc so to handle the performance issues better...any ideas?
0
 
exactjbCommented:
Well, the current documentation from Microsoft indicates that it is no hit at all. See http://msdn.microsoft.com/en-us/library/6t81adfd.aspx, which reads "The thread running the script is suspended, releasing its CPU utilization."
John
0
 
GlobaLevelAuthor Commented:
very good..let me apply..and I will be back to award points to you this week..thank you...
0
 
GlobaLevelAuthor Commented:
awesome!!!
0
All Courses

From novice to tech pro — start learning today.