READING COLUMN NAMES AND VALUES FROM DATA READER FILLED FROM EXCEL FILE

Article
2009-09-09

Question

_{Wednesday, September 9, 2009 5:35 PM}

Hi,

I am struggling to find a way to read the column names and values from a Data Reader that I populated from an excel file and would appreciate very much is someone could help me.

This is what the excel file looks like. As you can see I have columns headings in bold and values not in bold. Some cells have no values.

Company                    Audit    JARScan
test1                                             Y
test2                              Y

I need to produce any kind of array with name/value pairs for as many columns as there are:

company = test1 , Audit = "" , JARScan = "Y"
Company = test2 , Audit = Y , JARScan = ""

In have tried all these combination below but to no avail:

connection.GetSchema("Columns");

DataTable dt = dr.GetSchemaTable();
for (int i=0;i<dt.Columns.Count;i++)
{
string cl = dt.Columns[i].ColumnName;
}

while (dr.Read())
{
    for (int i = 0; i < dr.GetSchemaTable().Columns.Count; i++)
   {
        string cn = dr.GetSchemaTable().Columns[i].ColumnName;
        string val = dr.GetSchemaTable().Columns[i].DefaultValue.ToString();
   }

}

Cheers

Below is the full code but the bit to read the colum names and values is not working.

try
        {
            string FileName = Session["fileName"].ToString();
            string Extension = Path.GetExtension(Session["fileName"].ToString());
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);

            string connectionString = "";
            switch (Extension)
            {
                case ".xls": //Excel 97-03
                    connectionString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07
                    connectionString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }

connectionString = String.Format(connectionString, FilePath, "YES");

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = connection.CreateCommand())
                {
                    // sheet$ comes from the name of the worksheet
                    string sheet = ddlSheets.SelectedItem.Text;
                    command.CommandText = "SELECT * FROM [" + sheet + "]";

connection.Open();

using (DbDataReader dr = command.ExecuteReader())
{

                        DataTable columns;

                       //columns = connection.GetSchema("Columns");

                        DataTable dt = dr.GetSchemaTable();
                        for (int i=0;i<dt.Columns.Count;i++)
                        {
                            string cl = dt.Columns[i].ColumnName;
                        }



                        while (dr.Read())
                        {

                            for (int i = 0; i < dr.GetSchemaTable().Columns.Count; i++)
                            {
                                string cn = dr.GetSchemaTable().Columns[i].ColumnName;
                                string val = dr.GetSchemaTable().Columns[i].DefaultValue.ToString();

                            }

                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            lblMessage.ForeColor = System.Drawing.Color.Red;
            lblMessage.Text = ex.Message;
        }
        finally
        {
            //con.Close();
            //con.Dispose();
            Panel1.Visible = true;
            Panel2.Visible = false;

        }

    }

All replies (6)

_{Thursday, September 10, 2009 1:49 PM ✅Answered | 1 vote}

You can try this:

OleDbDataReader dr = cmd.ExecuteReader();
DataTable dt = dr.GetSchemaTable();

String Column1Header = dt.Rows[0][0].ToString();
String Column2Header = dt.Rows[1][0].ToString();
String Column3Header = dt.Rows[2][0].ToString();

String Value;

while (dr.Read())
{
   // do something
   // you can access row like this
    Value = dr[Column1Header].ToString();
}

To store this you can load these names into an array or list and use that collection.

_{Wednesday, September 9, 2009 6:04 PM | 1 vote}

I assume that you are getting "A", "B", etc... for column names. Excel does not allow labeling its columns this so it can run functions against them. You can store the column names in the first row or set up a static template that the Excel file have to utilize and use that schema.

_{Thursday, September 10, 2009 12:00 AM}

Hi Michael,

Thanks for your reply.

I did not quite understand what you said. Could you please elaborate a bit more on that.

Once I read the excel file into a data reader, I thought I would have access to its header information as well.

I can easily get the values from the cells by doing this:

dr("Audit")

But what about getting the headers and values dynamically?

Could you please provide some sample code for that?

As you can see, I have produced a very simple excel file. The first row contains the column headers and the other rows contain the values.

Company                    Audit    JARScan
test1                                             Y
test2                              Y

I just need a way to dynamically create a name / value pair. It could even be a XML file like this:

So the type attribute would contain the name of the column/heading and the value would contain the value of the cell.

I would apprediate very much if could shed some light.

Cheers

_{Thursday, September 10, 2009 2:30 AM}

I do not understand by what you mean "dynamically"? Once you read the file into the reader and you can access data by column name what exactly are you needing beyond that?

Are you asking if you can get the schema of the file before reading it?

_{Thursday, September 10, 2009 8:11 AM}

No,

Based on the data reader, I want to produce the following XML file. :

Please note that the XML structure contains both the column headings text and the values of the cells as attributes.

What I am finding difficult is how to get the column headings text and assign it to the type attribute.

EXCEL FILE

Company                    Audit    JARScan
test1                                             Y
test2                              Y

Cheers

_{Friday, September 11, 2009 8:28 AM}

Many thanks for you help.

I have tried you code example and it is working.

The only problem is that you are hard coding the number of columns to 3. Ideally, it sould account for any number of columns. So, inside the while loop I would have to read the column text and cell value.

Cheers

Share via

READING COLUMN NAMES AND VALUES FROM DATA READER FILLED FROM EXCEL FILE

Question

All replies (6)

EXCEL FILE

Additional resources