Hello AD,
This is a quick and "disrty" answer because I'm soon leaving home for a motorcycle ride with friends (Sunday morning here).
For the first part of your question : exporting multiple query to 1 csv file. This can not work the way you do it because the CSV file you save to is created for each query, the last one overwriting the previous one, thus you only have part of the result in your csv file.
One workaround would be to pass to your export procedure, not a Filename, but a TStringList.
This list is created BEFORE calling the export procedure, thus each results are appended to it, and destroyed after the export. This way, all data of all queries are in the StringList and saved to the same file.
I made some changes to your code as well :
- I deleted the image field from the second query because the result was ugly in the csv file (but you can add it back if needed)
- I hard-coded the export filename because I lacked time to do it properly.
The whole process is now
1 - Create the StringList
2 - Call export procedure that append it's results to the StringList as many times as needed
3 - Save the StringList to file
4 - Cleanup everything and end.
Export Procedure :
Procedure ExportToSl (SQL: string; Sl: TstringList);
var
Results: TDataSet;
i,c: integer;
sValue, sValues: string;
begin
SQLQuery(sql, Results);
c := Results.FieldCount-1;
while not Results.eof do
begin
sValues := '';
for i := 0 to c do
begin
sValue := Results.Fields.Fields[i].AsString;
if (Results.Fields.Fields[i].DataType = ftFloat) or (Results.Fields.Fields[i].DataType = ftLargeint) then
sValue := ReplaceStr(sValue, ',', '.')
else
begin
sValue := ReplaceStr(sValue, ',', ',');
sValue := ReplaceStr(sValue, #13#10, '|');
sValue := '''' + escape_special_characters(sValue) + '''';
end;
sValues := sValues + sValue+',';
end;
if sValues <> '' then SetLength(sValues, Length(sValues)-1);
sl.Add(sValues);
Results.next;
end;
Results.Close;
end;
As you can see, the StringList is passed as parameter, just like you did wit the filename, but not created in it. It's the calling procedure that will create and free it, like thi :
procedure Form1_bExport_OnClick (Sender: string; var Cancel: boolean);
var
Sl : TstringList;
begin
Sl := TstringList.Create;
sFileName := 'ExportedData-' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv';
SaveDialog := TSaveDialog.Create(Form1);
SaveDialog.FileName := sFileName;
SaveDialog.DefaultExt := 'csv';
SaveDialog.Filter := 'Database|*.csv|Any file|*.*';
SaveDialog.Options := ofOverwritePrompt+ofHideReadOnly+ofEnableSizing;
if SaveDialog.Execute then
begin
ExportToSl('SELECT lastname, firstname, salary, dateofbirth, isSmoke, comment FROM employees', Sl);
ExportToSl('SELECT coDate, coName, coActive FROM Company', Sl);
Sl.SavetoFile('.\export.csv');
ShowMessage('Data has been exported in to the specified folder');
end;
SaveDialog.Free;
Sl.Free;
en;
This way, you can call the ExportToSl procedure as many times as you want, on as many queries as you want, everything is added to the stringlist (appended) and saved in one shot.
Hope this helps, I leave the rest to our fellow coders on the forum.
Cheers
Mathias
I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
Import-Export Dmitry - Work.zip 34.14 kb, 479 downloads since 2018-01-27 
