Im using a SQL SELECT statement to export data from an ADO DataSet to an Excel database. I've accomplished this, but with a flaw. This SQL statement is part of the exporting feature of a program, so a SQL statement revision is needed.
The flaw is that the Field Names (not captions, so I get ExprXXXX for some of them) from the ADO dataset are inserted as the first row of the Excel sheet/table, with the actual data following.
I've played with the select statement and the extended property HDR=YES/NO with no luck.
How do I select the data into the Excel sheet without these annoying column names as the first row?

here is one of the SQL strings I tried:

"SELECT DATATYPE43, DATATYPE44, format(DATATYPE6,'mm/dd/yy'), format(DATATYPE40,'0'), format(DATATYPE41,'0'), format(DATATYPE60,'$###,##0.00'), format(DATATYPE63,'$###,##0.00'), format(DATATYPE64,'$###,##0.00'), format(DATATYPE54,'$###,##0.00') INTO [Excel 8.0; HDR=YES; DATABASE=excell test.xls].[TestSheet5]\r\nFROM DataPumpEntryTable;\r\n"

I played with HDR being yes and no...and removing the format()'s ...and in this example still have the column names removed, they were SELECT DATATYPE43 AS [Machine Name], etc.
For the columns with the format() I get the ExprXXXX. So the first row looks like this, with the data following starting on row 2:

DATATYPE43,,DATATYPE44,Expr1002,Expr1003,Expr1004, Expr1005,Expr1006,Expr1007,Expr1008

Hi SC,

If I understand correctly, the problem is not in Excel. So asking in the Excel section is asking in the wrong place?

Maybe not what you want, but to remove the first row in Excel suitable code would be
What happens when you tried
"SELECT DATATYPE43, DATATYPE44, format(DATATYPE6,'mm/dd/yy') AS [New Field Name], ..." ?

Or start with something really simple and see what happens? "SELECT *" So long as Excel's 256 column limit is not exceeded.

I am familiar with Excel but not whatever software you are using. I guess it is MS Access - maybe you can ask in the Access section of the forum?

Another idea, and it also might not be helpful, what about doing an import from Excel? Maybe Excel can do the query straight from the data source. Via menu Data, Import External Data, New Data Query or use ADO in VBA.

HTH,
Fazza

This manipulation is done in neither MS Access nor MS Excel. Its done in a program Im developing through a ADO Command object with the Microsoft.Jet.OLEDB.4.0 provider.
When I did SELECT DATATYPE54 AS [Machine Name] ... and specified names for each column, the first data row outputted to the Excel DB was those names, instead of the Field Names...
so I got for row 1: Machine Name, etc..
I tried SELECT * causing the same affect for all the fields, allthough I dont want every row.
Thinking about calling the column headers being the first row of data, a 'feature'

And that is exactly what I'd expect. That is what the "AS" does. Also it matches the standard treatment within Excel for data lists (the Excel equivalent of a database data table).

It sounds like everything is working perfectly, though I appreciate this is not how you originally intended.

If you really want no headers, I think there are two options. Viz, from where the export is made, or by deleting the header row once it is in Excel. With headers standard Excel treatment no-one should have too big a problem with the 'feature' though. Well done.

regards,
Fazza