Distinct Value Check     

{This script allows you to interrogate any column in a Spreadsheet and returns the distinct values within that column.  This is very useful when importing data into Ostendo where certain fields in Ostendo should be pre-populated with values from the spreadsheet.}

var

OpenDlg: TOpenDialog;

x,ColumnNumber: Integer;

ImportFile: String;

OutputList: TStringlist;

TempList: TStringList;

function GetImportFile: string;

var

OpenDlg: TOpenDialog;

begin

OpenDlg := TOpenDialog.Create(Nil);

try

   OpenDlg.InitialDir := 'C:\';

   OpenDlg.Filter := 'Excel Files (*.csv)|*.csv|All Files (*.*)|*.*';

   if OpenDlg.Execute then

     Result := OpenDlg.filename

   else

     Result := '';

finally

  OpenDlg.Free;

end;

end;

{Main Code Section}

begin

OutputList := TStringList.Create;

TempList := TStringList.create;

ImportFile := GetImportFile;

if trim(ImportFile) = '' then exit;

try

   ColumnNumber := AskQuestion('Column Number','INTEGER','Please enter the Column Number','');

   TempList.LoadFromFile(ImportFile);

   for x := 0 to TempList.Count - 1 do

    begin

     if OutputList.IndexOf(trim(ParseString(TempList.Strings[x],',',(ColumnNumber - 1)))) < 0 then

      begin

       OutputList.Add(trim(ParseString(TempList.Strings[x],',',(ColumnNumber - 1))));

      end;

    end;

   OutputList.SaveToFile('c:\DistinctValues.txt');

   run('c:\DistinctValues.txt');

finally

   TempList.free;

   OutputList.free;

end;

end.