Delphi TQuery保存到csv文件

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.

我想在不使用3d零件组件的情况下将TQuery的内容导出到CSV文件（Delphi 7）。据我所知，使用Delphi标准组件无法实现这一点。

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

我的解决方案是将内容保存在CSV格式的StringList中，并将其保存到文件中。

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?

PS：我不想使用JvCsvDataSet或任何组件。问题是：这只能用Delphi 7或更高标准组件完成吗？

Thank you in advance!

先谢谢你！

4 个解决方案

#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.

您只需要正确输出CSV内容（正确引用，处理嵌入式引号和逗号等）。您可以使用TFileStream轻松编写输出，并使用TQuery.Fields和TQuery.FieldCount正确获取数据。

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

我会留下奇特的CSV报价和特殊处理给你。这将照顾简单的部分：

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;

#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.

原始问题要求使用StringList的解决方案。所以它会更像这样。它适用于任何TDataSet，而不仅仅是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.

您可以添加选项以更改分隔符类型或其他任何内容。

#3

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

Delphi不提供对.csv数据的任何内置访问。但是，遵循VCL TXMLTransform范例，我编写了一个TCsvTransform类助手，它将.csv结构转换为TClientDataSet或从TClientDataSet转换.csv结构。至于将TQuery导出到.csv的初始问题，一个简单的TDataSetProvider将在TQuery和TClientDataSet之间建立链接。有关TCsvTransform的更多详细信息，请参阅http://didier.cabale.free.fr/delphi.htm#uCsvTransform

#4

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.

这就像Rob McDonell解决方案，但有一些增强功能：标题，转义字符，仅在需要时封装，以及“;”分隔器。如果不需要，您可以轻松禁用此增强功能。

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;

#1