DataTable doesn't read all values from Excel[Answered] RSS

• • Reply
  Rahul_agg11

  Member

  144 Points

  417 Posts

  DataTable doesn't read all values from Excel

  Jun 27, 2014 09:50 AM|Rahul_agg11|LINK

  Hi,

  I am exporting data from Excel to DataTable, but DataTable is not reading all the data in Excel. My code is as follows:-

          string myConnection07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=YES'";
  
          string conStr = "";
          switch (Extension)
          {
              case ".xls": 
  
                  conStr = myConnection03;
                  break;
              case ".xlsx": 
  
                  conStr = myConnection07;
                  break;
          }
  
          OleDbConnection connExcel = new OleDbConnection(conStr);
          OleDbCommand cmdExcel = new OleDbCommand();
          OleDbDataAdapter oda = new OleDbDataAdapter();
          DataTable dt = new DataTable();
          cmdExcel.Connection = connExcel;
  
  
          connExcel.Open();
          DataTable dtExcelSchema;
          dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
          string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
          connExcel.Close();
  
  
          connExcel.Open();
          cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
          oda.SelectCommand = cmdExcel;
          oda.Fill(dt);
          connExcel.Close();

  Please Help,

  Thanks,
  Rahul

• • Reply
  rajesh93180

  Participant

  1644 Points

  792 Posts

  Re: DataTable doesn't read all values from Excel

  Jun 30, 2014 08:34 AM|rajesh93180|LINK

  Rahul_agg11

  string myConnection07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=YES'";

  I think it should be like this..

  string myConnection07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=YES'";

  Check this..

  if (fileExtension == ".xls")
              connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
          else if (fileExtension == ".xlsx")
              connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
  
          //Create OleDB Connection and OleDb Command
  
          OleDbConnection con = new OleDbConnection(connectionString);
          OleDbCommand cmd = new OleDbCommand();
          cmd.CommandType = System.Data.CommandType.Text;
          cmd.Connection = con;
          OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
          DataTable dtExcelRecords = new DataTable();
          con.Open();
          DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
          string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
          cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
          dAdapter.SelectCommand = cmd;
          dAdapter.Fill(dtExcelRecords);
          con.Close();
          System.IO.File.Delete(fileLocation);
          return dtExcelRecords;

  -Rajesh Vemunoori LinkedIn Profile
  
  Mark as answer if you find this post helpful.