How to create watched folders.

I would like to create a watched folder in order to pull from a file A.S.A.P when it hits the folder. From there I want to upload or read the file into sequel server 2003 database table(s). I have tried using Data Loader and an evaluation of File Watcher which already exist for this kind of thing, but they cause errors while uploading to sequel server or need added modules that I can't seem to find. Any help would be appreciated. Has anyone else done this successfully?

LVL 1
jimmyb0004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chrisdunn_6Commented:
Probly sounds elementary but what about task sceduler every couple of minutes combined with if exsists statements.
0
jimmyb0004Author Commented:
I would prefer to watch the folder and then update transaction to file a.s.a.p, if I could. I have data loader which can schedule and grab the file every minute if I want it to, so I would probably try to find a way to integrate that with a batch file every 2 minutes or something that would rename the file and extension so that it would not be seen again by that program. As I said though, I'm hoping to find a solution that can do this more properly, either with data loader or by writing it myself if I can find someone that has done this before. Thanks for the input though, it's valid.
0
chrisdunn_6Commented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chrisdunn_6Commented:
Here is the VB way
http://www.devguru.com/features/tutorials/watchfolderact/watchfolder.asp

Data loader does not have the ability to continually watch a folder just intervals as we were talking about but using these 2 links I've givin you, you might be able to come up with something.  
0
chrisdunn_6Commented:
last but not least resort to batch file simple but should work

:start
xcopy (file) (location)
if errorlevel 1goto start
ren (file) (different file)
call some program
goto start
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jimmyb0004Author Commented:
I'll check the links out and get back to you. I want to correct one thing for you though. Data loader does have the ability to watch a folder in a sense. It's not exactly what I'm looking for but what they do is Folder Polling, which allows you to set up an interval to check that folder every minute, minute being the most frequent, and then if it finds what it's looking for, .txt file, etc.. , it will process it and then rename it so that it is no longer a .txt file and will not be seen. If it worked, that would be good enough for me, but when you use folder polling with data loader, it gives you an error when uploading into sequel that says, field size of {...} is to large for the maximum field size of {8060}, or something  like that. It doesn't seem to honor your field types as it does if you don't use folder polling. Not sure why this only happens during folder polling, but I can't seem to find a way around it without loosing my data type(s), depending on which field(s) are being manipulated. I'll check out these links tonight when I get home from work, and I'll post back again tomorrow and keep everyone updated. Thanks for the help so far chris. Hopefully I can find something in those links.
0
chrisdunn_6Commented:
Data loader does not have the ability to continually watch a folder(( just  in intervals as we were talking about)) but using these 2 links I've givin you, you might be able to come up with something.

Your not using the trial version are you? If you bought data loader I would go here and send an email to technical support. Trial has limitations.
http://www.dbload.com/support.htm

Otherwise try the links for info. I myself would create a loop as I outlined above dont use batch if you dont want to. once the statement becomes true execution begins and loop restarts in seconds not minutes.
0
Anthony PerkinsCommented:
>>From there I want to upload or read the file into sequel server 2003 database table(s). <<
Would that be SQL Server 2000 or SQL Server 2005?  The answer could be different in either case.
0
chrisdunn_6Commented:
Depends, I dont even know what file type were trying to upload. Im sure if I had all the info I could provide a better answer.
0
Anthony PerkinsCommented:
I am sorry, but my question was directed at the author's comment about using "sequel server 2003".
0
jimmyb0004Author Commented:
sorry.. I meant 2000.
0
jimmyb0004Author Commented:
I was thinking of our server when I said 2003. Chris I did contact dbloader and they directed us to the most current download we could get in order to fix a known issue. So I do have the most current file version of pro that is available. As for the file type that we're trying to upload. I'm trying to export data from one place to a delimited .txt file and then reading that into sequel. Hope this more accurate info helps.
0
chrisdunn_6Commented:
Do you know the line of code to initiate the dbloader transaction. By that I mean how you manually use dbloader without polling.
0
jimmyb0004Author Commented:
I'm not exactly sure what you're asking, but if I do, then the answer is that dbloader uses a GUI like wizard to help set up scheduling so that it will check the same location for the same file every day at that particular time you have set. So, it goes by exact location, exact filename, and exact time you set within the dbloader software scheduler. It creates a schema.ini file inside the directory where the file you want to watch is located. I assume the schema is holding the info about what dbloader has predetermined the file types to be, etc.. from it's first run.
0
jimmyb0004Author Commented:
Below is a copy of the Schema.ini file that dbloader uses. I guess it only uses it for the delimiter, which in this case, I use a pipe symbol " | " and header info, which  asks if the first line has headers or not (it doesn't), and the rest you can see yourself. This is a customer table pulled into a .txt file. When I schedule the dbloader to run at certain times, you have the choice to run it the first time or just schedule it. I run it to create the table and then just delete existing rows so that the new info comes in for a report. Hope this additional info helps. I'm looking at the links you provided chris and might be able to do something in .net or with the code you provided. I'll check that out in more detail today as I go. More info while I try these out is greatly appreciated by anyone of course. Thanks again all. I'll keep checking back and updating if there's anything to update on my end. :)

[R_CU_BA_Base.txt]
Format=Delimited(|)
ColNameHeader=False
MaxScanRows=0
TextDelimiter=""
0
jimmyb0004Author Commented:
By the way. After I export the, pipe delimited, info to the text file, I then am trying to get it to upload to the "Sequel server 2000 tables" so that I can create reports from there. Sorry about any confusion or lack of info before.
0
chrisdunn_6Commented:
Have you taken a look at this yet?
http://www.watchdirectory.net/?src=gowafo

might create less work for you.
0
chrisdunn_6Commented:
Here is some awsome info I stumbled on. Use my loop method to check files then use the info from this site to upload the text file (asuming its a csv).
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

0
chrisdunn_6Commented:
just use your loop program to activate the script when the staement becomes true.
0
jimmyb0004Author Commented:
Just wanted everyone to know that I didn't forget about this thread, just got sidetracked with some other issues here at work for a few days. I'll get back to this shortly and keep everyone up to date on trying some more of chrisdunn 6's fixes.
0
chrisdunn_6Commented:
awesome Im still watching this thread so let me know if you get stuck along the way.
0
chrisdunn_6Commented:
I beleive the conditional loop I exampled ealier on was a good starting point. Also gave plenty of links.
0
jimmyb0004Author Commented:
I haven't yet abandoned it. I just got redirected at work to another issue at the moment. I will be coming back to it to test out some of these things as well. If you are going to close it that is ok with me. But I would like to give full points to chrisdunn_6 for his insightful directions. The only reason I have not assigned points to a solution yet is that I do not know which of the possible solutions he has entered will bring me to where I need to be and therefore don't know where the actual solution lies. I'm sure it will be one of these from chrisk however, when I do get a chance to get back to it. This is information for work and I will be getting back to it at some point. Sorry for the delay and I don't know when I will be able to get back to it. Could be a week, or could be a month. Hopefully it will be within the next week or two, but I can't promise anything. Thank you chris for everything and If I don't get to post back to this again. I appreciate your help. I'm sure one of if not a combination of your answers will be the answer I'm looking for.
0
chrisdunn_6Commented:
well if the question stays open I will still help.
0
jimmyb0004Author Commented:
Thanks chris, I appreciate that. Hopefully I can get back to it soon, but as I'm sure you know, work can redirect ya for a bit here and there :-)

Thanks again for the help. I'll be back on this asap, and I will be testing out implementing your ideas.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.