Recently I came to the point where I had to export the tables of a database as CSV files. While in FireDAC there is TFDDataMove, I wasn’t able to use it as I encountered a bug in that component that rendered the output useless. (The bug has been fixed by Dmitry the day after I mentioned it, but is not publicly available yet.)
So I wondered how such a simple CSV export can be done with things available in Delphi. Well, here it is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
procedure ExportDataSetToCVS(DataSet: TDataSet; const FileName: string); var fld: TField; lst: TStringList; wasActive: Boolean; writer: TTextWriter; begin writer := TStreamWriter.Create(FileName); try lst := TStringList.Create; try lst.QuoteChar := '"'; lst.Delimiter := ';'; wasActive := DataSet.Active; try DataSet.Active := true; DataSet.GetFieldNames(lst); writer.WriteLine(lst.DelimitedText); DataSet.First; while not DataSet.Eof do begin lst.Clear; for fld in DataSet.Fields do lst.Add(fld.Text); writer.WriteLine(lst.DelimitedText); DataSet.Next; end; finally DataSet.Active := wasActive; end; finally lst.Free; end; finally writer.Free; end; end; |
This method works with any TDataSet descendant.
It uses the Text property of TField to get a string representation of the field value – and this is the point where you can hook in to adjust any fields output to your needs. Let’s say, we have a blob field to export. The OnGetText event handler for that field can look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
uses Soap.EncdDecd; procedure TForm254.Table1BMPGetText(Sender: TField; var Text: string; DisplayText: Boolean); var byteStream: TBytesStream; stringStream: TStringStream; begin stringStream := TStringStream.Create; try byteStream := TBytesStream.Create; try (Sender as TBlobField).SaveToStream(byteStream); byteStream.Position := 0; EncodeStream(byteStream, stringStream); finally byteStream.Free; end; Text := StringReplace(stringStream.DataString, #13#10, '', [rfReplaceAll]); finally stringStream.Free; end; end; |
The CR/LF sequences inside the Base64 coded string are removed because many CSV readers cannot cope with linebreaks inside quotes. As Base64 doesn’t need these linebreaks, we can do this safely in this case, but it might be an issue in others. As it is difficult to give a one-for-all solution for this problem, the OnGetText event is a good place to make the string representation fit your needs.
Next time I will show a similar approach for importing CSV files. Then I will have a closer look on AutoInc fields and references to them. Stay tuned…
