Exporting DBgrid to CSV?

Mawg says reinstate Monica picture Mawg says reinstate Monica · Jan 18, 2013 · Viewed 8k times · Source

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)

Answer

Andreas picture Andreas · Jan 18, 2013

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()