First Column not returning while reading excel sheet using oledb reader

Question

0.00/5 (No votes)

See more:

Hi,
I facing problem to read excel sheet using oledb reader, first column not returning by reader and showing at last column head F14 and column has been empty.
but when i open excel sheet and double click on header row border for auto adjust and auto re size save excel and again read then all columns returning perfectly.

Excel sheet which I try to read that generates using php application and after download that excel we put on my application to read data from excel but above issue come.

I already did lots of R&D even i give width in excel sheet while generating excel using web application. My code is like this

C#

private bool Import_To_Grid(string FilePath, string Extension)
       {
           try
           {
               string conStr = "";
               switch (Extension)
               {
                   case ".xls": //Excel 97-03
                       conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
                                .ConnectionString;
                       break;
                   case ".xlsx": //Excel 07 and above
                       conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
                                 .ConnectionString;
                       break;
               }
               conStr = String.Format(conStr, FilePath);
               OleDbConnection connExcel = new OleDbConnection(conStr);
               OleDbCommand cmdExcel = new OleDbCommand();
               OleDbDataAdapter oda = new OleDbDataAdapter();

               cmdExcel.Connection = connExcel;

               //Get the name of First Sheet
               connExcel.Open();
               Exceldt = new DataTable();
               DataTable dtExcelSchema;
               dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
               string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
               connExcel.Close();

               //Read Data from First Sheet
               connExcel.Open();
               cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
               oda.SelectCommand = cmdExcel;

               oda.Fill(Exceldt);
               connExcel.Close();

               //Bind Data to GridView
               dgv_showexcel.DataSource = Exceldt;
               BindDataToCmbClass(); //binddata to class for filter
               cmb_userclass.SelectedIndex = 0;
               return true;
           }
           catch (Exception ex) { MessageBox.Show("Its Error" + " " + ex.ToString()); return false; }
       }

config

XML

<add name="Excel03ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';" />
  <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';" />

[EDIT]

Error:

"No value given for one or more required parameters."

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at ImageMetaDataApplier.Home.Import_To_Grid(String FilePath, String Extension) in h:\Meharwan Singh\Desktop Application\ImageMetaCreator\ImageMetaDataApplier\home.cs:line 71

[/EDIT]

Posted 22-Jul-14 7:19am

sagar dindorkar

Updated 26-Jan-19 0:05am

Maciej Los

v4

Add a Solution

Comments

Maciej Los 22-Jul-14 15:32pm

Remove IMEX=1 and try again.
More details here [^]

sagar dindorkar 23-Jul-14 3:12am

I removed IMEX=1 still facing same issue

Maciej Los 23-Jul-14 4:14am

Use named columns: SELECT Col1, Col2, Col3, ... Col13 FROM [SheetName].

sagar dindorkar 23-Jul-14 6:35am

Hi Maciej,
yes I am did this way but through error of parameter not pass sufficient.

Maciej Los 23-Jul-14 6:51am

What error? Not understand...

sagar dindorkar 23-Jul-14 7:27am

--- the content has been moved to the question ---
Maciej Los

sagar dindorkar 23-Jul-14 7:28am

--- the content has been moved to the question ---
Maciej Los

sagar dindorkar 23-Jul-14 7:30am

Thank you!!!
please help me out!!
I am exhausted from last tow days to search out the issue

nilesh sawardekar 22-Jul-14 23:20pm

Your not getting first row or first column..?

sagar dindorkar 23-Jul-14 3:10am

First Column

nilesh sawardekar 23-Jul-14 3:58am

surprisingly your code work in my vs

nilesh sawardekar 23-Jul-14 4:02am

you want first header or next column?

sagar dindorkar 23-Jul-14 5:05am

@nilesh sawardekar first very very thankful of you to reply and show interest in question yes I know its working even for me working very nice but when i use downloaded excl in this code there is problem as I mention in my question manual generated excel reading very nicely even my another application generated excel sheet download by me and then i click on any of excel sheet column and then clicked for auto-size on column border then save file. then this code reading files perfectly.

sagar dindorkar 23-Jul-14 5:07am

I want to read all data exist in excel file

sagar dindorkar 23-Jul-14 6:36am

I am not getting First Column

CHill60 23-Jul-14 8:08am

When you bind the data have you set values for the DataPropertyName properties of the columns - could you have missed one out?

sagar dindorkar 23-Jul-14 9:16am

I am using this which included here in query all columns
String sCommand = @"SELECT [Username],[Password],[Season],[Project_id],[Class],[School],[First_name],[Last_name],[Address],[Postalcode],[Town],[Telephone],[Date_of_birth],[Student_id] FROM ["+SheetName+"]";

CHill60 23-Jul-14 9:22am

Sorry - I meant on the properties of the datagridview you can define which column from your query is displayed in each column of the grid.

sagar dindorkar 23-Jul-14 10:01am

All columns and I am checking using break point data reader not returning First Column

sagar dindorkar 23-Jul-14 9:25am

let me know one thing if we generate excel file by our program that time first column has been locked by program or something like that??
bcz when I use

Select * from [Worksheet$]
then I get data of all columns but I didn't get First column name and that column data.
and
Second Case is that when I open Excel file and double clicking of header column row(any) then after saving that excel sheet again I get all columns data perfectly

rajah rajah 16-Mar-16 0:58am

Hi sankar have u got solution for this issue ? i also stuck with the same issue. Pls let me know if u got solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2014-07-23T02:11:00

First of all, you did not provide enough information about your issue. I know, it's hard to be more specific in case when last few days you can't find a reason of error. Nevertheless...

Secondly, i suggest you to read these articles:
Accessing Microsoft Office Data from .NET Applications[^]
How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET[^] - it's VB.NET, but it's similar to C#

Now, have a look at here: How to: Get Data from Multiple Workbooks using One OleDbConnection?[^]
I'm using simplest code to retrieve data from MS Excel Workbook.

C#

String sConnString = "Your_Connection_String";
OleDbConnection oConn = new OleDbConnection(sConnString);
oConn.Open();
String sCommand  = @"SELECT <list_of_columns>" + Environment.NewLine +
            "FROM [SheetName$]" ;
OleDbCommand oComm = new OleDbCommand(sCommand, oConn);
OleDbDataReader oRdr = oComm.ExecuteReader();
DataTable oTbl = new DataTable();
oTbl.Load(oRdr);

//now, you can bind data

Test it. Let me know if it won't help...