Excel, OLEDB and uppercase problem.

[witek]

Hi.

There are some data on excel sheet which I try to read as database

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="
& ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";Extended
Properties=Excel 8.0;"


Some values are stored in different ways
as

UpperCase
UPPERCASE
or
uppercase

and sql statement like
select * from [DB$] where Field = "UpperCase"

uses binary comparison to return rows.


Is there any way to switch it to text comparison to have all rows
regardless how it was written on sheet

Any parameter to connection string

select * from [DB$] where UPPER(Field) = "UPPERCASE" does not work.

Thanks

[witek]

witek wrote:
> Hi.
>
> There are some data on excel sheet which I try to read as database
>
> ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="
> & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";Extended
> Properties=Excel 8.0;"
>
>
> Some values are stored in different ways
> as
>
> UpperCase
> UPPERCASE
> or
> uppercase
>
> and sql statement like
> select * from [DB$] where Field = "UpperCase"
>
> uses binary comparison to return rows.
>
>
> Is there any way to switch it to text comparison to have all rows
> regardless how it was written on sheet
>
> Any parameter to connection string
>
> select * from [DB$] where UPPER(Field) = "UPPERCASE" does not work.
>
> Thanks
>
Ok, my mistake
Ucase not upper
but anyway

is there any way to change comparison from case sensitive to case
nonsensitive ?

[AA2e72E]

"witek" wrote:
> select * from [DB$] where UPPER(Field) = "UPPERCASE" does not work.

Try:

SELECT * FROM[DB$] WHERE UCASE(Field) = 'UPPERCASE';

Use single quotes from strings and UCASE instead of UPPER.

[witek]

AA2e72E wrote:
> "witek" wrote:
>
>>select * from [DB$] where UPPER(Field) = "UPPERCASE" does not work.
>
>
> Try:
>
> SELECT * FROM[DB$] WHERE UCASE(Field) = 'UPPERCASE';
>
> Use single quotes from strings and UCASE instead of UPPER.

Thanks.
it works.
I use single quotation. It was my mistake writing post.

There is still a question if there is a way to change compare method to
case nonsenstive instead of unsing upcase function.

[AA2e72E]

"witek" wrote:
> There is still a question if there is a way to change compare method to case nonsenstive instead of unsing upcase function.

As far as I know, the JET 4.0 provider is case insensitive (not case
sensitive as you imply) and there is no way to specify "Option Compare Text"
or "Option Compare Binary". This option exists in Oracle (the default is case
sensitive) and SQL Server (the default is case insensitive).

[witek]

AA2e72E wrote:
>
> As far as I know, the JET 4.0 provider is case insensitive (not case
> sensitive as you imply)

I am working with Excel and
...where Fields = 'Upper'
returns different rows than
.... where Fields = 'upper'

Maybe it depends on Excel, not on Jet 4.0.
However I don't know how to change it.


Upcase function does what I need.
I will see later how fast it is.

Thanks