Import Excel file on Sql Server
I am having a problem while I import an excel file in SQL Server 2005 using OpenRowSet, it works fine when the excel file is closed. If the excel file is open, it will throw an error message. I have an excel file that is updated 8 to 10 times per minute by third party software. I need to import this excel file into SQL Server 2005 for very 10 seconds.
Any help would be much appreciated ......... Thanks Yogi
source to share
How do you know which lines you want to import if the file is constantly updating? Do you have some sort of sequence number so you can spot the gaps?
If you really want to run something like the suggested, then why not set up a process to copy the Excel spreadsheet file periodically and then get the code OPENROWSET
to read from that copy.
source to share
I think the general principle is that the file cannot or should not be accessed when opened and written by another program.
How is the conflict resolved if part of the file is being written and read at the same time?
Having said that, I ran into a situation on one of the computers I use (not Excel, not Windows, even a PC, so this is hardly relevant) where I could upload open files via FTP.
But I am using another nearly identical computer where this is not possible. I don't know why, but it seems to me that this is a normal situation.
This idea of copying the file and working with the copy is in line with what pjp said. If you can get away from him ... The frequency of access you demand, the yogi seems to be causing a conflict between reading and writing.
source to share