How did you create your Excel column? If your data is being curtailed
at 255 characters, you probably defined it as VARCHAR(255). You require
MEMO. Read up on Jet's data types for Excel. Get you schema design
correct.
If you have 'non-conforming numeric or text data' appearing as null
values, then you have either defined your table's column incorrectly or
have values that don't fit the data type of the column, probably the
latter. Get you data into the correct columns.
If the design and/or data is not your own and you are trying to work
dynamically with what you're given, you registry values (e.g. what are
you using for TypeGuessRows?) and/or connection string properties (e.g.
which values for IMEX have you chosen?) may be wrong. For some further
hints see:
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
Have you tried querying a text file using a schema.ini file? If you
prefer this system, I suggest you base any future petitions on this
functionality.
Best of luck with your campaign,
Jamie.
--
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.
The documentation is poor and you are sore. Hoping for a response or
action from Microsoft can only lead to more hurt. However, if you have
a specific issue you want to resolve or work around, you may have some
luck here.
> 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.
It's a common misconception that MS Access is the same as Jet. MS
Access *uses* Jet. Excel also uses Jet. Jet may be officially
'depreciated' by Microsoft but it is still the native SQL engine for
both Access2003 and Excel2003.
Tracking down the *fact* that Excel uses the memo data type for a text
column/value that exceeds 255 characters is a case of 'join the dots'
in the documentation and a bit of old-fashioned testing e.g. try this
one:
PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer
Overflow Error
http://support.microsoft.com/default.aspx?scid=kb;en-us;281517
"If any field looks like text and the length of data is more than 255
characters, the column is typed as a memo field."
> I suppose I could change the query parameters if they
> were documented.
I think all the issues here *are* documented, albeit in a succinct and
fragmented fashion e.g.
How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;en-us;257819
"IMEX=1 in the Extended Properties section of the connection string ...
enforces the ImportMixedTypes=Text registry setting"
> 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.
I agree the way Jet guesses data types for Excel is unsatisfactory but
throwing an exception when text > 255 is detected would make the
process useless.
I think you need to accept the fact that memo does exist and work with
it. Otherwise, find another way to access the data e.g. automating an
instance of the Excel.Application object, working with the Biff8
format, etc.
> What actually happened is that the string returned was from the a
> different row. THIS IS A BUG.
This sounds like a bug but we've only got your word for it. For anyone,
even Microsoft, to investigate they need some steps to reproduce the
bug. Please post yours here.
Jamie.
--