none
C# reading excel file where the header is not the first row in OLEDB RRS feed

  • Question

  • Hi , 
    I have this code but only import data when the column name start in first row. My column names starts in column 2 to column row 6 How I resolved this? I know i need a range but i dont know the code for that I try this but send me that error 

    System.InvalidOperationException: 'The ColumnName 'Atributo' the column does not march any column in the data source'.

    DataTable dt = new DataTable();
                    conString = string.Format(conString, filePath);
    
                    using (OleDbConnection connExcel = new OleDbConnection(conString))
                    {
                        using (OleDbCommand cmdExcel = new OleDbCommand())
                        {
                            using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                            {
                                cmdExcel.Connection = connExcel;
    
                                //Get the name of First Sheet.
                                connExcel.Open();
                                DataTable dtExcelSchema;
                                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                                string sheetName = dtExcelSchema.Rows[4]["TABLE_NAME"].ToString();
                                //string Range = dtExcelSchema.Rows[4]["COLUMN_NAME"].ToString();
                                connExcel.Close();
    
                                //Read Data from Specific Sheet
                                connExcel.Open();
                                cmdExcel.CommandText = "SELECT DISTINCT* From [4.2$A5:ZZ]";
                                odaExcel.SelectCommand = cmdExcel;
                                odaExcel.Fill(dt);
                                connExcel.Close();
                            }
                        }
                    }
    
                    conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(conString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            //Set the database table name.
                            sqlBulkCopy.DestinationTableName = "dbo.Tipo_Adquisicion";
    
                            //[OPTIONAL]: Map the Excel columns with that of the database table
                            sqlBulkCopy.ColumnMappings.Add("Atributo", "Tipo_Adquisicion");
                            sqlBulkCopy.ColumnMappings.Add("PERÍODO INFORMACIÓN", "descripcion");
                           // sqlBulkCopy.ColumnMappings.Add("Country", "Country");
    
                            con.Open();
                            sqlBulkCopy.WriteToServer(dt);
                            con.Close();
                        }
                    }
                }
    

    Wednesday, October 3, 2018 8:21 PM

Answers

  • Hi Neraks,

    Thank you for posting here.

    Please check the code which use to read special row from Excel using C#.

    My Excel

    using (OleDbConnection conn = new OleDbConnection())
                {
                    DataTable dt = new DataTable();
                    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"Read data from Excel.xlsx" + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        comm.CommandText = "Select * from [" + "Sheet1" + "$A3:B6]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                        }
                    }
                }

    The datatable I get from Excel.

    If you want t get the special row of the excel, change the A3:B6 in the code.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Neraks Thursday, October 4, 2018 1:15 PM
    Thursday, October 4, 2018 7:47 AM
    Moderator

All replies

  • Hello,

    Your code:

    cmdExcel.CommandText = "SELECT DISTINCT* From [4.2$A5:ZZ]";

    You selecting from page named '4.2'

    You selected all rows 

    You selected columns from 'A5' till 'ZZ'

    You also should be able to use 

    cmdExcel.CommandText = "SELECT DISTINCT A as 'My Column Name' From [Sheet1$A1:A1]";

    Sorry, I do not understand a description : in column 2 to column row

    If you not need first row - just do not use it.

    Exception is - your DataTable dt didn't have column  

    "Atributo"

    It's most likely have A5,A6,...,ZZ - the same as it was in Excel shhet


    Sincerely, Highly skilled coding monkey.

    Wednesday, October 3, 2018 9:24 PM
  • thanks  but I try but still dont works ,I need all the rows from row 6, because in the first rows there is data that is not necessary and the headings of the tables are also from row 6 down. 

    this i have now

     cmdExcel.CommandText = "SELECT F7 as Atributo From [4.3$A6:F10]";


    • Edited by Neraks Thursday, October 4, 2018 1:42 AM
    Thursday, October 4, 2018 1:42 AM
  • in FROM part you specify PAGE$<Column From>:<Column To>.

    You will get ALL non-empty rows and deal with them on client.


    Sincerely, Highly skilled coding monkey.


    Thursday, October 4, 2018 6:25 AM
  • Hi Neraks,

    Thank you for posting here.

    Please check the code which use to read special row from Excel using C#.

    My Excel

    using (OleDbConnection conn = new OleDbConnection())
                {
                    DataTable dt = new DataTable();
                    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"Read data from Excel.xlsx" + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        comm.CommandText = "Select * from [" + "Sheet1" + "$A3:B6]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                        }
                    }
                }

    The datatable I get from Excel.

    If you want t get the special row of the excel, change the A3:B6 in the code.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Neraks Thursday, October 4, 2018 1:15 PM
    Thursday, October 4, 2018 7:47 AM
    Moderator
  • THANKs! now works :) 
    Thursday, October 4, 2018 1:14 PM