October 2, 2013 at 1:54 am
I have a CSV file which is getting information about every 30 seconds from a third party program and I want that data to be imported into my SQL DB. I tried using the import export wizard but it didn't like it because the file was being used by the third party program.
Is anyone able to provide any insights? Apologies for my noobyness, I am brand new to SQL!
October 2, 2013 at 2:01 am
Sounds like you attempted the import at the very moment the CSV file was being written to by the third-party program. Try creating an SSIS package and running it in a job with a couple of retries. Since you're new to SQL Server, SSIS will be a bit of a learning curve for you. You'll need to seek help from colleagues or try some online tutorials on getting started in SSIS.
John
October 2, 2013 at 2:18 am
Thanks for the quick reply. That sounds good. I will have a look into that and try and figure out SSIS.
Do you think it will have an issue with the fact that the file will be being utilised by the third part program?
October 2, 2013 at 2:23 am
Definitely, which is why I suggested the retries on the job. Do you have any figures on how long the application takes to transfer the data into the CSV file? I'm assuming that the lock is only held while this is happening. If it's held permanently then you have problems. But surely the application wouldn't be designed like that - what's the point of exporting data that can't then be accessed?
John
October 2, 2013 at 2:31 am
Yeah unfortunately it is permanently running. It's a work around that I'm trying to figure out. the third party device was supposed to send SNMP traps and I would have monitored the data (voltage) using my SNMP receiver program which allows for polling of data. but unfortunately the product doesn't actually do that even though it was advertised that way.
It does do this real time polling of data into a csv file however so I thought this could be a good work around. it updates the csv once every 30 seconds but you can change that to whatever you want.
October 2, 2013 at 2:48 am
Permanently running, but does it actually hold a lock on the file permanently? That's what you need to check. If you double-click on the file and it opens, you should also be able to import data from it.
John
October 2, 2013 at 7:27 pm
Yeah I can open it. and I could copy and do a special past into another spreadsheet so presumably that means that it's not locked. It is read only though.
I might try to do the SSIS process. Hopefully it won't take too long!
October 3, 2013 at 8:07 am
If using xp_cmdshell won't get you yelled at, you could have a stored procedure copy the file to a processing directory so it wouldn't be locked by the program. Just have to mind permissions issues.
October 3, 2013 at 8:10 am
Yes, good idea. Still susceptible to locks, but at least the file's safe once it's done. You can actually do it as part of your SSIS package without using xp_cmdshell.
John
October 7, 2013 at 12:16 am
I've actually been playing around with SSIS a bit and have made some headway I think.
What I've done is I've just made a package that copies the csv file and then I read that copied csv file and import it to my database. It was pretty simple to do in the end but there are still a couple of issues that I need to resolve:
-- When the updated file gets imported into the DB the old rows don't get replaced they all remain in the DB
-- I'm not sure how to make it to re-compile the SSIS package every so often (every few hours) to update the data. I was thinking maybe a loop?
Thanks again for your help and if you can help me with this issue!
October 7, 2013 at 11:31 am
You may need to write a process that does a merge instead of insert if you are importing old info. Especially if the older csv rows can get updated. Another option would be the old truncate and re-insert but that isn't the best either, the only other option I can think of is to write a process that compares against the old file and only takes the new entries and then you import that file.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply