Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
M.E.
Aged Yak Warrior
539 Posts |
 Posted - 2002-11-06 : 13:11:59
|
| This has happened to me several times and I've seen posts on this forum about it in the past. It seems to be a bug with importing from excel.All of our reference tables follows the exact same formatReference value, Description, legacy_code (all varchars of various length)Our spread sheet would contain values likegas, gas well, 01s gas, sour gas well, 02oil, oil well, 03this goes on for about 100 columns or so. A simple import should populate this table.. right? well.. not quite. For some unknown reason, some legacy codes are reported as nulls. Our table would begas, gas well, 01s gas, sour gas well, nulloil, oil well, nullWhy these are reported as nulls, I got no clue. It seems to be in groups (all legacy codes > 15 are null.. or all ones <10 are null... it seems to be random). The only solution we've found is to drop the spreadsheet and completely recreate it. We've tried altering the column data types in excel, but nothing seems to work (other than a complete recreate of the excel spreadsheet). Any ideas on this one would be helpful... it's got us quite well baffledMark-----------------------SQL isn't just a hobby, It's an addiction |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-11-06 : 13:53:11
|
| Is this relavent at all ? Maybe not but try the 2nd part[url]http://www.sqldts.com/default.aspx?6,222,254,0,1[/url]HTHJasper Smith |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-06 : 15:49:17
|
| Thankyou for you contribution jasperI've seen this article before and it does have some relevance. However in our situations, we have a table written asValue, legacy codevalue,01value,02value,03value,04value,05value,06value,07value,08value,09value,10value,11value,12value,13value,14value,15value,16value,17value,18value,19value,20value,21All the values will load correctly. All legacy codes load correctly except 15-21 which are recorded as nulls. If it was just the first 8 that didn't load or all the ones after....I will look a little further into it with option 2... We'll see what I can get from it. What I don't understand is why recreating the spreadsheet seems to get rid of the problem.-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 16:35:15
|
| I have had very subtle and obscure bugs come up in Excel whenever a worksheet is changed. Some settings are made at the time the worksheet is created and first saved, like the print range, that sometimes reset to another setting. The same applies for named ranges, or any range that was used in a formula and then updated afterwards. Actually, the best example is to use the Goto feature and choose Last Cell. This never changes no matter how much data you remove; it always goes to the last cell that ever contained a value on the worksheet, even if you completely clear it. Likewise, if you only had 15 rows originally, that might've been saved somewhere in the worksheet and DTS is using it instead of scanning the sheet.Basically, when that happens the worksheet is "corrupted" and you'll have to create new sheet to fix it. You *might* be able to fix it by using a named range for the data:http://www.sqldts.com/default.aspx?6,101,243,0,0You can write an Excel macro that redefines that named range, so if people edit the workbook it can run automatically and set the range to encompass all of the appropriate data. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-06 : 18:39:15
|
| Hmmm, interesting. Actually, all the cases of this happening occoured on a spreadsheet that had several revisions done on it. This would also explain why deleting it and reentering it in a new spreadsheet would correct the problem. I'll have to go through all the other ones that have had several revisions made to it and see if they are experiencing simular problemsGeez thats an obscure one ain't it?-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
|
|
|
|
|
DTS Importing Excel spreadsheets - Warning/discuss