qimport3.pas

Advanced Data Import Component Suite for Borland Delphi and C++ Builder allows you to import your da

  {$ENDIF}
  FIsCSV := True;
  try
    CheckProperties;
    if not Assigned(Stream) then
      raise Exception.Create(sStreamMustBeAssigned);
    FStream := Stream;
    FComma := Comma;
    FQuote := Quote;
    BeforeImport;
    DoBeginImport;
    DoImport;
  finally
    try
      DoEndImport;
      AfterImport;
    finally
      FIsCSV := False;
    end;
  end;
end;

procedure TQImport3.Cancel;
begin
  FCanceled := true;
end;

procedure TQImport3.LoadConfiguration(const FileName: string);
var
  FIniFile: TIniFile;
begin
  if not FileExists(FileName) then Exit;
  FIniFile := TIniFile.Create(FileName);
  try
    DoLoadConfiguration(FIniFile);
  finally
    FIniFile.Free;
  end;
end;

procedure TQImport3.SaveConfiguration(const FileName: string);
var
  FIniFile: TIniFile;
begin
  if Trim(FileName) = EmptyStr then Exit;
  FIniFile := TIniFile.Create(FileName);
  try
    DoSaveConfiguration(FIniFile);
  finally
    FIniFile.Free;
  end;
end;

procedure TQImport3.DoLoadConfiguration(IniFile: TIniFile);
var
  i: integer;
  AStrings: TStrings;
  str: string;
begin
  with IniFile do
  begin
    AStrings := TStringList.Create;
    try
      Self.FileName := ReadString(QI_BASE, QI_FILE_NAME, Self.FileName);
      AStrings.Clear;
      Map.Clear;
      ReadSection(QI_MAP, AStrings);
      for i := 0 to AStrings.Count - 1 do
        Map.Values[AStrings[i]] := ReadString(QI_MAP, AStrings[i], EmptyStr);
      Formats.DecimalSeparator := ReadString(BASE_FORMATS, BF_DECIMAL_SEPARATOR,
        Formats.DecimalSeparator)[1];
      Formats.ThousandSeparator := ReadString(BASE_FORMATS,
        BF_THOUSAND_SEPARATOR, Formats.ThousandSeparator)[1];
      Formats.ShortDateFormat := ReadString(BASE_FORMATS, BF_SHORT_DATE_FORMAT,
        Formats.ShortDateFormat);
      Formats.LongDateFormat := ReadString(BASE_FORMATS, BF_LONG_DATE_FORMAT,
        Formats.LongDateFormat);
      Formats.ShortTimeFormat := ReadString(BASE_FORMATS, BF_SHORT_TIME_FORMAT,
        Formats.ShortTimeFormat);
      Formats.LongTimeFormat := ReadString(BASE_FORMATS, BF_LONG_TIME_FORMAT,
        Formats.LongTimeFormat);
      AStrings.Clear;
      Formats.BooleanTrue.Clear;
      ReadSection(BOOLEAN_TRUE, AStrings);
      for i := 0 to AStrings.Count - 1 do
        Formats.BooleanTrue.Add(AStrings[i]);
      AStrings.Clear;
      Formats.BooleanFalse.Clear;
      ReadSection(BOOLEAN_FALSE, AStrings);
      for i := 0 to AStrings.Count - 1 do
        Formats.BooleanFalse.Add(AStrings[i]);
      AStrings.Clear;
      Formats.NullValues.Clear;
      ReadSection(NULL_VALUES, AStrings);
      for i := 0 to AStrings.Count - 1 do
        Formats.NullValues.Add(AStrings[i]);
      AStrings.Clear;
      FieldFormats.Clear;
      for i := 0 to DestinationColCount - 1 do
      begin
        str := DATA_FORMATS + DestinationColName(i);
        ReadSection(str, AStrings);
        if AStrings.Count = 0 then
          Continue;
        with FieldFormats.Add do
        begin
          FieldName := DestinationColName(i);
          GeneratorValue := ReadInteger(str, DF_GENERATOR_VALUE, 0);
          GeneratorStep := ReadInteger(str, DF_GENERATOR_STEP, 0);
          ConstantValue := ReadString(str, DF_CONSTANT_VALUE, EmptyStr);
          NullValue := ReadString(str, DF_NULL_VALUE, EmptyStr);
          DefaultValue := ReadString(str, DF_DEFAULT_VALUE, EmptyStr);
          LeftQuote := ReadString(str, DF_LEFT_QUOTE, EmptyStr);
          RightQuote := ReadString(str, DF_RIGHT_QUOTE, EmptyStr);
          QuoteAction := TQuoteAction(ReadInteger(str, DF_QUOTE_ACTION, 0));
          CharCase := TQImportCharCase(ReadInteger(str, DF_CHAR_CASE, 0));
          CharSet := TQImportCharSet(ReadInteger(str, DF_CHAR_SET, 0));
        end;
      end;
      CommitAfterDone := ReadBool(QI_BASE, QI_COMMIT_AFTER_DONE,
        CommitAfterDone);
      CommitRecCount := ReadInteger(QI_BASE, QI_COMMIT_REC_COUNT,
        CommitRecCount);
      ImportRecCount := ReadInteger(QI_BASE, QI_IMPORT_REC_COUNT,
        ImportRecCount);
      ErrorLog := ReadBool(QI_BASE, QI_ENABLE_ERROR_LOG, ErrorLog);
      ErrorLogFileName := ReadString(QI_BASE, QI_ERROR_LOG_FILE_NAME,
        ErrorLogFileName);
      RewriteErrorLogFile := ReadBool(QI_BASE, QI_REWRITE_ERROR_LOG_FILE,
        RewriteErrorLogFile);
      ShowErrorLog := ReadBool(QI_BASE, QI_SHOW_ERROR_LOG, ShowErrorLog);
//      SQLLog := ReadBool(QI_BASE, QI_ENABLE_SQL_LOG, SQLLog);
//      SQLLogFileName := ReadString(QI_BASE, QI_SQL_LOG_FILE_NAME,
//        SQLLogFileName);
//      SQLLogFileRewrite := ReadBool(QI_BASE, QI_SQL_LOG_FILE_REWRITE,
//        SQLLogFileRewrite);

      ImportDestination := TQImportDestination(ReadInteger(QI_BASE,
        QI_IMPORT_DESTINATION, Integer(qidDataSet)));
      ImportMode := TQImportMode(ReadInteger(QI_BASE, QI_IMPORT_MODE,
        Integer(qimInsertAll)));
      AStrings.Clear;
      KeyColumns.Clear;
      ReadSection(QI_KEY_COLUMNS, AStrings);
      for i := 0 to AStrings.Count - 1 do
        KeyColumns.Add(AStrings[i]);
      {$IFNDEF NOGUI}
      GridCaptionRow := ReadInteger(QI_BASE, QI_GRID_CAPTION_ROW, -1);
      GridStartRow := ReadInteger(QI_BASE, QI_GRID_START_ROW, -1);
      {$ENDIF}
    finally
      AStrings.Free;
    end;
  end;
end;

procedure TQImport3.DoSaveConfiguration(IniFile: TIniFile);
var
  i: integer;
  str: string;
begin
  with IniFile do
  begin
    WriteString(QI_BASE, QI_FILE_NAME, Self.FileName);
    EraseSection(QI_MAP);
    for i := 0 to Map.Count - 1 do
      WriteString(QI_MAP, Map.Names[i], Map.Values[Map.Names[i]]);
    WriteString(BASE_FORMATS, BF_DECIMAL_SEPARATOR, Formats.DecimalSeparator);
    WriteString(BASE_FORMATS, BF_THOUSAND_SEPARATOR, Formats.ThousandSeparator);
    WriteString(BASE_FORMATS, BF_SHORT_DATE_FORMAT, Formats.ShortDateFormat);
    WriteString(BASE_FORMATS, BF_LONG_DATE_FORMAT, Formats.LongDateFormat);
    WriteString(BASE_FORMATS, BF_SHORT_TIME_FORMAT, Formats.ShortTimeFormat);
    WriteString(BASE_FORMATS, BF_LONG_TIME_FORMAT, Formats.LongTimeFormat);
    EraseSection(BOOLEAN_TRUE);
    for i := 0 to Formats.BooleanTrue.Count - 1 do
      WriteString(BOOLEAN_TRUE, Formats.BooleanTrue[i], EmptyStr);
    EraseSection(BOOLEAN_FALSE);
    for i := 0 to Formats.BooleanFalse.Count - 1 do
      WriteString(BOOLEAN_FALSE, Formats.BooleanFalse[i], EmptyStr);
    EraseSection(NULL_VALUES);
    for i := 0 to Formats.NullValues.Count - 1 do
      WriteString(NULL_VALUES, Formats.NullValues[i], EmptyStr);
    for i := 0 to FieldFormats.Count - 1 do begin
      str := FieldFormats[i].FieldName;
      if Trim(str) = EmptyStr then Continue;
      WriteString(str, DF_GENERATOR_VALUE, FieldFormats[i].FieldName);
      WriteInteger(str, DF_GENERATOR_VALUE, FieldFormats[i].GeneratorValue);
      WriteInteger(str, DF_GENERATOR_STEP, FieldFormats[i].GeneratorStep);
      WriteString(str, DF_CONSTANT_VALUE, FieldFormats[i].ConstantValue);
      WriteString(str, DF_NULL_VALUE, FieldFormats[i].NullValue);
      WriteString(str, DF_DEFAULT_VALUE, FieldFormats[i].DefaultValue);
      WriteString(str, DF_LEFT_QUOTE, FieldFormats[i].LeftQuote);
      WriteString(str, DF_RIGHT_QUOTE, FieldFormats[i].RightQuote);
      WriteInteger(str, DF_QUOTE_ACTION, Integer(FieldFormats[i].QuoteAction));
      WriteInteger(str, DF_CHAR_CASE, Integer(FieldFormats[i].CharCase));
      WriteInteger(str, DF_CHAR_SET, Integer(FieldFormats[i].CharSet));
    end;
    WriteBool(QI_BASE, QI_COMMIT_AFTER_DONE, CommitAfterDone);
    WriteInteger(QI_BASE, QI_COMMIT_REC_COUNT, CommitRecCount);
    WriteInteger(QI_BASE, QI_IMPORT_REC_COUNT, ImportRecCount);
    WriteBool(QI_BASE, QI_ENABLE_ERROR_LOG, ErrorLog);
    WriteString(QI_BASE, QI_ERROR_LOG_FILE_NAME, ErrorLogFileName);
    WriteBool(QI_BASE, QI_REWRITE_ERROR_LOG_FILE, RewriteErrorLogFile);
    WriteBool(QI_BASE, QI_SHOW_ERROR_LOG, ShowErrorLog);
//    WriteBool(QI_BASE, QI_ENABLE_SQL_LOG, SQLLog);
//    WriteString(QI_BASE, QI_SQL_LOG_FILE_NAME, SQLLogFileName);
//    WriteBool(QI_BASE, QI_SQL_LOG_FILE_REWRITE, SQLLogFileRewrite);

    WriteInteger(QI_BASE, QI_IMPORT_DESTINATION, Integer(ImportDestination));
    WriteInteger(QI_BASE, QI_IMPORT_MODE, Integer(ImportMode));
    EraseSection(QI_KEY_COLUMNS);
    for i := 0 to KeyColumns.Count - 1 do
      WriteString(QI_KEY_COLUMNS, KeyColumns[i], EmptyStr);
    {$IFNDEF NOGUI}
    WriteInteger(QI_BASE, QI_GRID_CAPTION_ROW, GridCaptionRow);
    WriteInteger(QI_BASE, QI_GRID_START_ROW, GridStartRow);
    {$ENDIF}
  end;
end;

procedure TQImport3.DestinationInsert;
{$IFNDEF NOGUI}
var
  i: Integer;
{$ENDIF}
begin
  if IsCSV then Exit;
  case ImportDestination of
    qidDataSet:
      case AddType of
        qatAppend: DataSet.Append;
        qatInsert: DataSet.Insert;
      end;
    {$IFNDEF NOGUI}
    qidDbGrid:
      case AddType of
        qatAppend: DBGrid.DataSource.DataSet.Append;
        qatInsert: DBGrid.DataSource.DataSet.Insert;
      end;
    qidListView: begin
      FCurrListItem := ListView.Items.Add;
      for i := 1 to ListView.Columns.Count - 1 do
        FCurrListItem.SubItems.Add(EmptyStr);
    end;
    qidStringGrid: begin
      if FCurrStrGrRow = StringGrid.RowCount then
        StringGrid.RowCount := StringGrid.RowCount + 1;
      if FCurrStrGrRow = -1
        then FCurrStrGrRow := StringGrid.RowCount - 1;
    end;
    {$ENDIF}
  end;
  FLastAction := qiaInsert;
end;

procedure TQImport3.DestinationEdit;
begin
  if IsCSV then Exit;
  case ImportDestination of
    qidDataSet: DataSet.Edit;
    {$IFNDEF NOGUI}
    qidDbGrid: DBGrid.DataSource.DataSet.Edit;
    {$ENDIF}
  end;
  FLastAction := qiaUpdate;
end;

procedure TQImport3.DestinationDelete;
{$IFNDEF NOGUI}
var
  i: integer;
{$ENDIF}
begin
  if IsCSV then Exit;
  case ImportDestination of
    qidDataSet: DataSet.Delete;
    {$IFNDEF NOGUI}
    qidDbGrid: DBGrid.DataSource.DataSet.Delete;
    qidListView:
      if Assigned(FCurrListItem) then FCurrListItem.Delete;
    qidStringGrid:
      if (FCurrStrGrRow > -1) and (FCurrStrGrRow < StringGrid.RowCount) then
      begin
        for i := FCurrStrGrRow to StringGrid.RowCount - 2 do
          StringGrid.Rows[i].Assign(StringGrid.Rows[i+1]);
        StringGrid.RowCount:= StringGrid.RowCount - 1;
      end;
    {$ENDIF}
  end;
  FLastAction := qiaDelete;
end;

procedure TQImport3.DestinationSetValues;
var
  i: Integer;
  wstr: WideString;
  str: AnsiString;
  errorMsg: string;
begin
  for i := 0 to FImportRow.Count - 1 do
  begin
    if FIsCSV then
    begin
      wstr := FImportRow[i].Value;

      if QIPos(',', wstr) > 0 then
        wstr := QIStringReplace(wstr, ',', '.', [rfReplaceAll]);

      if FQuote <> #0 then
        wstr := QIQuotedStr(wstr, qiChar(FQuote));

      if i = FImportRow.Count - 1 then
        wstr := wstr + LF
      else
        wstr := wstr + qiChar(FComma);

      if Assigned(FOnWideStringToCharset) then
        FOnWideStringToCharset(Self, wstr, str)
      else
        str := AnsiString(wstr);

      FStream.Write(str[1], Length(str));
      Continue;
    end;
    
    case ImportDestination of
      qidDataSet:
        begin
          errorMsg := StringToField(DataSet.Fields[FImportRow[i].FColumnIndex],
            FImportRow[i].Value, FImportRow[i].IsBinary);
          if errorMsg <> EmptyStr then
            WriteErrorLog(errorMsg);
        end;
{$IFNDEF NOGUI}
      qidDBGrid:
        begin
          errorMsg := StringToField(DBGrid.Columns[FImportRow[i].FColumnIndex].Field,
            FImportRow[i].Value, FImportRow[i].IsBinary);
          if errorMsg <> EmptyStr then
            WriteErrorLog(errorMsg);
        end;
      qidListView:
        if Assigned(FCurrListItem) then
        begin
          if FImportRow[i].FColumnIndex = 0 then
            FCurrListItem.Caption := FImportRow[i].Value
          else FCurrListItem.SubItems[FImportRow[i].FColumnIndex - 1] :=
              FImportRow[i].Value;
        end;
      qidStringGrid:
        if FCurrStrGrRow > -1 then
          StringGrid.Cells[FImportRow[i].FColumnIndex, FCurrStrGrRow] :=
            FImportRow[i].Value;
{$ENDIF}
    end;
  end;
end;

procedure TQImport3.DestinationPost;
begin
  if IsCSV then Exit;
  case ImportDestination of
    qidDataSet:
      begin
        if DataSet.State in [dsInsert, dsEdit] then
          DataSet.Post;
      end;
{$IFNDEF NOGUI}
    qidDbGrid:
      if DBGrid.DataSource.DataSet.State in [dsInsert, dsEdit] then
        DBGrid.DataSource.DataSet.Post;
    qidStringGrid:
      Inc(FCurrStrGrRow);
{$ENDIF}
  end;
end;

procedure TQImport3.DestinationCancel;
begin
  if IsCSV then Exit;
  case ImportDestination of
    qidDataSet: if DataSet.State in [dsInsert, dsEdit] then DataSet.Cancel;
    {$IFNDEF NOGUI}
    qidDbGrid: if DBGrid.DataSource.DataSet.State in [dsInsert, dsEdit] then
      DBGrid.DataSource.DataSet.Cancel;
    {$ENDIF}
  end;
end;

function TQImport3.DestinationFindColumn(const ColName: string): integer;
var
  Field: TField;
{$IFNDEF NOGUI}
  i: integer;
{$ENDIF}
begin
  Result := -1;
  //if FIsCSV then Exit;
  case ImportDestination of
    qidDataSet: begin
      Field := DataSet.FindField(ColName);
      if Assigned(Field) then
        Result := Field.Index;
    end;
    {$IFNDEF NOGUI}
    qidDBGrid:
      for i := 0 to DBGrid.Columns.Count - 1 do
        if AnsiCompareText(DBGrid.Columns[i].Title.Caption, ColName) = 0 then
        begin
          Result := i;
          Exit;
        end;
    qidListView:
      for i := 0 to ListView.Columns.Count - 1 do
        if AnsiCompareText(ListView.Columns[i].Caption, ColName) = 0 then
        begin
          Result := i;
          Exit;
        end;
    qidStringGrid: begin
      i := StrToIntDef(ColName, -1);
      if i > -1 then