nicholasjfox
asked on
Delphi/Excel field titles
I connected an AdoQuery to an ExceWorkbook. The workbooks come to me with spaces in column titles, for instance "Test Results". I need to code something in Delphi that alters "Test results" in the Excel workbook to "TestResults".
ASKER
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
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
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 ')
ADOQuery1.SQL:.Add('Order by ' + QuotedStr('Excel.Test results')
(Not ", but ')
ASKER
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 ...
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;
ADOQuery1.Close;
ADOQuery1.ParamCheck := False;
ADOQuery1.SQL:.Add('SELECT
ADOQuery1.SQL:.Add('FROM Excel ');
ADOQuery1.SQL:.Add('Order by [Test results]');
ADOQuery1.Open;
ASKER
imitchie,
That gives the error message 'No value given for one or more parameters'
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('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
ASKER
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.
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)');
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].Displa yName + '/' + ADOQuery1.Fields[i].FieldN ame + ',';
Clipboard.AsText := s;
Can you please paste (the clipboard content) here?
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].Displa
Clipboard.AsText := s;
Can you please paste (the clipboard content) here?
ASKER
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.
' 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.
ASKER
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
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?
ADOQuery1.SQL.Add('SELECT *');
ADOQuery1.SQL.Add('FROM Excel ');
ADOQuery1.SQL.Add('Order by ("Amount first")');
just for kicks?
ASKER
Imitchie, I was using "test results" just as an example.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.