Click here to Skip to main content
15,113,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, i create a simple app that can select all the data in an excel file, but
my problem is that all of the columns is not in row 1 in excel file but in row 4

from row 1 to 3 it consist of the name of the department, name of the sender and date generated and in the row 4 the column names that has the data that i needed.


try
            {
                string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", Path);
                using (OleDbConnection oConnection = new OleDbConnection(constr))
                {
                    string Query = string.Format("SELECT [EmplyeeName],[DOB], [Email], [Mobile] FROM [{0}]", "Sheet1$");
                    using (OleDbCommand oCommand = new OleDbCommand(Query, oConnection))
                    {                        
                        OleDbDataReader oReader = null;
                        try
                        {
                            oConnection.Open();
                            oReader = oCommand.ExecuteReader();
                            while (oReader.Read())
                            {
                                string a = oReader["EmplyeeName"].ToString();
                                string b = oReader["DOB"].ToString();
                                string c = oReader["Email"].ToString();
                                string d = oReader["Mobile"].ToString();
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }                        
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }


What I have tried:

i will update this if i have a progress.
Posted
Updated 4-Aug-21 20:59pm

1 solution

Look at your code:
C#
while (oReader.Read())
{
    string a = oReader["EmplyeeName"].ToString();
    string b = oReader["DOB"].ToString();
    string c = oReader["Email"].ToString();
    string d = oReader["Mobile"].ToString();
}
The reader will continue to return rows until there are no more left, so the loop will process all the rows. But ... inside the loop, you overwrite the previous row's content each time with the current rows - and then you discard even that because a, b, c, and c are all "local" to the loop and go out of scope at the end and can no longer be accessed.

So even if you did move the definitions of those four variables outside the loop so they could be accessed later, they would only ever contain the final row's values!

Either use a collection and add your into it inside the loop, use a DataTable and a DataAdapter instead of a DataReader, or modify your SELECT statement to return on the row you are interested in.
   
Comments
Reden Rodriguez 5-Aug-21 7:45am
   
my column A is consist of 3 different words before the EmployeeName how can i ignore those word? What i did is i deleted the unnecessary rows but my problem now is that it changes how many rows that i needed to delete.
OriginalGriff 5-Aug-21 8:06am
   
If your data consists of columns with information before the info you want (i.e. the "EmployeeName" column contains stuff like "My Name is Reden Rodriguez") then you can't easily get SQL of Excel to remove it: you would need to process each cell value in your C# and remove it there. That would probably mean looking closely at the actual data and identifying exactly what has to be removed first.
Then string.Substring or a Regex is the way to go, depending on the actual column values.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900