Delphi/Excel field titles

Not at my workstation to look into this properly, but I have an idea.
What is your worksheet being displayed in?
For example in a DBGrid you can modify the onColumnDraw or onCellDraw event and modify the column's title.

It is being displayed in a DBGrid but I need to change the field names so that SQL.Code is accepted.

While trying to sort a table by "Test Results" using the following code:

ADOQuerry1.Close;
ADOQuery1.SQL:.Add('SELECT *');
ADOQuery1.SQL:.Add('FROM Excel ');
ADOQuery1.SQL:.Add('Order by Test results');
AdoQuery1.Open;

Now, I've tried all types of variations with test results. {test results}, "test results" ("Test results"), ["test results"] etc etc but nothing works.

The only way the code works is if I go to the Excel workbook,.manually change the field name to "testresults" and change the code in Delphi accordingly. Then it works fine.

Changing the field names in DBGrid won't help with the code, will it?

Thanks

Okay first off, you're correct, changing this in the DBgrid won't change the results of an SQL query.
And yes you could technically program something to just run through the sourcecode of an excel XLS file and somehow remove the spaces of the column titles.
However my advice is to keep trying the SQL.
Have you tried referring to the field names as a decendant of the sheet, e.g.
Order by "Excel.Test results" (Note the term should be in inverted commas in your SQL, as in:
ADOQuery1.SQL:.Add('Order by ' + QuotedStr('Excel.Test results")

There has got to be a way of successfully doing it in SQL.
You might check the way excel's implementation of SQL refers to this sort of thing, or for those familiar with working with the ADO dataset might know more - I use other datasets

A minor correction of my line there:
ADOQuery1.SQL:.Add('Order by ' + QuotedStr('Excel.Test results')
(Not ", but ')

Nothing seems to work, I'm afraid.

Here

http://delphi.about.com/od/beginners/l/aa021202a.htm

it says

Enclose multiword names in [ ] brackets:
ADOQuery1.SQL.Text := 'SELECT [Last Name], [First Name] FROM [Address Book]';


But on this I get the error message Parameter[test results] has no default value.

Your suggestion above doesn't produce an error but it doesn't sort the table.

Perhaps I need to contact the suppliers of the data and try to persuade them to change their column titles ...

try

ADOQuery1.Close;
ADOQuery1.ParamCheck := False;
ADOQuery1.SQL:.Add('SELECT *');
ADOQuery1.SQL:.Add('FROM Excel ');
ADOQuery1.SQL:.Add('Order by [Test results]');
ADOQuery1.Open;

imitchie,

That gives the error message 'No value given for one or more parameters'

ADOQuery1.SQL:.Add('SELECT *');
ADOQuery1.SQL:.Add('FROM Excel ');
ADOQuery1.SQL:.Add('Order by (Test results)');

or

ADOQuery1.SQL:.Add('SELECT *');
ADOQuery1.SQL:.Add('FROM Excel ');
ADOQuery1.SQL:.Add('Order by F3');   // assuming the it's column F and the first row is 3

ADOQuery1.SQL:.Add('Order by (Test results)');

This produces error 'Syntax error (missing operator) in query expression'

ADOQuery1.SQL:.Add('Order by ("Test results")');

Interestingly, this produces no error but doesn't sort the table.

ADOQuery1.SQL:.Add('Order by F3');

Parameter F3 has no default value.

Hmm apparently if the table you import from excel has no fieldnames in its first row it refers to them as F1 F2 F3 etc - so one strategy may be to import your data with a blank first row, then refer to your fields that way

Have you tried:
ADOQuery1.SQL:.Add('SELECT *');
ADOQuery1.SQL:.Add('FROM Excel ');
ADOQuery1.SQL:.Add('Order by Excel.[Test results]');

or
ADOQuery1.SQL:.Add('SELECT *');
ADOQuery1.SQL:.Add('FROM Excel ');
ADOQuery1.SQL:.Add('Order by Excel.(Test results)');

here's a thought:

var
  i: integer;
  s: string;

ADOQuery1.SQL.Add('SELECT *');
ADOQuery1.SQL.Add('FROM Excel ');
ADOQuery1.Open;

s := '';
for i := 0 to ADOQuery1.FieldCount -1 do
  s := s + ADOQuery1.Fields[i].DisplayName + '/' + ADOQuery1.Fields[i].FieldName + ',';
Clipboard.AsText := s;

Can you please paste (the clipboard content) here?

RTW

' one strategy may be to import your data with a blank first row, then refer to your fields that way'


I did think of that. But by putting HDR=No in the connection string, I kept getting an error message 'Could not find installable ISAM'. I Googled this error and it looked a real monster.

IMitchie,

The clipboard:


Time/Time, Code Id/ Code Id, Name Id/Name Id, Score first/Score first, Amount first/Amount first, Score second/Score second, Amount second/Amount second,  Score third/Score third, Amount third/Amout third,  Suspended/Suspended, Total in Group/Total in Group

is "test results" supposed to be in there? i can't see it....

ADOQuery1.SQL.Add('SELECT *');
ADOQuery1.SQL.Add('FROM Excel ');
ADOQuery1.SQL.Add('Order by ("Amount first")');

just for kicks?

Imitchie, I was using "test results" just as an example.

OK imitchie, this is what hapened.
I changed my connection string to yours. Set up a test excel sheet, everything worked fine. Tried it on the file that's caused all the trouble and using ORDER By [test results] it wouldn't have it.
So I began to think in the excel files I get they don't only put spaces in the middle of the field title, they put them elsewhere, or perhaps the field names come from a query in Excel or something. Whatever, Delphi wouldn't recognise  the column names.
But then I found out you can order by field position, so I tried ('Order by 3 ASC') and it worked in conjunction with your connection string. Now whether your string made any difference I can't say and I don't intend to go back to find out - but thanks very much for your help and that of others on this thread.