Range with odd column names causing problems
 

Hi,
I'm having to pull data from some other worksheets, and am using an ADO
Connection, and select statement to get the details from the ranges.
The problem is that some of the columns have odd column names, which are
causing problems.
One has columns such as "01/04/2005", 01/05/2005" etc. The sql here doesn't
bring back the columns but the result of the math calculation of 1 divided by
4 divided by 2005. Does anyone have any ideas on this? I've tried placing
square brackets round the column names to no avail.
The other sheet has column names with spaces in, such as 'Opening Balance'.
Again I'm having problems with these.

If anyone has any ideas/workarounds for the above (I can't change the column
names as they are not under my control) it would be most appreciated.

Many thanks in advance.
Andy

Range with odd column names causing problems
 

possibly format the column names to appear as

01_Apr_2005

dd_mmm_yyyy

--
Regards,
Tom Ogilvy

"Andy" wrote in message
...
Hi,
I'm having to pull data from some other worksheets, and am using an ADO
Connection, and select statement to get the details from the ranges.
The problem is that some of the columns have odd column names, which are
causing problems.
One has columns such as "01/04/2005", 01/05/2005" etc. The sql here
doesn't
bring back the columns but the result of the math calculation of 1 divided
by
4 divided by 2005. Does anyone have any ideas on this? I've tried placing
square brackets round the column names to no avail.
The other sheet has column names with spaces in, such as 'Opening
Balance'.
Again I'm having problems with these.

If anyone has any ideas/workarounds for the above (I can't change the
column
names as they are not under my control) it would be most appreciated.

Many thanks in advance.
Andy

Range with odd column names causing problems
 

Tom,
Thanks for the reply.
Unfortunately I'm not able to format the column headers as they are not
under our control. I'm not sure what 'worst practice' book advised to use
dates as column headers, but unfortunately I'm stuck with them.

I've since found that I can use 'select * from forecastcosts' (where
forecastcosts is the range name) but this will leave things exposed if the
possition of any of the columns changes in the future. I'd like to reference
the column names somehow if I can.

Many thanks again for any help.
Andy

"Tom Ogilvy" wrote:

possibly format the column names to appear as

01_Apr_2005

dd_mmm_yyyy

--
Regards,
Tom Ogilvy

"Andy" wrote in message
...
Hi,
I'm having to pull data from some other worksheets, and am using an ADO
Connection, and select statement to get the details from the ranges.
The problem is that some of the columns have odd column names, which are
causing problems.
One has columns such as "01/04/2005", 01/05/2005" etc. The sql here
doesn't
bring back the columns but the result of the math calculation of 1 divided
by
4 divided by 2005. Does anyone have any ideas on this? I've tried placing
square brackets round the column names to no avail.
The other sheet has column names with spaces in, such as 'Opening
Balance'.
Again I'm having problems with these.

If anyone has any ideas/workarounds for the above (I can't change the
column
names as they are not under my control) it would be most appreciated.

Many thanks in advance.
Andy

Range with odd column names causing problems
 

"Andy" wrote ...

I'm not able to format the column headers as they are not
under our control. I'm not sure what 'worst practice' book advised to use
dates as column headers, but unfortunately I'm stuck with them.

I've since found that I can use 'select * from forecastcosts' (where
forecastcosts is the range name) but this will leave things exposed if the
possition of any of the columns changes in the future. I'd like to reference
the column names somehow if I can.

I can't reproduce. Using the column name as posted, this works for me:

SELECT [01/04/2005] AS sensible_name
FROM [ColumnHeadersAreDates$];

(note I already had this sheet in my test database <g).

If you are still having problems, another approach might be use HDR=NO
in your connection string. From the query's point of view, the new
column names would become F1, F2, F3 etc according to ordinal position
and your 'old' column names dates would become the first row of data.
You could use their known values to eliminate this row e.g.

SELECT F4 AS sensible_name FROM [ColumnHeadersAreDates$] WHERE
F4 < '01/04/2005';

The above relies on a couple of things:

Point number one, I've assumed the column is 'seen' as text. Because
the column header is now part of data set, it may influence the
columns data type if it is not already DATETIME. If the data type for
the column was determined to be numeric, the WHERE clause becomes F4
< 38443 (but what if that value is legitimate elsewhere in the
column..?) So this approach could have an affect on the column and
even knock out some values by turning them null. For details on the
processes involved, see:

http://www.dicks-blog.com/excel/2004...al_data_m.html

You could simply avoid querying the header row. My column headers are
in the range A1:E1, so I could simply set HDR=NO and use:

SELECT F4 AS sensible_name FROM
[ColumnHeadersAreDates$A2:E65536];

BTW I don't get all 65535 rows, just those with data i.e. the
intersection between the specified range and the UsedRange. However...

Point two: I've know the ordinal position of my column but you said
you wanted to allow for the ordinal positions changing (well done for
being wary of SELECT * in production code, many just don't get this).

You can find out the column names and their ordinal positions using
the OpenSchema method of the ADO Connection object e.g. code snippet:

Set rsTables = Con.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "Table"))

Do While Not rsTables.EOF

Set rsColumns = Con.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, rsTables!TABLE_NAME, Empty))

Do While Not rsColumns.EOF

Debug.Print rsColumns!COLUMN_NAME, _
rsColumns!ORDINAL_POSITION

(IIRC you can also the ADOX objects but they just call OpenSchema
under the hood anyhow <g.)

Using OpenSchema you can always be sure what 'F' number to use for
your column.

Of course, if you are using the range address to knock avoid the old
header row, you need to know the address of the first cell/header in
the table which is tricky to do with ADO...

Jamie.

--