+ Reply to Thread
Results 1 to 5 of 5

Excel's ODBC driver's field limit

  1. #1
    Jo
    Guest

    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.


  2. #2
    Scot T Brennecke
    Guest

    Re: Excel's ODBC driver's field limit

    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" <JoJoTwilligo@hotmail.com> wrote in message
    news:1123531155.072657.190360@g47g2000cwa.googlegroups.com...
    > 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.
    >




  3. #3
    Jo
    Guest

    Re: Excel's ODBC driver's field limit


    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" <JoJoTwilligo@hotmail.com> wrote in message
    > news:1123531155.072657.190360@g47g2000cwa.googlegroups.com...
    > > 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.
    > >



  4. #4
    Harlan Grove
    Guest

    Re: Excel's ODBC driver's field limit

    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?


  5. #5
    Jo
    Guest

    Re: Excel's ODBC driver's field limit


    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1