Exporting query data with SaveAsCsv, SaveAsXml and SaveAsJson

MB-820 · Develop by using AL · Updated

A query object combines data from several tables into one dataset. When you need that data in a file, the Query data type has built-in SaveAs methods.

Methods on the Query data type

MethodOutput
SaveAsCsvComma-separated values
SaveAsXmlXML
SaveAsJsonJSON (added in newer runtime versions)

Each has an OutStream version, which is the one to use in Business Central online.

procedure ExportItems()
var
    ItemsQuery: Query "Items Query";
    TempBlob: Codeunit "Temp Blob";
    OutStr: OutStream;
    InStr: InStream;
    FileName: Text;
begin
    TempBlob.CreateOutStream(OutStr);
    ItemsQuery.SaveAsCsv(OutStr);
    TempBlob.CreateInStream(InStr);
    FileName := 'items.csv';
    DownloadFromStream(InStr, '', '', '', FileName);
end;

What queries can’t do

Formats like HTML, PDF, Word and Excel belong to reports (Report.SaveAsPdf, Report.SaveAsExcel and so on). If an answer offers SaveAsHtml or SaveAsExcel for a query, it’s wrong.

Exam traps

Query: CSV, XML, JSON. Report: PDF, Word, Excel, HTML.

Older exam material was written before SaveAsJson existed. If only one answer is accepted, XML has been supported the longest.

Learn more: Query data type.

Practice questions

Pick an answer to check it. Questions are original and written for practice.

1. Which of these methods is NOT available on a Query variable?

Show answer

Answer: C.SaveAsExcel is a report method. Queries support CSV, XML and JSON.

2. You need to export query results as a file the user downloads. What do you pass to SaveAsCsv in a cloud-ready way?

Show answer

Answer: B.In Business Central online you can't write to server paths. Write to an OutStream (for example from Temp Blob) and then download it.

3. Which object type supports SaveAsHtml?

Show answer

Answer: C.SaveAsHtml, SaveAsPdf, SaveAsWord and SaveAsExcel belong to reports, not queries.

Keep going