How to omit the first row in an excel import into a DataTable

Question

The issue I have is I have a spreadsheet to import (on azure so I can't use the excel connector) but there are two rows on the top of the sheet. The first one I need to remove because the second row has the column names. The rest of the rows are the data.

DataTable dt = new DataTable();
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
dt = result.Tables[0];
if (dt.Rows[0].ItemArray[0].ToString() == "Category Info") { dt.Rows[0].Delete(); dt.AcceptChanges(); }

The data imports into the data table but then I need to delete the first row because someone decided it was a good idea to have two rows for the header. :(

When I include the "if" statement, I get an error that columns are missing from my import.

The challenge I'm trying to solve is allowing the user to import an excel file in a specific format onto a web application running on AZURE and as far as I know, the Excel libraries are not available on Azure... hence, bulk copy.

I also tried:

DataSet result = excelReader.AsDataSet();
if (result.Tables[0].Rows[0].ItemArray[0].ToString() == "Category Info") { result.Tables[0].Rows[0].Delete(); result.AcceptChanges(); }
dt = result.Tables[0];

Still getting the result "Column 'DataPointX' does not belong to table Sheet."

Answer 1

I worked it out and this is acceptable:

FileStream stream = File.Open(path, FileMode.Open, FileAccess.ReadWrite);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

DataTable dt = new DataTable();
dt = excelReader.AsDataSet().Tables[0];

Int16 shift = 0;
if (dt.Rows[0].ItemArray[0].ToString() == "Some Text in the header")
{
    shift = 1; //If the first row is a header, we need to shift everything up one row.
}

foreach (DataColumn Col in dt.Columns)
{
    Col.ColumnName = dt.Rows[shift].ItemArray[Col.Ordinal].ToString();
}

Answer 2

"Column 'DataPointX' does not belong to table Sheet."

If you retrieve row’s cell by using dt.Rows[RowIndex]["DataPointX"] but the DataTable does not contain the column DataPointX, which will throw the above error message. Please debug the code to check the variable dt and view the contents of dt via the DataSet Visualizer to make sure the column DataPointX is existing.

Besides, I use the code that you provided to read data from the below Excel file on my side, and I find the DataPointX will missing if it is number.

//read data from excel, same as yours

excelReader.Close();
dt = result.Tables[0];

if (dt.Rows[0].ItemArray[0].ToString() == "Category Info")
{
    dt.Rows[0].Delete();
    dt.AcceptChanges();
}

GridView1.DataSource = dt;
GridView1.DataBind();

GridView output data

If the column DataPointX are filled with number in your excel file, please try to change cell format to text and test if it could fix the issue.

My test Excel file (data of DataPointX field is number, data of DataPointY field is text)