January 12, 2009 at 11:51 pm
Hello All,
I am trying to copy excel data which is getting updated continuosly to a SQL server table.
The excel data is getting changed every second( With the available s/w)
I need to take this data to sql server table every 15 seconds.
I am able to do it with the dataset but in between while running the application (which convert online excel to sql server table) I get the following errors.
The Provider Could not determine the string value. For Example, the row was just created, the default for the string column was not available and the consumer had not yet set a new string value.
The Connection for viewing your linked Microsoft Excel WorkSheet was lost.
These errors are not coming continuously those are coming in between while running the application
Please help me with this....
Plz let me know if there are any readily available softwares which can convert the excel data to SQL server every 15 seconds.
Any help will be greatly appreciated.
Thanks.
January 15, 2009 at 9:12 am
Hate to say it, you've got the wrong tool (Excel) for that job.
It's not designed for concurrent access like that. It has no support for transactions or isolation. It's almost like using a text for this. I strongly doubt you can make this work unless you can pause the update of the worksheet when you are loading it.
This is one case where Access would be a better tool for the job 🙂 . You need a DB, and Excel is not a db
January 20, 2009 at 12:15 am
thx for reply, but I have to use the Excel sheet bcoz the realtime data is sent only to the excel sheet and not directly to any database like access.
i have to save this data from excel to sql server database table every 15 seconds.
Is this error de to the load on the machine or like that?
Is there any solution plz plz help...
January 20, 2009 at 12:40 am
geetanjaliks (1/12/2009)
Hello All,I am trying to copy excel data which is getting updated continuosly to a SQL server table....
Why don't you go for SSIS?
Abhijit - http://abhijitmore.wordpress.com
January 20, 2009 at 11:56 am
geetanjaliks (1/20/2009)
thx for reply, but I have to use the Excel sheet bcoz the realtime data is sent only to the excel sheet and not directly to any database like access.i have to save this data from excel to sql server database table every 15 seconds.
Is this error de to the load on the machine or like that?
Is there any solution plz plz help...
That's what I'm trying to say. If:
1) the spreadsheet is constantly being updated
2) you need to pull data from the spreadsheet
3) you may not lock the spreadsheet
you will get errors in you data. No way around it. Need to rethink this. Some options:
1) Periodically switch spreadsheets. Create excel file 1, at some reasonable interval (thinking 15 minutes to hour) have the feed create a new file, start updating that, import file 1.
2) change the intermediate step to something else. Like a database
3) Modify the feed to add something like a checksum, read data, calc checksum, re-read any rows that fail.
As stated, you will not make this work with any reliability. You will get bad data, have file locking issues, and likely at some point file corruption.
I understand you may have some limitations and constraints, but this will be fragile at best.
If you got a nail, use a hammer. If you don't have a hammer, a rock might work. You are trying to use a crystal vase.
January 20, 2009 at 12:12 pm
You don't say but how is the sheet getting updated?
Is it a single user or an application feeding the sheet?
January 20, 2009 at 12:16 pm
You'll get that error if some of the data in the spreadsheet is in the process of being updated. If it's "continually being updated", then you're going to run into that error sometimes, and there's no way around it except to stop using Excel for something it's not built for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2009 at 2:32 am
I used to have nearly similar problem. My friend coded some VBS and it worked:
While your spreadsheet is under update, it has a "deny flag" raised, VBS study if flag is raised or not ... if not then it copies file to another folder where SSIS reads it into db. Just schedule VBS and SSIS to match.
I'm not sure if terms are correct and I dont have anymore that VBS code (code was approx 30 line so it cant be very complex).
Cheers
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply