I’ve perused the documentation, looking for how to export a table or query result to Excel or CSV file. I found how to do this manually by using the Export button.
Now I need to do this programatically from a menu item or form. I can’t seem to find anything in the API section of the documentation to show how this is done.
Can you please suggest code that, given a query name and the parameter value, can execute the query and cause that data to be exported to an Excel or CSV file?
Thanks…
Hi Ron,
Regarding the CSV file extension:
Five allows downloading the results of a query into a CSV file through the Dataviews. In your query, make sure the field ‘CSV Show If’ is set to true.
In case you want to make the CSV programmatically, based on code, you need to create your own custom function. (I believe you can achieve it using AI to assist you)
Regarding the Excel file extension:
I believe you cannot generate it using pure JavaScript code; you may need a library to support its generation.
What I am able to suggest is to search for a JavaScript library that does this generation based on the data you provided.
Add this library to Five’s library, and you can call this library via a function passing the data, and then you can generate a downloadable file.
I believe you can achieve it using AI, but if you prefer, we can also create this function(s), but we will charge for this custom development.
Regards,
Elton S
Thanks for the quick answer Elton.
It is not so important that I get the file in Excel format, as Excel can easily open a CSV file.
The question was more about how to generate that csv file based on a saved query, so it doesn’t need to be done through the UI by a user.
I know I will need to write a custom function, but ChatGPT does not always understand Five’s complexities. I’ve looked through the API documentation and can’t find anything about exporting to CSV in code.
I’m not looking for you to write my code for me, but I need an example of how you would get a data set via a query and save the results as CSV. OR, I can write a function/query that clears, then loads a table, then export that to the file. If this can be done by clicking a button in the UI, then there should be a way to do this via JavaScript code. If the documentation contained a function or method to do this, then I wouldn’t be asking the question.
Hi Ron,
Because we allow exporting CSV files from Data views using queries as a data source (having the field CSV Show If set to true ), you can assume that you can create the result you wish and export it.
In your case, you want to export it via a function, and for the CSV file, you can do so in JavaScript and even make it easier with AI. (I don’t think it has much to do with the five methods/functions because it is pure JavaScript)
Example: This is a prompt passed to AI:
“Based on a given object, can you create a CSV file that can be downloaded? This entire function will be executed by an event that already exists, so no need to create a button to be clicked. I need to pass an object, the name of the file, and it will generate the CSV downloadable file.”
This is the client-side code provided:
function downloadCSV(data, filename = "data.csv") {
if (!data || !data.length) {
console.error("No data provided");
return;
}
// Extract headers from object keys
const headers = Object.keys(data[0]);
// Convert data to CSV rows
const csvRows = [
headers.join(","), // header row
...data.map(row =>
headers.map(field => {
let value = row[field] ?? "";
// Escape quotes by doubling them
value = String(value).replace(/"/g, '""');
// Wrap in quotes if needed
if (value.search(/("|,|\n)/g) >= 0) {
value = `"${value}"`;
}
return value;
}).join(",")
)
];
const csvString = csvRows.join("\n");
// Create Blob and download
const blob = new Blob([csvString], { type: "text/csv;charset=utf-8;" });
const url = URL.createObjectURL(blob);
const link = document.createElement("a");
link.href = url;
link.setAttribute("download", filename);
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
URL.revokeObjectURL(url);
}
Example usage:
const data = [
{ name: "Alice", age: 25, city: "Brisbane" },
{ name: "Bob", age: 30, city: "Sydney" }
];
downloadCSV(data, "users.csv");
The complexity is related to the generation of an Excel file, which requires an external library.
Regards,
Elton S
Thanks Elton,
This is just what I was looking for. Sorry for not being able to query ChatGPT myself, as I’m too new at this to know what is pure JavaScript functionality and what needs to be Five functionality.
Follow-up:
What I’m eventually after is to have a functionality to create the CSV file, and attach it to an email, similarly to the way I’m doing this with reports, so it can be mail-merged and sent to desired recipients.
In that case, you get the reportResult.report object and add that to the email, done on the server.
In the case of the CSV data, does the existing mail-merge logic require it to be downloaded in an actual file before attaching it to the email, or do you think there is a way to get the rows back from the query and directly attach to the email as CSV file?
thanks…
Hi Ron,
The functionality you need needs to work similarly to the reports; you need to attach a base64 file to the email. Therefore, your approach needs to be something like:
1 - Retrieve the data.
2 - Create a CSV data structure with headers and columns.
3 - Convert everything to base64 format.
4 - Attach the base64 to the email.
Make sure when you attach the file, you add the correct MIME type ‘data:text/csv;’
You don’t need to download the file before attaching it. In my previous post, I mentioned downloading because I did not know you wanted to attach it to the email.
Regards,
Elton S