Import data from Excel into SQL Server

Question

I need to import data from Excel into a SQL Server database. I use ADO to read the excel files.

Sometimes it happens that a row is empty in Excel, which will create an empty row import failure on the SQL Server side.

Any good idea to remove these empty rows or detect during import?

I'm looking for a rather effective code style solution, i show my current solution with the field loop here

function EmptyRow(aQuery: TADOQuery): Boolean;
var
  i: Integer;
  fname: string;
  temp_line: string;
begin

  temp_line := '';
  for i := 0 to aQuery.Fields.Count - 1 do
  begin
    fname := aQuery.Fields[i].FieldName;
    temp_line := temp_line + aQuery.FieldByName(fname).asString;
  end;
  if (temp_line <> '') then
    result := false
  else
    result := true;
end;

If you want help with this, add the code you're using to your q. You can't reasonably expect readers to guess what you're doing. — MartynA
– MartynA, Commented Nov 24, 2015 at 18:39

Jens Borrisholt · Accepted Answer · 2015-11-24 19:52:35Z

2

You could exit the first thime you find a non empty string

function EmptyRow(aQuery: TADOQuery): Boolean;
var
  Field: TField;
begin
  for Field in aQuery.Fields do
    if Field.AsString <> '' then
      exit(false);

  exit(True);
end;

And if you have an older Delphi you could implement it like tihs:

function EmptyRow(aQuery: TADOQuery): Boolean;
var
  I: Integer;
begin
  Result := False;
  for I := 0 to aQuery.Fields.Count - 1 do
    if aQuery.Fields[I].AsString <> '' then
      exit;

  Result := True;
end;

edited Nov 24, 2015 at 19:52

answered Nov 24, 2015 at 19:46

Jens Borrisholt

6,4342 gold badges38 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import data from Excel into SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related