DbGrid to CSV

[zsver]

Hello All,

Could somebody can show me a short example how to export a dbgrid to a simple text file (comma separated)?

I did not find any working example in google

Thanks in advance
Zsolt

[OnixJr]

I wrote a simple code (this don't use information from DBGrid, only from DataSet) now:

Code: [Select]


procedure TForm1.Button1Click(Sender: TObject);
var
  I, J: Integer;
  SL: TStringList;
begin
  SL := TStringList.Create;
  Sqlite3Dataset1.First;
  for I := 1 to  Sqlite3Dataset1.RecordCount do
  begin
    SL.Add('');
    Sqlite3Dataset1.RecNo := I;
    for J := 0 to Sqlite3Dataset1.Fields.Count - 1 do
      SL[SL.Count - 1] := SL[SL.Count - 1] + Sqlite3Dataset1.Fields[J].AsString + ';';
  end;
  SL.SaveToFile('C:\test.csv');
  SL.Free;
end;


Keep in mind that in any field cannot have comma, obviously =)

Regards

[OnixJr]

I have noticed only now: I did use semicolon on code, and this work for me (Excel 2003)...
Change semicolon to comma if necessary =)

Regards

[zsver]

Thank you very much for your help
I'll try the code tomorrow.

Regards
Zsolt

[OnixJr]

I has worked in a better code to do this:

Code: [Select]


procedure TForm1.Button1Click(Sender: TObject);
begin
  CreateCSVFile('C:\test.csv');
end;

procedure TForm1.CreateCSVFile(NomeArquivo: String);
const
  Delim = ';'; // change this if necessary
var
  S: String;
  I: Integer;
  Stream: TFileStream;
begin
  Stream := TFileStream.Create(NomeArquivo, fmCreate);

  while not Sqlite3Dataset1.Eof do
  begin
    S := '';

    for I := 0 to DBGrid1.Columns.Count - 1 do
      S := S + '"' + StringReplace(TColumn(DBGrid1.Columns[I]).Field.AsString, '"', '""', []) + '"' + Delim;

    S := S + LineEnding;
    Stream.Write(PChar(S)^, Length(S));
    Sqlite3Dataset1.Next();
  end;

  Stream.Free();
end;


Regards
Júnior

[zsver]

Hi

Your code works fine, thanks again.

In the meantime I found out that the latest lazarus 0.9.25 beta is containing a new component called dbexport which is able to export from TDataSet to CSV, XML, SQL, DBF and some other formats. to compile dbexport component you will need fpc 2.3.1 or if you are using 2.2 series, just comment out the {IF} statement at the beginning of the unit.

Regards
Zsolt

[OnixJr]

In the meantime I found out that the latest lazarus 0.9.25 beta is containing a new component called dbexport which is able to export from TDataSet to CSV, XML, SQL, DBF and some other formats

I didn't have noticed this. Good news =)

Regards,
Júnior

[zsver]

one more result of some google

if we change the delimiter to chr(9) which is TAB, and the lineending to chr(13), and take away the quotes ("), we can create a simple excel file easily.
At least my excel (2003) is opening it like a normal XLS without any additional converting necessary

Code: [Select]


procedure TfrmMain.CreateXLSFile(filename: String);
const
  Delim = CHR(9);
var
  S: String;
  I: Integer;
  Stream: TFileStream;
begin
  Stream := TFileStream.Create(filename, fmCreate);

  while not SQLDataSet.Eof do
  begin
    S := '';

    for I := 0 to DBGrid1.Columns.Count - 1 do
      S := S + TColumn(DBGrid1.Columns[I]).Field.AsString + Delim;

    S := S + CHR(13);
    Stream.Write(PChar(S)^, Length(S));
    SQLDataSet.Next();
  end;

  Stream.Free();
end;      


[bobix]

If you also need to include your column titles:

Code: Pascal  [Select][+][-]

1. procedure TZaqvkiForm.CreateXLSFile(filename: String; Sender: TDBGrid);
2. const
3.   Delim = CHR(9);
4. var
5.   S: String;
6.   I: Integer;
7.   Stream: TFileStream;
8. begin
9.   savedialog1.FileName:=filename;
10.   if savedialog1.Execute then
11.    begin
12.     filename:=savedialog1.FileName;
13.     Stream := TFileStream.Create(filename, fmCreate);
14.     S := '';
15.     for I := 0 to Sender.Columns.Count - 1 do
16.      if Sender.Columns[I].Visible=true then S := S + TColumn(Sender.Columns[I]).Title.Caption + Delim;
17.      S := S + CHR(13);
18.      Stream.Write(PChar(S)^, Length(S));
19.      while not sender.DataSource.DataSet.Eof do
20.       begin
21.        S := '';
22.        for I := 0 to Sender.Columns.Count - 1 do
23.         if Sender.Columns[I].Visible=true then S := S + TColumn(Sender.Columns[I]).Field.AsString + Delim;
24.  
25.        S := S + CHR(13);
26.        Stream.Write(PChar(S)^, Length(S));
27.        sender.DataSource.DataSet.Next();
28.       end;
29.    Stream.Free();
30.    if FileExists(filename) then OpenDocument(filename);
31.   end;
32. end;    
33.