Excel's ODBC driver's field limit

I'm trying to import this Excel file using the Excel ODBC driver, but
its failing on this one file that has at least 256 columns. The error
message is "{ODBC Excel Driver] Too many fields defined." The statement
I'm using to perform the query is SELECT * `<table name>` WHERE 1. Is
this a problem of * being too many columns? Is there anyway around
this, such as making the connection read-only, or forward scrolling
only, or any other restrictions that I can live with? Read-only and
forward scrolling are 2 such restrictions.

Also, how do I identify the error code returned? Right now I'm
getting -1040 as an error, but is there a more abstract way of
detecting this error? I'm developing under VC++6.0, btw, so I might not
be using the same environment as you.

I'm not aware of any work-arounds other than simply breaking up your query into two or more
(depending on how many fields there are) so that the field list is below the limit. Most database
"scientists" would say that a table with over 256 columns is probably poorly designed.

If you're using CRecordset (the MFC class), you're probably catching a CDBException, which has
m_nRetCode, m_strError, m_strStateNativeOrigin members, and the GetErrorMessage method. Please
explain what "a more abstract way" would mean to you.

"Jo" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to import this Excel file using the Excel ODBC driver, but
> its failing on this one file that has at least 256 columns. The error
> message is "{ODBC Excel Driver] Too many fields defined." The statement
> I'm using to perform the query is SELECT * `<table name>` WHERE 1. Is
> this a problem of * being too many columns? Is there anyway around
> this, such as making the connection read-only, or forward scrolling
> only, or any other restrictions that I can live with? Read-only and
> forward scrolling are 2 such restrictions.
>
> Also, how do I identify the error code returned? Right now I'm
> getting -1040 as an error, but is there a more abstract way of
> detecting this error? I'm developing under VC++6.0, btw, so I might not
> be using the same environment as you.
>

Scot T Brennecke wrote:
> I'm not aware of any work-arounds other than simply breaking up your query into two or more
> (depending on how many fields there are) so that the field list is below the limit. Most database

That's all I could think of.

> "scientists" would say that a table with over 256 columns is probably poorly designed.

Oh? Why would you say that? How would someone design a table when
they have 256 different pieces of data that they need to store for each
record? Expanding the columns and cramming several pieces into one
column isn't much of a solution. You can break them up into several
tables, but what's the point?
But this is accademic anyway, since I'm not reading from a pure
database, but an Excel file, which seems to be prepared to have more
than just a few 100 columns.

> If you're using CRecordset (the MFC class), you're probably catching a CDBException, which has
> m_nRetCode, m_strError, m_strStateNativeOrigin members, and the GetErrorMessage method. Please
> explain what "a more abstract way" would mean to you.

More abstract would be like a macro, or something more portable
than just a number. Anyway, I'm not using CRecordset but SQL*() calls
(like SQLColumns() and such) and I've learned how to find out the
number of columns w/out having to do a SELECT query on the table.

>
> "Jo" <[email protected]> wrote in message
> news:[email protected]...
> > I'm trying to import this Excel file using the Excel ODBC driver, but
> > its failing on this one file that has at least 256 columns. The error
> > message is "{ODBC Excel Driver] Too many fields defined." The statement
> > I'm using to perform the query is SELECT * `<table name>` WHERE 1. Is
> > this a problem of * being too many columns? Is there anyway around
> > this, such as making the connection read-only, or forward scrolling
> > only, or any other restrictions that I can live with? Read-only and
> > forward scrolling are 2 such restrictions.
> >
> > Also, how do I identify the error code returned? Right now I'm
> > getting -1040 as an error, but is there a more abstract way of
> > detecting this error? I'm developing under VC++6.0, btw, so I might not
> > be using the same environment as you.
> >

Jo wrote...
>Scot T Brennecke wrote:
....
>>"scientists" would say that a table with over 256 columns is probably poorly designed.
>
> Oh? Why would you say that? How would someone design a table when
>they have 256 different pieces of data that they need to store for each
>record? Expanding the columns and cramming several pieces into one
>column isn't much of a solution. You can break them up into several
>tables, but what's the point?

Doesn't need to be broken into several tables. Any data that could be
put in tabular format could be represented in a table with only 4
fields: new key field, old key field, identifying field, value field.
For example,

ID LName FName DOB
01 Bloggs Joseph 05-Jan-1950
02 Smith John 22-Jun-1973
03 Doe Jane 01-Apr-1985

could be transformed into

Key ID Desc Value
0001 01 LName Bloggs
0002 01 FName Joseph
0003 01 DOB 05-Jan-1950
:
0007 03 LName Doe
0008 03 FName Jane
0009 03 DOB 01-Apr-1985

For that matter, the new key field could be scrapped if the ID and Desc
fields became a composite key. This isn't as efficient a design as the
first table, but it should demonstrate that de minimus table structure
involves fields needed to identify entities, fields needed to identify
different properties for each entity, with such property identifiers
not being duplicated for any entity individually but allowing any
entity to have any property, and finally the values for those
properties.

In tables containing hundreds of fields it's likely many of the fields
represent the same information for different categories, e.g., separate
fields for units sold, revenues and costs of goods sold for each
quarter within a fiscal year. That sort of table should have a fiscal
quarter field and only one field each for units sold, revenues and cost
of goods sold. Few practical data entities have hundreds of different
properties that are common to all such entities.

> But this is accademic anyway, since I'm not reading from a pure
>database, but an Excel file, which seems to be prepared to have more
>than just a few 100 columns.
....

Proving the point. Excel tables are usually 'flat files', and flat
files are seldom normalized.

Excel worksheets can have only 256 columns. Are you querying an entire
worksheet?

Harlan Grove wrote:
> Jo wrote...
> >Scot T Brennecke wrote:
> ...
> >>"scientists" would say that a table with over 256 columns is probably poorly designed.
> >
> > Oh? Why would you say that? How would someone design a table when
> >they have 256 different pieces of data that they need to store for each
> >record? Expanding the columns and cramming several pieces into one
> >column isn't much of a solution. You can break them up into several
> >tables, but what's the point?
>
> Doesn't need to be broken into several tables. Any data that could be
> put in tabular format could be represented in a table with only 4
> fields: new key field, old key field, identifying field, value field.
> For example,
>
> ID LName FName DOB
> 01 Bloggs Joseph 05-Jan-1950
> 02 Smith John 22-Jun-1973
> 03 Doe Jane 01-Apr-1985
>
> could be transformed into
>
> Key ID Desc Value
> 0001 01 LName Bloggs
> 0002 01 FName Joseph
> 0003 01 DOB 05-Jan-1950
> :
> 0007 03 LName Doe
> 0008 03 FName Jane
> 0009 03 DOB 01-Apr-1985
>
> For that matter, the new key field could be scrapped if the ID and Desc
> fields became a composite key. This isn't as efficient a design as the
> first table, but it should demonstrate that de minimus table structure
> involves fields needed to identify entities, fields needed to identify
> different properties for each entity, with such property identifiers
> not being duplicated for any entity individually but allowing any
> entity to have any property, and finally the values for those
> properties.

Looks like a nightmare to program for. My instinct would have been
to go w/multiple tables. Is this what database guys do when they need
that many columns, or are giant table definitions acceptable?

> In tables containing hundreds of fields it's likely many of the fields
> represent the same information for different categories, e.g., separate
> fields for units sold, revenues and costs of goods sold for each
> quarter within a fiscal year. That sort of table should have a fiscal
> quarter field and only one field each for units sold, revenues and cost
> of goods sold. Few practical data entities have hundreds of different
> properties that are common to all such entities.

If you say so. I don't have control over the data at issue, so I'm
just preparing for the worst.

>
> > But this is accademic anyway, since I'm not reading from a pure
> >database, but an Excel file, which seems to be prepared to have more
> >than just a few 100 columns.
> ...
>
> Proving the point. Excel tables are usually 'flat files', and flat
> files are seldom normalized.
>
> Excel worksheets can have only 256 columns. Are you querying an entire
> worksheet?

Is that so? Well, the file I was working with had 256 columns. Yes,
it was 1 worksheet. Those 256 columns seemed to be what was too much
for the ODBC driver, and, from experimenting, I inferred that the ODBC
driver can only take 255 columns.