Link to home
Create AccountLog in
Avatar of GlobaLevel
GlobaLevelFlag for United States of America

asked on

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...
Avatar of chadcku
chadcku

Do you have an example of some of the lines from the text file? What is the column delimiter?
Avatar of GlobaLevel

ASKER

RED:                                       Blue:                               Green:
____                                      _____                              ______

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


Green is a very lengthy column
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
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 : )
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... : )
ASKER CERTIFIED SOLUTION
Avatar of exactjb
exactjb
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?
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
very good..let me apply..and I will be back to award points to you this week..thank you...
awesome!!!