TADOQuery Including blank rows

Question

When using the 'while not TADOQuery.Eof' with an microsoft Excel Workbook, it's including rows which are completely empty. Is there a way to stop including any rows that are completely blank as I don't need them?

Answer 1

You could exclude blank lines in the SQL used to open the spreadsheet. If the first row contains column headings like 'Column1', 'Column2', etc then the following SQL will not return rows where the value in the first column is blank

select * from [sheet1$] 
where Column1 <> ''

Obviously the SQL could be a bit more specific (in terms of column values) about what you regard as constituting a blank row.

You'll have gathered that there are various ways to deal with variations in the contents of the column headers, but as the other answer shows, these are likely to be far more verbose than simply skipping blank rows inside the body of your main while not EOF loop to read the table contents, so I can't really see any benefit to not doing it by just skipping the blank rows.

Btw, ime the Excel data accessible via SQL behaves as though the query is automatically restricted to the UsedRange range in the Excel COM interface.

Answer 2

Original answer:

If I understand you correctly and you want to exclude empty rows after the query is opened, then next approach may help (but I think, that you should exclude these rows with SQL statement, as in @MartynA's answer). Here, empty rows are all rows, which have Null value for all fields.

procedure TForm1.btnDataClick(Sender: TObject);
var
   i: Integer;
   empty: Boolean;
begin

   qry.First;
   while not qry.Eof do begin
      // Check for empty row. Row is empty if all fields have NUull value.
      empty := True;
      for i := 0 to qry.FieldCount - 1 do begin
         if not qry.Fields[i].IsNull then begin
            empty := False;
            Break;
         end{if};
      end{for};
      // Read record data if record is not empty
      if not empty then begin
         // Your code here ...
      end{if};
      // Next record
      qry.Next;
   end{while};

end;

Update:

It's an attempt to improve my answer. If the table structure is not known, you can query the table with always false WHERE clause to get this structure and generate an SQL statement dynamically:

procedure TForm1.btnDataClick(Sender: TObject);
var
    i: Integer;
    where: string;
begin
    // Get column names
    qry.Close;
    qry.SQL.Clear;
    qry.SQL('SELECT * FROM [SheetName] WHERE 1 = 0');
    try
       qry.Open;
    except
       ShowMessage('Error');
    end{try};
    where := '';
    for i := 0 to qry.FieldCount - 1 do begin
        where := where + '(' + qry.Fields[i].FieldName + ' <> '''') AND ';
    end{for};
    where := 'WHERE ' + Copy(where, 1, Length(where) - 5);

    // Read data without "empty" rows
    qry.Close;
    qry.SQL.Clear;
    qry.SQL('SELECT * FROM [SheetName] ' + where);
    try
       qry.Open;
    except
       ShowMessage('Error');
    end{try};

end;