Retrieving Excel Schema Information

  • Thread starter Thread starter Rohit
  • Start date Start date
R

Rohit

Hello,

I am retreiving Excel Schema Information from a Excel Sheet.

I am using GetOleDBSchemaTable for it.

I see a strange behaviour sometimes that when I retrieve the Column names
from a
particular Excel file, It returns the column names which are there as well
as some extra columns like "F1,F2.,..."

Can anyone please tell me if this is a bug or I am doing something wrong.

Any help is appreciated.

Regards,
Rohit
 
Just a thought. Try to see in Excel what is the last used cell. Formatting
even if the column is empty may well expose those apparently empty
columns...
 
Hello Patrice,

Thanks for the response.
But I cant come to know if the column has some values in it, because the
Schema will just return me the column names

Dim SchemaFilter() As Object = New Object(3) {Nothing, Nothing,
Me.ComboSheets.Text & "$", Nothing}

Dim dtTables As DataTable =
conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,
SchemaFilter)



Regards,

Rohit
 
I meant that you could open the file with Excel and use Edit, Find menu
(translated from French, may differ) and the cells button to select the
"last cell" option. It should allows to see if it points you to an empty
cell that just have formatting options but not data causing those columns to
be seen by the driver. The easiest way to fix this is to delete those
columns...

Once the source of the problem is known, you can go to the fixing phase.

Is this really a problem ? Where does this file come from ? Is it provided
by users ? If this is for some kind of data import, I would just take care
of the columns I'm interested in and would ignore others (possibly warning
the user).
 
But the problem is I dont know the Excel file what info it contains so i
just cant edit the Excel file.

Now in my .NET code, how can i handle such a situation?/
 
Do you have Excel ? Launching Excel is not to solve the problem but just to
verify my assumption as I'm not 100% sure this is the problem you are
running into.

Now for the programming part, could you explain us what you want to do with
this Excel file and why having those extra columns would create a problem in
your application ?

For now you have those extra columns but I don"t understand whay this is a
problem. I would assume this is for importing data. You should be
interesting then only in known columns and could perhaps ignore others...
In the worst case, once the Excel file is opened you'll be able to see that
this column doesn't have any data (but you could have also extra columns
because the user entered by mistake something in its Excel sheet in whihc
ase the extra columns will contain something).
 
Ok Let me explain you.

I create a Excel sheet, where in for first 3 columns I have a Column name in
the first row and then the data in subsequent rows. Noe for 4th and 5th
column i leave them as it is. For 6th and 7th column I again have some
column names in the first row and then the data in subsequent rows,

so it looks something like this:
Col1 Col2 Col3 <<Blank Column>> <<Blank Column>> Col6 Col7
Rows of Data.....

Now when I use GetOleDBSchemaTable to get the Schema Information for this
excel file, I get the following columns in the datatable.
Col1 Col2 Col3 F1 F2 Col6 Col7

Now these F1 and F2 are the column names which are genrated by OLEDB Schema
and these were empty columns in the Excel sheet.

So my question is how can i get rid of "F1" and "F2"??

Hope I made myself clear.

Regards,
Rohit
 
Ok I didn't understood it was made on purpose.

IMO you can't. The driver just takes from the first column to the last
column of data. Those columns are empty but they still exists (exactly as a
DB could have a column with no data in it). My personal preference would be
just to ignore the unwanted columns when processing the schema (possibly
filtering or deleting the extra rows once the schema is retrieved in the
schema DataTable)...

I'm not sure but if those columns are really so annoying you could perhaps
try to select only the wanted columns and create a named range from them
(not sure but I believe this is possible) and then try to access this named
range. Not sure it's worth the trouble over just ignoring the unwanted
information...
 
¤ Hello,
¤
¤ I am retreiving Excel Schema Information from a Excel Sheet.
¤
¤ I am using GetOleDBSchemaTable for it.
¤
¤ I see a strange behaviour sometimes that when I retrieve the Column names
¤ from a
¤ particular Excel file, It returns the column names which are there as well
¤ as some extra columns like "F1,F2.,..."
¤
¤ Can anyone please tell me if this is a bug or I am doing something wrong.

I don't believe it's a bug but I would have to see an example of the Excel sheet. The names F1, F2
etc. are the default column names when no header is available or when the column names are blank.
What happens when you query the Worksheet? Do you have column names F1, F2 etc. in the resulting
DataTable? If so, it's probably because the driver has determined that there are legitimate values
in those columns.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top