Solved

Delphi/Excel field titles

Posted on 2007-11-18
18
781 Views
Last Modified: 2013-11-23
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".
0
Comment
Question by:nicholasjfox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 5
18 Comments
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20308048
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.
0
 

Author Comment

by:nicholasjfox
ID: 20308173
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
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20308203
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

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

Author Comment

by:nicholasjfox
ID: 20308695
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 ...
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20308760
try

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

Author Comment

by:nicholasjfox
ID: 20308812
imitchie,

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


0
 
LVL 25

Expert Comment

by:imitchie
ID: 20308853
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
0
 

Author Comment

by:nicholasjfox
ID: 20308881
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.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20308969
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
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20308984
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)');
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20309101
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?
0
 

Author Comment

by:nicholasjfox
ID: 20310824
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.
0
 

Author Comment

by:nicholasjfox
ID: 20310848
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


0
 
LVL 25

Expert Comment

by:imitchie
ID: 20310872
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?
0
 

Author Comment

by:nicholasjfox
ID: 20311112
Imitchie, I was using "test results" just as an example.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 50 total points
ID: 20311355
hmm.... this worked for me

select * from [Sheet1$] order by [Order tot]

where my sheet is (slashes added for clarity)
my test/col umn1/order tot
1      3      23
2      5      2
3      23      23
4      1      111

the connection string i used is (yes i created an odbc link)
    ConnectionString :=
      'Provider=MSDASQL.1;Persist Security Info=False;Extended Properti' +
      'es="DSN=Excel Files;DBQ=C:\b1.xls;DefaultDir=C:\;DriverId=790;Ma' +
      'xBufferSize=2048;PageTimeout=5;";Initial Catalog=C:\b1';
0
 

Author Comment

by:nicholasjfox
ID: 20311672
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

742 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question