Hmmm

[Michael C#]

I changed the name of one of the columns to "X" and it read the ColumnName
correctly. I guess it's assigning alphanumeric "F3, F4, etc." because the
ColumnNames are all numeric (1990, 1991, etc.) Is there any way to force it
to recognize these values as actual ColumnNames so I don't lose the
information?

Thanks

 

[Cor Ligthert]

Michael has given his own answer "HDR=True in extended properties"

 

[Paul Clement]

¤ I changed the name of one of the columns to "X" and it read the ColumnName
¤ correctly. I guess it's assigning alphanumeric "F3, F4, etc." because the
¤ ColumnNames are all numeric (1990, 1991, etc.) Is there any way to force it
¤ to recognize these values as actual ColumnNames so I don't lose the
¤ information?

Those are the default column name values if you don't specify HDR=YES in the connection string. It
doesn't matter what the data types are.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Michael C]

Huh ???

Specifying HDR=YES (which I believe is the default, by the way), returned
names like "F3", "F4", "F5" for *all* column names that were numeric (i.e.,
1990, 1991, etc.) It returned the actual column name for columns that were
named "X", "Source", etc. This happened when I specified HDR=YES *and* when
I left out the HDR= specification.

My final fix was to set HDR=NO;IMEX=1 in the Extended Properties and treat
the header row as a regular row as far as OleDb is concerned.

Thanks,
Michael C#

 

[Paul Clement]

¤ Huh ???
¤
¤ Specifying HDR=YES (which I believe is the default, by the way), returned
¤ names like "F3", "F4", "F5" for *all* column names that were numeric (i.e.,
¤ 1990, 1991, etc.) It returned the actual column name for columns that were
¤ named "X", "Source", etc. This happened when I specified HDR=YES *and* when
¤ I left out the HDR= specification.
¤
¤ My final fix was to set HDR=NO;IMEX=1 in the Extended Properties and treat
¤ the header row as a regular row as far as OleDb is concerned.
¤
¤ Thanks,
¤ Michael C#

If you use HDR=YES, the first row of the Worksheet is used for the column names. I have no idea what
your Worksheet looks like so I can't explain the behavior you are seeing.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Michael C]

If you use HDR=YES, the first row of the Worksheet is used for the column names. I have no idea what
your Worksheet looks like so I can't explain the behavior you are seeing.

Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

From the Original Posted question: "The Excel worksheet looks has the
following columns: Data Description / Source / 1990 / 1991 / 1992 / etc."

From a Subsequent Posting: "I guess it's assigning alphanumeric "F3, F4,
etc." because the ColumnNames are all numeric (1990, 1991, etc.) "

The worksheet has the following column headers: "Data Description",
"Source", "1990", "1991", "1992", ...

With HDR=YES, OleDB returns the following column names: "Data Description",
"Source", "F1", "F2", "F3", ...

If you can explain this, I would definitely appreciate it. In the meantime,
HDR=NO;IMEX=1; lets me get the info. I need out of this worksheet.

Thanks,
Michael C#, MCDBA

 

[Andy O'Neill]

From the Original Posted question: "The Excel worksheet looks has the
following columns: Data Description / Source / 1990 / 1991 / 1992 / etc."

From a Subsequent Posting: "I guess it's assigning alphanumeric "F3, F4,
etc." because the ColumnNames are all numeric (1990, 1991, etc.) "

The worksheet has the following column headers: "Data Description",
"Source", "1990", "1991", "1992", ...

With HDR=YES, OleDB returns the following column names: "Data
Description",
"Source", "F1", "F2", "F3", ...

If you can explain this, I would definitely appreciate it. In the
meantime,
HDR=NO;IMEX=1; lets me get the info. I need out of this worksheet.

Thanks,
Michael C#, MCDBA

Beats the hell out of me, if it's something to do with them being numerics
how about if they were kinda text.
I wonder if your headers were '1990 and '1991 with a quote...
I reckon that'd make em dealt with as text.

I've imported data from excel in the past.
Sometimes I had to insert a dummy line or excel decides the datatype of a
column is numeric or text.

 

[Michael C#]

Beats the hell out of me, if it's something to do with them being numerics
how about if they were kinda text.
I wonder if your headers were '1990 and '1991 with a quote...
I reckon that'd make em dealt with as text.

I've imported data from excel in the past.
Sometimes I had to insert a dummy line or excel decides the datatype of a
column is numeric or text.

Hi Andy,

I actually thought of that and changed some of the columns to text with a
quote. It still didn't read the all-number fields as column headers... I'm
thinking OleDb just might not like all-number column names. That's ok
though, changing the headers on all these spreadsheets isn't a viable option
for this project. I came to the conclusion that if your column headers are
not guaranteed to be non-numeric, reading them in as

I ended up just reading the header row in just like any other row, and dealt
with it in code.

You might try the IMEX=1 parameter in your apps. It seems to help OleDb
bring data back as the correct datatype.

The only other problem I ran into was Excel occasionally adding a completely
blank row or column to the end of the spreadsheet, but that was easy enough
to deal with in code (Just check for a null of empty string column or row
'header').

Thanks,
Michael C#