2

I have a DB grid which is sorted (the user clicked a few radio buttons and checkboxes to influence the display).

I would like to export all of the data (not just what is visible in the grid), sorted identically, to CSV - how do I do so? The data - not the user settings, just to clarify.

Thanks in advance for any help


[Update] I build sqlQuery bit by bit, depending on the user's settings of checkboxes & radio groups, then, when one of them changes, I

   ActivityADQuery.SQL.Clear();
   ActivityADQuery.SQL.Add(sqlQuery);
   ActivityADQuery.Open(sqlQuery);

That is to say that there isn't a hard coded query, it varies and I want to export the current settings.

I don't know enough if I want to export from the grid or the dataset (I am just not a db guy, this is my first DBgrid), but I suspect that I want the grid, because it has a subset of fields of he dataset.

I guess that TJvDBGridCSVExport is a Jedi component(?) I have tried to avoid them so far, great as they sound, because I prefer discreet, stand-alone, components to installing a huge collection. That may not be the cleverest thing to do, but it's how I feel - ymmv (and prolly does)

Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
  • 1
    tried TJvDBGridCSVExport ? – Arioch 'The Jan 18 '13 at 08:25
  • 1
    How is the order of the data implemented? The related DataSet should have the same order as shown by the Grid and you only have to export the DataSet to CSV – Sir Rufo Jan 18 '13 at 08:27
  • 2
    @Arioch'The does TJvDBGridCSVExport export all the data from the related DataSet or only that shown by Grid? – Sir Rufo Jan 18 '13 at 08:30
  • 1
    Please clarify: Do you want to export the Data or the Settings or both? – Sir Rufo Jan 18 '13 at 08:43
  • 1
    `with Grid.DataSource.DataSet do begin`. And if it would only export data from grid itself, it would hardly request DBGrid - just StringGrid would suffice. Well, u may dislike big libraries - but u may minimize them as well, cut the slack. But before doing it u may find that many of ur custom routines were in the lib already. Like when i worked on TDbf i found a bunch of copypasted routines that were in RxLib, JCL, vgLib and almost any FLOSS libof the time :-) PS. `SQL.Clear(); SQL.Add` why ?why not just `SQL.Text := ...` ? ;-) – Arioch 'The Jan 18 '13 at 09:33
  • 1
    Tqake some TDataSet wit hCSV back-end and just copythe very TDataSet into it row by row. I don't remember if TBatchMove was BDE-bound or not, but i believe there are a lot of samples cloning one TDataSet agaisnt another. Clone `.FieldDefs` then clone the rows and that must be it. One of possible starting points to find CSV datasets http://torry.net/pages.php?id=567 – Arioch 'The Jan 18 '13 at 09:46

2 Answers2

3

Another solution, works also with (multi)selected rows:

procedure TReportsForm.ExportToCSV(const aGrid : TDBGrid; const FileName : String);
Var
  I, J : Integer;
  SavePlace : TBookmark;
  Table : TStrings;
  HeadTable : String;
  LineTable : String;
  First : Boolean;
Begin

  HeadTable := '';
  LineTable := '';
  Table := TStringList.Create;
  First := True;

  Try
    For I := 0 To Pred(aGrid.Columns.Count) Do
      If aGrid.Columns[I].Visible Then
        If First Then
        Begin
// Use the text from the grid, in case it has been set programatically
// E.g., we prefer to show "Date/time" than "from_unixtime(activity.time_stamp, "%D %b %Y  %l:%i:%S")"
//          HeadTable := HeadTable + aGrid.Columns[I].FieldName;
          HeadTable := HeadTable + ActivityReportStringGrid.Columns[i].Title.Caption + ','; // Previous separated wth semi-colon, not comma! (global)
          First := False;
        End
        Else
        begin
//          HeadTable := HeadTable + ';' + aGrid.Columns[I].FieldName;
          HeadTable := HeadTable + ActivityReportStringGrid.Columns[i].Title.Caption + ',';
        end;

    Delete(HeadTable, Length(HeadTable), 1);  // Remove the superfluous trailing comma
    Table.Add(HeadTable);
    First := True;

    // with selection of rows
    If aGrid.SelectedRows.Count > 0 Then
    Begin
      For i := 0 To aGrid.SelectedRows.Count - 1 Do
      Begin
        aGrid.DataSource.Dataset.GotoBookmark(pointer(aGrid.SelectedRows.Items[i]));
        For j := 0 To aGrid.Columns.Count - 1 Do
          If aGrid.Columns[J].Visible Then
            If First Then
            Begin
              lineTable := lineTable + aGrid.Fields[J].AsString;
              First := False;
            End
            Else
              lineTable := lineTable + ',' + aGrid.Fields[J].AsString;

        Delete(LineTable, Length(LineTable), 1);  // Remove the superfluous trailing comma
        Table.Add(LineTable);
        LineTable := '';
        First := True;
      End;
    End
    Else
      //no selection
    Begin
      SavePlace := aGrid.DataSource.Dataset.GetBookmark;
      aGrid.DataSource.Dataset.First;

      Try
        While Not aGrid.DataSource.Dataset.Eof Do
        Begin
          For I := 0 To aGrid.Columns.Count - 1 Do
            If aGrid.Columns[I].Visible Then
              If First Then
              Begin
                lineTable := lineTable + aGrid.Fields[I].AsString;
                First := False;
              End
              Else
                lineTable := lineTable + ',' + aGrid.Fields[I].AsString;


          Delete(LineTable, Length(LineTable), 1);  // Remove the superfluous trailing comma
          Table.Add(LineTable);
          LineTable := '';
          aGrid.DataSource.Dataset.Next;
          First := True;
        End;

        aGrid.DataSource.Dataset.GotoBookmark(SavePlace);
      Finally
        aGrid.DataSource.Dataset.FreeBookmark(SavePlace);
      End;
    End;
    Table.SaveToFile(FileName);
  Finally
    Table.Free;
  End;
End;  // ExportToCSV()
Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
Andreas
  • 1,334
  • 1
  • 10
  • 21
1

You could use a own tiny procedure wich could be adapted to your needs

Procedure Dataset2SeparatedFile(ads: TDataset; const fn: String; const Separator: String = ';');
var
  sl: TStringList;
  s: String;
  i: Integer;
  bm: TBookmark;

  Procedure ClipIt;
  begin
    s := Copy(s, 1, Length(s) - Length(Separator));
    sl.Add(s);
    s := '';
  end;
  Function FixIt(const s: String): String;
  begin
    // maybe changed
    Result := StringReplace(StringReplace(StringReplace(s, Separator, '', [rfReplaceAll]), #13, '', [rfReplaceAll]), #10, '', [rfReplaceAll]);
    // additional changes could be Quoting Strings
  end;

begin
  sl := TStringList.Create;
  try
    s := '';
    For i := 0 to ads.FieldCount - 1 do
    begin
      if ads.Fields[i].Visible then
        s := s + FixIt(ads.Fields[i].DisplayLabel) + Separator;
    end;
    ClipIt;
    bm := ads.GetBookmark;
    ads.DisableControls;
    try
      ads.First;
      while not ads.Eof do
      begin
        For i := 0 to ads.FieldCount - 1 do
        begin
          if ads.Fields[i].Visible then
            s := s + FixIt(ads.Fields[i].DisplayText) + Separator;
        end;
        ClipIt;
        ads.Next;
      end;
      ads.GotoBookmark(bm);
    finally
      ads.EnableControls;
      ads.FreeBookmark(bm);
    end;
    sl.SaveToFile(fn);
  finally
    sl.Free;
  end;
end;
bummi
  • 27,123
  • 14
  • 62
  • 101