FileSystemWatcher to SQL 2005
-
I have a comma delimited text file that I am trying to import into SQL on the fly. For the sake of discussion, the file would be formatted like this; yyyy/MM/dd HH:mm:ss,022.514.045,Site Name,TX,0000,0000,Alarm Name,ON File name RWALARMSyyyyMMdd.txt I understand how to monitor a directory with FSW, not the contents of a file. Any suggestions would be greatly appreciated. Thanks,
-
I have a comma delimited text file that I am trying to import into SQL on the fly. For the sake of discussion, the file would be formatted like this; yyyy/MM/dd HH:mm:ss,022.514.045,Site Name,TX,0000,0000,Alarm Name,ON File name RWALARMSyyyyMMdd.txt I understand how to monitor a directory with FSW, not the contents of a file. Any suggestions would be greatly appreciated. Thanks,
I assume an outside process is appending to this file and you want to then import that new information to SQL? The FSW will of course tell you the file has changed. You will then need to verify that outside process has completed using the file. You do this by attempting to open the file for writing and only proceed when you have an exclusive lock on the file. Now at this point, there are a few ways to do it (your choice depends on how you exactly get the data), but I will describe what I found to be the easiest for me. If it's the first time opening a file just import the entire contents into SQL and then I will write a comment line that is the timestamp of when I imported that batch (ie. # MM/dd/yyyy HH:mm:ss (# of records) imported) So I know the next time the FSW kicks off my process, I just jump to the last comment marker I wrote to the file and ready everything appended after that. Another option is to get the last record date from SQL and only read the records from the file with a greater date. If your text file isn't sequential, this approach could add more code to sort the file by date or another approach. You could also create an import table in the database that has the timestamp and the line number offset from the previous import and use that number to jump within the text file. Hope I've given you some ideas on how you can approach the file importing. Also, just in case I misunderstood and you wanted code specific stuff and not appracoh opinions then look into the TextReader and TextWriter classes, along with ADO.NET
-
I assume an outside process is appending to this file and you want to then import that new information to SQL? The FSW will of course tell you the file has changed. You will then need to verify that outside process has completed using the file. You do this by attempting to open the file for writing and only proceed when you have an exclusive lock on the file. Now at this point, there are a few ways to do it (your choice depends on how you exactly get the data), but I will describe what I found to be the easiest for me. If it's the first time opening a file just import the entire contents into SQL and then I will write a comment line that is the timestamp of when I imported that batch (ie. # MM/dd/yyyy HH:mm:ss (# of records) imported) So I know the next time the FSW kicks off my process, I just jump to the last comment marker I wrote to the file and ready everything appended after that. Another option is to get the last record date from SQL and only read the records from the file with a greater date. If your text file isn't sequential, this approach could add more code to sort the file by date or another approach. You could also create an import table in the database that has the timestamp and the line number offset from the previous import and use that number to jump within the text file. Hope I've given you some ideas on how you can approach the file importing. Also, just in case I misunderstood and you wanted code specific stuff and not appracoh opinions then look into the TextReader and TextWriter classes, along with ADO.NET
Yes, you assumption is correct. A program that was written by someone else, writes a log entry each time a piece of equipment has a failure or clears the failure. There is no file locks on the file, and I can manually edit it as necessary. The system opens a new file @ midnight each night, so that would be the first time it is opened. I didn't mention it, but I envision this as a Windows Service so that it will be done automatically. Yes, I am looking for some code assistance. I can do the SQL piece, I am just not firmiliar with the FSE for monitoring changes to the contents of a file. Thanks,