Column Names  
Author Message
carrielm





PostPosted: .NET Framework Data Access and Storage, Column Names Top

Hi,

I have an Excel data sheet that I need to read and select the pertinent rows to load into a dataset. The data will then be checked against a database and the new data will be added. I have several questions/problems, the first of which has to do with Column names:

My Excel sheets have column headers, BUT the header names are not located in the first row, rather they are located in the 5th row of the sheet, always. I would ignore this issue altogether and simply use column position as a locator for the data, but the sheets do not always have the same number of columns (ie sometimes columns are missing). Therefore I need to know which columns contain which data.

How can I specify the columns such that the correct data get inserted into the correct columns in the DataSet, and ultimately the database

I am using an OleDB connection to get the data from the Excel table, and the extended properties of HDR=No to indicate that the first row is not a header row and IMEX=1 to read the columns as strings. (If I don't specify IMEX=1 then I can't read all of the values in row 5 (ie the true column names) correctly - some are null.)

Help!

Carrie


.NET Development15  
 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Column Names Top


Have you tried defining a Range for your data using Excel and then specifying the Range name in your SELECT statement.

BTW, the HDR argument simply indicates whether you want to use the first row for the column names in your result set. If this value is No then then the default column names are used (F1, F2, F3, etc.).



 
 
carrielm





PostPosted: .NET Framework Data Access and Storage, Column Names Top

Good to know about the HDR argument.

I'm not sure I understand why I want to define a range for my Excel data - the range will vary for every sheet. I can define the range in my SELECT statement like this:

cnOleDbDA = New OleDbDataAdapter("SELECT * FROM [A5:F5]", cnOleDb)

and it returns my header row (hooray!), but now what do I do with it How do I use those values to name my columns in the DataSet I'm at a loss - right now I am using a DataGrid control on a windows form to view what is being returned.

I must apologize - I am a complete beginner at VB.NET. I have used VB6 in the past, but not to manipulate database data!

 
 
carrielm





PostPosted: .NET Framework Data Access and Storage, Column Names Top

OK, actually I have to amend what I previously said. If I use a SELECT statement and remove the HDR argument, the DataTable gets "filled" only with the row I selected, the header row. The resulting DataTable is consequently empty, but with headers. Now how can I fill it with the rest of the data

Apologies for being dense. :)

Carrie

 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Column Names Top


OK, maybe I misunderstood. I thought your column headers were always in row five of the Worksheet. Doesn't that mean that your data starts in row six Or am I missing something

Using a Range will identify where the data begins and ends.

Edit:

BTW, [A5:F5] represents only one row of data.



 
 
carrielm





PostPosted: .NET Framework Data Access and Storage, Column Names Top

Whoops, yes you are correct. Headers are always in row 5 and data always starts in row 6. So if I specify my range as starting in row 5 and ending in an arbitrarily large row (like row 100 - there should never be this much data) will the data set fill with just however many rows there actually are I have tried this using a DataGrid and this is what seems to happen.

Now that I have a DataSet that is filled with Excel data, how do I explicity reference the data This is a complicated situation, and maybe this is not the correct forum, but this is what I am trying to do:

For example, say I have the following DataSet (simplified) from my Excel sheet:

Sample NH3 NO3 O-P Dilution Flag
FLD BLK 09/18/06 0.0015 0.003 0.021 1 (null)
S1 N1T 0.0459 0.5665 0.0759 1 (null)
S2 N1B 0.0450 0.5590 0.0495 1 (null)
S3 N3T 0.086 0.581 0.0895 1 O
etc.

My database (Access Database) has the following structure:
ID date station NO3-NO2_T NO3-NO2_B SI_T SI_B etc...
1 09/18/06 N1 0.5665 0.5590

The equipment that produces the Excel sheets stupidly formats the data this way and I can't change it. I only care about rows that have sample data in them, as indicated by "S"#. However, the date is not referenced for each row, only in the first row for that date (here: FLD BLK) in the combined field called "Sample". In addition, there are top ("T") and bottom ("B") samples, which are reported in separate rows in the Excel sheet and the same row in the database (because each row in the database refers to a SITE, not a sample). There are more columns in the database than there are in the Excel sheet (for example, SI_T) - this is because some data is coming from other Excel sheets. Finally, the "Flag" field indicates that a certain parameter needs to be re-processed because it is out of range - so, in the example above, the O-P parameter (which is abbreviated "O" in the "Flag" field) for sample S3 N3T will be reprocessed in the future. This means this sample will show up in another data sheet and that cell value will have to be updated even though there is already a value for it in the database.

I think this is a question for another forum...

Thanks so much,

Carrie

 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Column Names Top


First, you should be able to determine the row count for the Worksheet by running the following query:

SELECT COUNT(F1) AS RowCount FROM [SheetName$]

If there is nothing in the first column of the first four rows then you should have an accurate row count (minus 1 for the header). Otherwise, if there is anything in the first column (F1) of any of the first four rows you will need to subtract one for each row that contains data in this column. The assumption here is that there is always data present in the first column of each row that contains data.

As far as your second question is concerned you can reference the data through the DataTable you've created in the DataSet. I would check out the documentation for the DataTable.Rows property. The DataTable also has a SELECT method which enables you to sort and filter.



 
 
carrielm





PostPosted: .NET Framework Data Access and Storage, Column Names Top

Thanks Paul, will do.

:)