TDataSetHelper - Export to CSV

[bogdanpolak]

TDataSetHelper:

1. export to csv file - simple
2. export to csv file - advanced - BCDFields, BlobFields, etc.

function TDataSetHelper.ExportToCsvString(const aDisableControl
  : boolean = false): string;
var
  sb: TStringBuilder;
  idx: Integer;
  fld: TField;
begin
  if not self.Active then
    raise Exception.Create
      ('DataSet in not active. Please open connection first.');
  if aDisableControl then
  begin
    self.DisableControls;
  end;
  self.First;
  sb := TStringBuilder.Create;
  try
    for idx := 0 to self.FieldCount - 1 do
    begin
      fld := self.Fields[idx];
      sb.Append(IfThen(idx > 0, ',' + fld.FieldName, fld.FieldName));
    end;
    sb.AppendLine();
    while not self.Eof do
    begin
      for idx := 0 to self.FieldCount - 1 do
      begin
        fld := self.Fields[idx];
        if (idx > 0) then
          sb.Append(',');
        if fld.IsNull then
          Continue;
        case fld.DataType of
          ftString, ftFixedChar, ftFixedWideChar, ftWideString:
            sb.Append(TFormatCsv.Text(fld.AsWideString));
          ftSmallint, ftInteger, ftWord, ftAutoInc, ftLargeint, ftLongWord,
            ftShortint, ftByte:
            sb.Append(fld.AsString);
          ftCurrency:
            sb.Append(Format('%.2f', [fld.AsCurrency],
              TFormatSettings.Invariant));
          ftFloat, ftExtended, ftSingle:
            sb.Append(Format('%f', [fld.AsCurrency],
              TFormatSettings.Invariant));
          ftDate, ftTime, ftDateTime:
            sb.Append(TFormatCsv.DateTime(fld.AsDateTime));
          // ftBoolean, ftBCD, ftBytes, ftVarBytes, ftBlob, ftMemo, ftGraphic,
          // ftFmtMemo, ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftADT,
          // ftArray, ftReference, ftDataSet, ftOraBlob, ftOraClob, ftVariant,
          // ftInterface, ftIDispatch, ftGuid, ftTimeStamp, ftFMTBcd, ftWideMemo,
          // ftOraTimeStamp, ftOraInterval, ftConnection, ftParams, ftStream,
          // ftTimeStampOffset, ftObject, ftSingle:
        else
          raise Exception.Create(Format('Unsupported field in dataset: %s',
            [fld.FieldName]))
        end;
      end;
      sb.AppendLine();
      self.Next;
    end;
    Result := sb.ToString;
  finally
    self.First;
    self.EnableControls;
    sb.Free;
  end;
end;

{ TFormatCsv }

class function TFormatCsv.DateTime(const dt: TDateTime): string;
var
  hasTime: boolean;
begin
  hasTime := dt <> Int(dt);
  if hasTime then
    Result := DateToISO8601(dt, false)
  else
    Result := FormatDateTime('yyyy-mm-dd', dt);
end;

class function TFormatCsv.Text(const s: string): string;
begin
  if s.Contains('"') then
    raise Exception.Create('Quotes indside text data are not supported now.');
  if s.Contains(',') or s.Contains(#10) or s.Contains(#13) then
    Result := Format('"%s"', [s])
  else
    Result := s;
end;