?
Solved

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

Posted on 2010-09-09
10
Medium Priority
?
770 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

 
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
 
LVL 1

Accepted Solution

by:
exactjb earned 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

762 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