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...
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...
Do you have an example of some of the lines from the text file? What is the column delimiter?
ASKER
RED: Blue: Green:
____ _____ ______
Tomatoes @123. Blueberries&3r5tf) Tomatoes and blueberries...
Beets @w34rf. Eggplant&drkfkk) Eggplants and beets.....
Green is a very lengthy column
____ _____ ______
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
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
ASKER
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 : )
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 : )
ASKER
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... : )
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
John
ASKER
very good..let me apply..and I will be back to award points to you this week..thank you...
ASKER
awesome!!!