Solved

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

Posted on 2010-09-09
10
762 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This article will show, step by step, how to integrate R code into a R Sweave document
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

947 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

20 Experts available now in Live!

Get 1:1 Help Now