Delphi TQuery save to csv file

30,578

Solution 1

Of course it can.

You just have to do the work to properly output the CSV content (quoting properly, handling embedded quotes and commas, etc.). You can easily write the output using TFileStream, and get the data using the TQuery.Fields and TQuery.FieldCount properly.

I'll leave the fancy CSV quoting and special handling to you. This will take care of the easy part:

var
  Stream: TFileStream;
  i: Integer;
  OutLine: string;
  sTemp: string;
begin
  Stream := TFileStream.Create('C:\Data\YourFile.csv', fmCreate);
  try
    while not Query1.Eof do
    begin
      // You'll need to add your special handling here where OutLine is built
      OutLine := '';
      for i := 0 to Query.FieldCount - 1 do
      begin
        sTemp := Query.Fields[i].AsString;
        // Special handling to sTemp here
        OutLine := OutLine + sTemp + ',';
      end;
      // Remove final unnecessary ','
      SetLength(OutLine, Length(OutLine) - 1);
      // Write line to file
      Stream.Write(OutLine[1], Length(OutLine) * SizeOf(Char));
      // Write line ending
      Stream.Write(sLineBreak, Length(sLineBreak));
      Query1.Next;
    end;
  finally
    Stream.Free;  // Saves the file
  end;
end;

Solution 2

The original question asked for a solution using a StringList. So it would be something more like this. It will work with any TDataSet, not just a TQuery.

procedure WriteDataSetToCSV(DataSet: TDataSet, FileName: String);
var
  List: TStringList;
  S: String;
  I: Integer;
begin
  List := TStringList.Create;
  try
    DataSet.First;
    while not DataSet.Eof do
    begin
      S := '';
      for I := 0 to DataSet.FieldCount - 1 do
      begin
        if S > '' then
          S := S + ',';
        S := S + '"' + DataSet.Fields[I].AsString + '"';
      end;
      List.Add(S);
      DataSet.Next;
    end;
  finally
    List.SaveToFile(FileName);
    List.Free;
  end;
end;

You can add options to change the delimiter type or whatever.

Solution 3

This is like the Rob McDonell solution but with some enhancements: header, escape chars, enclosure only when required, and ";" separator. You can easily disable this enhancements if not required.

procedure SaveToCSV(DataSet: TDataSet; FileName: String);
const
  Delimiter: Char = ';'; // In order to be automatically recognized in Microsoft Excel use ";", not ","
  Enclosure: Char = '"';
var
  List: TStringList;
  S: String;
  I: Integer;
  function EscapeString(s: string): string;
  var
    i: Integer;
  begin
    Result := StringReplace(s,Enclosure,Enclosure+Enclosure,[rfReplaceAll]);
    if (Pos(Delimiter,s) > 0) OR (Pos(Enclosure,s) > 0) then  // Comment this line for enclosure in every fields
        Result := Enclosure+Result+Enclosure;
  end;
  procedure AddHeader;
  var
    I: Integer;
  begin
    S := '';
    for I := 0 to DataSet.FieldCount - 1 do begin
      if S > '' then
        S := S + Delimiter;
      S := S + EscapeString(DataSet.Fields[I].FieldName);
    end;
    List.Add(S);
  end;
  procedure AddRecord;
  var
    I: Integer;
  begin
    S := '';
    for I := 0 to DataSet.FieldCount - 1 do begin
      if S > '' then
        S := S + Delimiter;
      S := S + EscapeString(DataSet.Fields[I].AsString);
    end;
    List.Add(S);
  end;
begin
  List := TStringList.Create;
  try
    DataSet.DisableControls;
    DataSet.First;
    AddHeader;  // Comment if header not required
    while not DataSet.Eof do begin
      AddRecord;
      DataSet.Next;
    end;
  finally
    List.SaveToFile(FileName);
    DataSet.First;
    DataSet.EnableControls;
    List.Free;
  end;
end;

Solution 4

Delphi does not provide any built-in access to .csv data. However, following the VCL TXMLTransform paradigm, I wrote a TCsvTransform class helper that will translate a .csv structure to /from a TClientDataSet. As for the initial question that was to export a TQuery to .csv, a simple TDataSetProvider will make the link between TQuery and TClientDataSet. For more details about TCsvTransform, cf http://didier.cabale.free.fr/delphi.htm#uCsvTransform

Share:
30,578

Related videos on Youtube

RBA
Author by

RBA

Updated on June 21, 2020

Comments

  • RBA
    RBA over 3 years

    I want to export content of a TQuery to a CSV file without using a 3d part component(Delphi 7). From my knowledge this can not be accomplished with Delphi standard components.

    My solution was to save the content in a StringList with a CSV format, and save it to a file.

    Is there any comfortable solution?

    PS:I don't want to use JvCsvDataSet or any component. Question is: can this be accomplished only with Delphi 7 or higher standard components?

    Thank you in advance!

    • David Heffernan
      David Heffernan over 12 years
      It's pretty simple to write a CSV file emitter. Since you can't find functionality built in, and since you don't want 3rd party solutions, then you'll likely need to go down this route.
  • jachguate
    jachguate over 12 years
    Some information on how to escape the sTemp: csvreader.com/csv_format.php
  • Edelcom
    Edelcom over 12 years
    @Radu Barbu: be aware of date formatting when importing a csv into a spreadsheet ( I am assuming you want to do this ). F.i. Excel tries to guess the date based on the input, so please make sure your dates are handled correctly ( if present of course ). Try explicitly converting them to 'dd-mmm-yyyy' format if you have problems.
  • David Heffernan
    David Heffernan over 12 years
    Question did not ask for a solution using TStringList. It just asked for a solution that didn't use 3rd party libraries.
  • Giorgio Calzolato
    Giorgio Calzolato about 8 years
    Fix: required semicolon after TDataSet, not comma. "procedure WriteDataSetToCSV(DataSet: TDataSet; FileName: String);"

Related