Remove 255 character limit from OLE DB .NET and Fix Data Corruptio

[cfrphoto]

Make it possible to query any data from an Excel spreadsheet in exactly the
form it appears in the spreadsheet. The highest priority is to remove the
255 character limit from the size of fields being accessed in a query. I
suppose there may be some way to do this from SQLServer using a character
array, but if so, it is undocumented and probably not very developer
friendly. It is also necessary to remove the "feature" that makes
non-conforming numeric or text data disappear. The query should return all
text visible to the user in every row and column queried. The present OLE
DB interface causes data to disappear or even worse, appear in the wrong row.
This is a computational disaster and potentially a security problem.
Also, in the mean time, adding extra quote characters to tab delimited data
when saving as a text file further corrupts the data.

 

[cfrphoto]

I have no control over how the spreadsheet is created. The bug relating to
whether a field is text or numeric is the worst problem because changing the
cells to text does not have affect pre-existing data. This means that even
if I am able to modify the Excel spreadsheet, "TypeGuessRows" will fail.

Repeating, to keep it simple, I just want to be able to query the text in an
Excel spread sheet. In many cases, trying to maintian data types is
problematic. This comment applies to SQLServer, Access or any other
database when extracting data into a data warehouse. The safest approach is
to treat all data as text unless the data is extracted from a controlled
source where the data type in a column is known.

I do not agree with the comment about using "memo" data. This seems to be
an artifact of Access and older versions of SQLServer where the varchar limit
was 255. The varchar size limit in SQLServer is 4000 for Unicode and 8000
for single byte characters. Unfortunately the Visual Studio documentation
is rather weak in providing an functional overview before launching into some
example that avoids interesting or useful cases. Links to classes
referenced as properties are not always provided.

I suppose I could change the query parameters if they were documented. So
far, I have not found enough useful (high level) documentation. I have
tried various combinations of IMEX parameters, but there was no indication in
the documentation that Memo existed or had to be used for strings greater
that 255 characters. In that case, an exception should have been thrown.
What actually happened is that the string returned was from the a different
row. THIS IS A BUG.

Exporting to a .txt file defeats the purpose of having an automated
extraction procedure. Worse, Excel adds quotes to strings containing quotes
even in tab delimited mode. One of the Excel spreadsheets created using
"Paste Special" from an Access Database contains embedded new line
characters. Of course, the .txt file was unuseable. I was able to get
around this problem only by replacing all instances of <ctrl>J with a string
like \n that could be restored later.

Again, I would like to be able to query in a "type free" manner returning
every cell in the Excel spreadsheet as a string. Exporting to a file is not
really an object.