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
Solved

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

Posted on 2010-09-09
10
765 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Server 2012 management. 5 33
Everything left of comma in excel 4 44
VBA or Script to identify files which are duplicate in a folder 6 31
BATCH to EXE Converter 2 33
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

808 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