CODEWITHSUNDEEP
c#exceloledb
Richa
Richa

Reputation: 3289

Read all columns in excel

How do I read an Excel spreadsheet having blank column. I have spreadsheet in below format:

enter image description here

I am trying to read the excel file with this code:

 string query = "SELECT * FROM [Sheet1$]";
 using (OleDbConnection connection = new OleDbConnection(connString))
  {
    connection.Open();
   OleDbCommand command = new OleDbCommand(query, connection);
   OleDbDataReader reader = command.ExecuteReader();
 while (reader.Read())
     {
       //Do something
     }
}

But it reads data of only the starting 2 columns and not the remaining. How do I read data of all columns?

Upvotes: 1

Views: 1051

Answers (2)

Lei Yang
Lei Yang

Reputation: 4325

I tried OleDbDataAdapter to fill DataTable and displayed in DataGridView. Your sql can retrive all the data. However, your excel contains duplicate column names. After read by sql, column name was added suffix such as column1. So maybe in your //Do something statement, you can try such column names.

my test data my test result

Upvotes: 3

ChP
ChP

Reputation: 486

I had a similar problem with data connections in Excel a while ago. The following query should do the trick:

string query = "SELECT * FROM [Sheet1$A:E]";

You should just specify the range you would like to use.

Upvotes: 2

Related Questions