Solved

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

Posted on 2010-09-09
10
761 Views
Last Modified: 2012-05-10
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...
0
Comment
Question by:GlobaLevel
  • 6
  • 3
10 Comments
 
LVL 1

Expert Comment

by:chadcku
ID: 33639300
Do you have an example of some of the lines from the text file? What is the column delimiter?
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33639466
RED:                                       Blue:                               Green:
____                                      _____                              ______

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


Green is a very lengthy column
0
 
LVL 1

Expert Comment

by:exactjb
ID: 33639894
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33639983
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33640014
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Accepted Solution

by:
exactjb earned 500 total points
ID: 33641883
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33641959
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
 
LVL 1

Expert Comment

by:exactjb
ID: 33645912
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33662505
very good..let me apply..and I will be back to award points to you this week..thank you...
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33679338
awesome!!!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
The viewer will learn how to count occurrences of each item in an array.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now