Solved

Delphi/Excel field titles

Posted on 2007-11-18
18
738 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
  • 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now