Registry changes to help with Excel Imports

Hi,
There are a lot of reasons for this, books full of them actually.

Back in the late 80's/early 90's Microsoft got a few of us to use pre-Excel as a data source.
This was a dark time technology wise, we had been using Lotus Symphony and boy was that painful.

Yes believe it or not Excel was first a data source.
Scary right?

Then Access, and of course bad boy SQL Server 7 would step up soon but mess things up for a bit.

By the time SQL Server 2000 came out Excel and Access had reputations, file sharing reputations.
People and businesses were using them to share/distribute sensitive information and they were constantly under attack.

OK, what's all that mean?
Well, the header info in both Excel and Access evolved to what they are during that growth.
For years developers have depended on a trick where you can rename an Access DB and the JET engine can still use it.
That's because of the internal header.

If you check you'll find the method you posted the link to can lead to a vulnerability,
The very same vulnerability that Excel and Access had for years.
If either are saved on-line the remote user can attempt amalicious attack, especially with Excel.

I won't go into that but just think about it.
Salute,
Mark

 

The JET database engine is notorious and well documented as being 'quirky' (to use the polite term). One of its more 'interesting' features is that to determine datatype, it scans the first few rows, and based on what it finds, decides that is the datatype. If there is nothing in those 8 rows, it figures that it is just empty or something and procedes to ignore the rest of the excel file.

If you want more info, this blog has a pretty good writeup on it.
http://blog.lab49.com/archives/196

I wrote up a slightly more in depth, and very polite support ticket asking if could change the default from 8 to the max (16384) for me, by setting a registry key specific to this issue, and got this back

That looks suspiciously like a form letter, so I figured I would ask here instead, as there are no alternate methods (even with that setting, it is hard to believe someone could write a database engine that works in such a moronic fashion), and at least from my point of view this is a pretty reasonable request.

Anyone who works with excel files (probably access too) is going to want this, and I can't see it adversely affecting anyone else. I would be quite amazed to find anyone who actually wants the 8 row default.

 

Well, a vulnerability is definately a good reason not to do it, but it is not like we are not able to use the JET ADO provider currently or anything. I have a line of business app for a company that was previoiusly using excel to store about 50k records worth of data. In their administrative interface, I am giving them the option to upload an excel file, and import the records into sql server. The problem I am running into is that for them, Zip code is an optional field. If the first 8 records do not use that field, excel decides to just ignore that column. I told them to try and make sure that the first record has all the fields filled in, but it is kind of irritating to have to add that step for no real reason.

I would even be happy if that registry key went from 8 to 200 or so, as there is a much better chance that optional fields will be hit in that time.

 

In all honesty, for security reasons, a shared host should not allow that.
Yes you have a good reason for it but we all suffer from the 1% factor.

1% factor = The miserable amount of people who are always looking to create trouble.