Click here to Skip to main content
15,885,985 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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

What I have tried:

ataTable 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[0]["TABLE_NAME"].ToString();
                            
                            connExcel.Close();

                            // TRY THIS BUT NOT WORKS
                            // string Range = 
                            //cmdExcel.CommandText = "SELECT * From [" + sheetName + "$B6:C10]";
                            //cmdExcel.CommandText = "SELECT * From [" + sheetName + Range +"]";
                            connExcel.Open();
                            cmdExcel.CommandText = "SELECT * From ["+ sheetName +"]"; 
                            
                            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("CÓDIGO EMPRESA", "Tipo_Adquisicion");
                        sqlBulkCopy.ColumnMappings.Add("PERÍODO INFORMACIÓN", "descripcion");
                       // sqlBulkCopy.ColumnMappings.Add("Country", "Country");

                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                    }
                }
            }
Posted
Updated 4-Oct-18 3:17am
Comments
Maciej Los 3-Oct-18 14:48pm    
OleDb provider can read one-row-header. If a header contains more rows, those rows are treated as data.

Please, read my comment to the question. You can not read (load) multiline header through the OleDb provider.

If you would like to read data from sheet starting from second row, you have to define it in SELECT statement, for example:
SQL
SELECT * FROM [Sheet1$a2:d]


For further details, please see: Excel connection strings - ConnectionStrings.com[^]

Alternatively, you can read Excel data using:
- OpenXml sdk[^]
- EPPlus library[^]
 
Share this answer
 
v2
Comments
Richard Deeming 3-Oct-18 15:41pm    
Wouldn't a5:d start at the fifth row, rather than the second row? :)
Maciej Los 4-Oct-18 2:20am    
Yeah, it should be 2 instead of 5.
NerakSeven 3-Oct-18 15:57pm    
I try with that example but dont works :(
Maciej Los 4-Oct-18 2:20am    
This reply is not descriptive at all!
I resolved with that code below :)

cmdExcel.CommandText = "Select * from [" + "4.3" + "$A6:D10]";
                           odaExcel.SelectCommand = cmdExcel;
 
Share this answer
 
Comments
Maciej Los 4-Oct-18 13:31pm    
So, you did exactly what i said.
NerakSeven 4-Oct-18 16:10pm    
yes thanks!

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



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