Case sensitivity with ADO.NET and Excel

[SteveHarris]

Hi
I'm running a 'LIKE' query against an Excel worksheet using ADO.NET and its proving to be case sensitive. I really want it to be case insensitive (which it should be according to all the info I can find) - does anyone have any ideas how I can achieve that?

My connect string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFile.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"

My query is: SELECT * FROM [Sheet1$] WHERE Chassis LIKE '%Foo%'

This does match Chassis entries of 'Foo', but not 'foo', 'FOO', etc.

Thanks in advance

Steve H

[dglienna]

Seems like something is wrong. Read this link

[SteveHarris]

Thanks for the link. The content seems to focus purely on SQL Server and my problem is with Excel. I did try adding a COLLATE Latin1_General_CI_AS clause to the WHERE condition but it fails with an unhelpful error (E_Failxxxx).

I'm not using Excel out of choice - its what the customer requires due to their operational setup and hosting situation. If I could move the data to a proper DB then I'd be fine, but I don't have that luxury in this case.

Any other ideas anyone?

many thanks,

Steve H

[Alsvha]

I don't think you have other choice then to compare them case insensitive, meaning convert both to upper case when running your queries.

Something like:
SELECT * FROM [Sheet1$] WHERE UPPER(Chassis) LIKE '%FOO%'

Note, I'm not sure if UPPER works in the SQL to Excel like that, but it would work in other flavors of SQL, but I'm sure a similar function would exists

[SteveHarris]

I didn't really want to do that as it will hit performance. I'm not sure how the Jet OLE DB driver deals with Excel files but I guess it doesn't really have that much opportunity to optimise operations like this.

If I can't find any other solution then I will fall back to this approach, but hoping that someone can come up with an alternative!

thanks,

Steve H

[Alsvha]

It will indeed be a performance hit, yes.

Perhaps there's an alternative method of doing this - how often will you read from the excel file? And how's the data used?

Because if the Excel file is manageable in size, there could perhaps be some merit in just pulling all the (potentially needed) data into your code layer and filter it there?
As your code layer will be better optimized at running such string manipulation/comparisons?

[SteveHarris]

Update: This is wierd, but the problem disappeared when the application was deployed from a dev system onto a staging server. Haven't tried the live box yet, but it would seem that case sensitivity is determined by the server build rather than any application settings.

Thanks for the suggestions that were made, but in light of the discovery that its a system-specific issue I will consider this thread closed.

Many thanks, Steve H

[dglienna]

That's what my link said.

[SteveHarris]

Hi - I'm not trying to be picky or ungracious because I really do value all the help I can get, but your link related to SQL Server 2000 configuration rather than the OS-level build issues which I now think are at the heart of my problems. I did try the 'collation' clauses suggested in the article but they gave syntax errors with the OLE DB driver.

The application I'm developing doesn't use SQL Server at all - in fact the live server won't even have it installed. It would be 10 times easier to code this whole piece if there was a decent DB to work with, but the customer has mandated that the data be held in Excel files on a server with no formal RDBMS installed.

Thanks for all suggestions though!

regards

Steve H

[robertocm]

Hi
I'm running a 'LIKE' query against an Excel worksheet using ADO.NET and its proving to be case sensitive. I really want it to be case insensitive (which it should be according to all the info I can find) - does anyone have any ideas how I can achieve that?

Dear all,
Sorry for posting in this 'classic' thread but i'm feeling the need to show my gratitude for the ideas commented above sharing this simple solution that have worked for me in a similar situation.
In my case i've found the issue solved just changing the connection string from this:

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name & ";Extended Properties=Excel 12.0 Xml;"

To this:
cnn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

Many Thanks

[robertocm]

Dear All,

Sorry but, testing in another computer, with windows7, an the same problem reappear,

The ok test in previous post was done in a laptop with XP.