Excel Import using ADO.net

[Guest]

hi!
I've managed to pull some data from an excel spreadsheet using ADO.net
Rather simple.
The Only problem is that whenever I put that data into dataGrid, it adds
these F1, F2 words to the header whenever a null is retrived from the excel
doc. In this example, all items are null, but that won't always be the case.
I just want to get rid of the F1, F2 items that are automatically added.

I took a screen shot, take a look:

http://thekcins.com/caseyTest/Fhead.jpg

Thanks for the Help!

Casey

 

[Bernie Yaeger]

Hi Casey,

Those appears to be field headers - ie, column1, column2, etc. I believe
there is a switch in the connectionstring that allows for 'NOHEAD' or
something like that - I saw a reference to this on google with 'excel to
dataset' as the search.

HTH,

Bernie Yaeger

 

[Guest]

Yeah, there's an option you can add to the end of the connectionString:
"Extended Properties=""Excel 8.0;HDR=YES"""

HDR=YES, or HDR=NO, but it doesn't help. If I set HRD = NO, then it just
adds an extra blank line after the F1 header column...

 

[Guest]

I actually Figured it out. The "F1", "F2" fields are actually the names of
the columns. I guess Excel gives them that name or something. Anyhow, the
names of the colums can be changed using:

ds.Tables(0).Columns.Item(1).ColumnName = "something"

that fixed the problem.

 

[Bernie Yaeger]

Hi Casey,

Yes, that was my surmise, but I didn't know you could rename them - glad you
figured it out.

Bernie

 

[Paul Clement]

¤ hi!
¤ I've managed to pull some data from an excel spreadsheet using ADO.net
¤ Rather simple.
¤ The Only problem is that whenever I put that data into dataGrid, it adds
¤ these F1, F2 words to the header whenever a null is retrived from the excel
¤ doc. In this example, all items are null, but that won't always be the case.
¤ I just want to get rid of the F1, F2 items that are automatically added.
¤
¤ I took a screen shot, take a look:
¤
¤ http://thekcins.com/caseyTest/Fhead.jpg
¤

You can also alias the default column names in your SQL statement:

Dim da As New System.Data.OleDb.OleDbDataAdapter("Select F1 As Col1, F2 As Col2, F3 As Col3 from
[ReportSheet$]", objConn)


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