StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Pre-requisite: you can easily determine in your code what the maximum number number of rows is.</p> <p>Assuming (1) there's a big overhead per SELECT, so SELECTing a row at a time is slow (2) SELECTing 64K or 8M rows (even if blank) is slow ... so you want to see if somewhere in the middle can be faster. Try this:</p> <p>Select CHUNKSIZE (e.g. 100 or 1000) rows at a time (less when you would otherwise over-run MAX_ROWS). Scan each chunk for the blank row that marks end-of-data.</p> <p><strong>UPDATE: Actually answering the explicit questions:</strong></p> <p><em>Q: Does anyone know of a way to write a query that says either;</em></p> <p><em>Q1: 'select everything down and right of B14'?</em></p> <p>A1: <code>select * from [Sheet1$B12:]</code> doesn't work. You would have to do <code>...B12:IV</code> in Excel 2003 and whatever it is in Excel 2007. However you don't need that because you know what your rightmost column is; see below.</p> <p><em>Q2: 'select everything in columns B->D'</em></p> <p>A2: <code>select * from [Sheet1$B:D]</code></p> <p><em>Q3: 'select B12:D<code>*</code>' where <code>*</code> means 'everything you can'</em></p> <p>A3: select * from [Sheet1$B12:D]</p> <p>Tested with Python 2.5 using the following code:</p> <pre><code>import win32com.client import sys filename, sheetname, range = sys.argv[1:4] DSN= """ PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=%s; Extended Properties='Excel 8.0;READONLY=true;IMEX=1'; """ % filename conn = win32com.client.Dispatch("ADODB.Connection") conn.Open(DSN) rs = win32com.client.Dispatch("ADODB.Recordset") sql = ( "SELECT * FROM [Excel 8.0;HDR=NO;IMEX=1;Database=%s;].[%s$%s]" % (filename, sheetname, range) ) rs.Open(sql, conn) nrows = 0 while not rs.EOF: nrows += 1 nf = rs.Fields.Count values = [rs.Fields.Item(i).Value for i in xrange(nf)] print nrows, values if not any(value is not None for value in values): print "sentinel found" break rs.MoveNext() rs.Close() conn.Close() </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload