Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

skip first row in read of Excel file

Hello I'm trying to translate an Excel file to my dataGridView and it's having column name issues because the way the Excel file is formatted, there are two setup cells for the rest of the document. However the Column names are actually on Row #2. How can I skip the first row in the file read so that the Columns in the dataGridView show the cell values from the second row?

Current code:

  var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 8.0;", openFileDialog1.FileName);

string query = String.Format("select * from [{0}$]", "Sheet1");
var adapter = new OleDbDataAdapter(query, connectionString);

DataSet ds = new DataSet();

adapter.Fill(ds);

DataTable dt = ds.Tables[0];

techGrid.DataSource = dt;
like image 764
ikathegreat Avatar asked Mar 15 '12 02:03

ikathegreat


People also ask

How do I skip the first row in Excel?

Select the header or the first row of your list and press Shift + Ctrl + ↓(the drop down button), then the list has been selected except the first row.

How do I skip the first row in Excel using Apache POI?

getSheet(SHEET); Iterator<Row> rows = sheet. iterator(); List<Product> entities = new ArrayList<Product>(); int rowNumber = 0; while (rows. hasNext()) { Row currentRow = rows. next(); if (rowNumber == 0) { rowNumber++; continue; } Iterator<Cell> cellsInRow = currentRow.

How do I skip the first row in Excel while reading in Python?

If you want to skip the number of n rows from the top, you need to use the skiprows parameter. Let's say we want to skip the first 2 rows when reading the file. We can see that top two rows has been skipped from the result. And if you want to skip n rows from the end you can use the skipfooter parameter.

How do I skip a column in a read in Excel?

Skip Columns From Excel Sheet Sometimes while reading an excel sheet into pandas DataFrame you may need to skip columns, you can do this by using usecols param. This takes values {int, str, list-like, or callable default None}.


2 Answers

The correct method is to tell Excel exactly where in the worksheet to find your column headers and data. Importing the entire sheet and trying to reconstruct your headers from an arbitrary data row is asking for serious trouble. OPENQUERY does not guarantee row order. In testing it will appear to always import in order, but as soon as you move it to a system with a multi-volume tempdb or a heavily loaded production system, your imports will no longer be ordered, and your code will be trying to interpret your data as column headers.

instead of:

string query = String.Format("select * from [{0}$]", "Sheet1");

use:

string query = String.Format("select * from [{0}${1}]", "Sheet1","A2:ZZ");

EDIT: use "A2:end" instead of "A2:ZZ".

like image 165
Anon Avatar answered Oct 02 '22 06:10

Anon


There is an easier way than programatically removing the rows, use the header row property of the connection string. This should skip the first row for you and you can then do what you do with the rest of the rows from there. From ConnectionStrings.com:

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=myOldExcelFile.xls; 
Extended Properties="Excel 12.0;HDR=YES";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

like image 42
Tommy Avatar answered Oct 02 '22 07:10

Tommy