none
Read Excel values. RRS feed

  • Question

  • Hi,

    In excel if i have 3 columns and n number of rows,I need to loop through excel sheet.

    While looping if a Email column value is not present in the table, I should read that entire row Name,Email and Address and save it table.

    Then next read next rows subsequently. If Email is present in table I should not insert the value and skip that row.

    I have an Excel with following column names as header.

    a ) Name b) Email c) Password

    In table T1 I have

    a) Name

    b)Email

    c)Password

    Excelxcel.Application xlApp;
                    Excel.Workbook xlWorkBook;
                    Excel.Worksheet xlWorkSheet;
                    Excel.Range range;
    
                    string str;
                    int rCnt = 0;
                    int cCnt = 0;
    
                    xlApp = new Excel.Application();
                    xlWorkBook = xlApp.Workbooks.Open("@"C:\\Users\\guhananth\\Documents\\MEDICAL BILLS.xlsx"", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                    range = xlWorkSheet.UsedRange;
    
                    for (rCnt = 0; rCnt <= range.Rows.Count; rCnt++)
                    {
                        for (cCnt = 0; cCnt <= range.Columns.Count; cCnt++)
                        {
                            //You have your cell column value here
                            str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                            Response.Write(str);
                            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["YourConnectionStringValue"].ConnectionString))
                            {
                                //Open your connection
                                conn.Open();
    
                                //Your Select Query
                                //Change this as per your design and provide the tablename and columnname here
                                string selectString = "SELECT COUNT(*) FROM YourtableName WHERE YourIDField = @idfield";
    
                                //Create Command object
                                SqlCommand myCommand = new SqlCommand(selectString, conn);
    
                                //Pass your parameter here
                                myCommand.Parameters.AddWithValue("@idfield", str);
    
                                // Get the Result query
                                var idExists = (Int32)myCommand.ExecuteScalar() > 0;
    
                                //Check if record exists in table
                                if (!idExists)
                                {
                                        //How to get each column values ans pass to these string
                              string strName,strLast,strGender
                                        SqlCommand cmd1 = new SqlCommand("Insert into YourtableName (first_name,last_name,sex,dob,active) values(@first_name, @last_name,@sex,@dob,@active)", conn);
                                        //Pass values to parameter
                                        cmd1.Parameters.Add("@first_name", SqlDbType.NVarChar).Value = strName;
                                        cmd1.Parameters.Add("@last_name", SqlDbType.NVarChar).Value = strLast;
                                        
                                        //Exceute query
                                        cmd1.ExecuteNonQuery();
                                        conn.Close();
                                    
                                }
                            }
                        }
                    }

    Here how to collect each column values into 3 seperate variables Name,Email and Address.

    Guha

    Saturday, March 7, 2015 2:34 AM

Answers

  • ExecuteScalar only returns the first field.  You need to use ExecuteReader to get all the values

                                SqlDataReader reader = myCommand.ExecuteReader();
                                string strName = reader["first_name"];
                                string strLast = reader["last_name"];
                                string sex = reader["sex"];


    jdweng

    Sunday, March 8, 2015 11:29 AM
  • If the question is how to read from Excel, then you already have a similar code:

        string name = range[rCnt, 0].Value2.ToString();
        string email = range[rCnt, 1].Value2.ToString();
        string password = range[rCnt, 2].Value2.ToString();

    (If indexes 0, 1, 2 do not work, then try 1, 2, 3).

    And you probably do not need the second (inner) for loop, since you are enumerating the rows, then get three values by column index.

    And it will be more optimal if you create the connection one time before the loop instead of multiple times.

    And both of the SQL statements, or all of them, can be probably combined into single special query.


    Sunday, March 8, 2015 2:22 PM
  • That is one way, but for performance's sake I'd probably use xlWorkSheet.UsedRange to fetch all cells into 2 dimension array of object at once to avoid the cost of transverse COM boundary.
    Monday, March 9, 2015 1:56 AM
    Answerer

All replies

  • ExecuteScalar only returns the first field.  You need to use ExecuteReader to get all the values

                                SqlDataReader reader = myCommand.ExecuteReader();
                                string strName = reader["first_name"];
                                string strLast = reader["last_name"];
                                string sex = reader["sex"];


    jdweng

    Sunday, March 8, 2015 11:29 AM
  • If the question is how to read from Excel, then you already have a similar code:

        string name = range[rCnt, 0].Value2.ToString();
        string email = range[rCnt, 1].Value2.ToString();
        string password = range[rCnt, 2].Value2.ToString();

    (If indexes 0, 1, 2 do not work, then try 1, 2, 3).

    And you probably do not need the second (inner) for loop, since you are enumerating the rows, then get three values by column index.

    And it will be more optimal if you create the connection one time before the loop instead of multiple times.

    And both of the SQL statements, or all of them, can be probably combined into single special query.


    Sunday, March 8, 2015 2:22 PM
  • I'll add that you need to call reader.Read() once before using it to move the cursor to the first record.

    Btw, his code is for reading the COUNT(*) to see if record with same key is already exist, so no problem is on that part.

    Monday, March 9, 2015 1:50 AM
    Answerer
  • That is one way, but for performance's sake I'd probably use xlWorkSheet.UsedRange to fetch all cells into 2 dimension array of object at once to avoid the cost of transverse COM boundary.
    Monday, March 9, 2015 1:56 AM
    Answerer