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
I have decided to use the included functionality to export my data, which should be much simpler than trying to automate it via code.
I created a DataView powered by a particular query. I set CSV Show If to true
I added a menu item to show the DataView, and this works just fine. Now I have questions regarding this functionality.
1: Filtering data
The query returns several fields from the Members table. My users need one export for all records, and another export for “men only”. We have a Gender field which will contain either M or F. So the question is, how to filter the data. I know in general how to add a parameter to the query. I can see using ActionButtons for this, or screen fields.
If Screen Fields, couldn’t I add a drop-down field which had pre-defined choices such as M: Men, W: Women, Both: Both? Then wire up the event to run a function that sets a five variable, which I will use for the query parameter?
Also, how to write the query so if “M” filter on that, if “F” filter on that, if “Both”, don’t filter at all?
2: Download file name
I’ve tried this with no filtering at all, and it immediately downloads default.csv to my downloads folder. So far so good, but this is not very useful to my users.
Many months ago, we discussed how to specify a name for the download file, perhaps an input box asking for name, or a “save as” dialog where I can assign a destination and file name. Can you tell me if this was ever done, or how to at least specify a name for the downloaded file?
Thanks for any help…
I have solved #1, so don’t need help with that.
However, #2 is quite important so users can download the report and find it again.
Now I remember about specifying a file name and location. You’ve already fixed this when I generate a report on the client side and click the download button. It pops up a file dialog where I can change the name and location.
Is there any way to incorporate that same result when I click the download button on a DataView?
Also, is there any way to supply a default file name instead of the generic GUID-like name that appears in that file dialog?
Thanks…
Hi Ron,
Thank you for raising these questions.
1. Export to CSV Button
The “Export to CSV” button in a Data View is a built-in feature that becomes available when the “CSV Show If” field is set to True. Because it is a standard platform feature, its behaviour cannot be modified.
However, Users can change the file name and save location when downloading the file, depending on their browser settings. Most browsers have an option to prompt the user for a save location before downloading a file.
If you require different functionality, you may consider creating a custom button that meets your specific requirements.
2. Custom File Names
The enhancement that allows a custom file name to be supplied and removes the GUID from the exported file name has already been implemented. This improvement is currently undergoing testing and will be included in an upcoming release.
I understand that we have discussed this previously, and I appreciate your patience while we work through the final stages of testing and release.
Regards,
Elton S
Thanks for the reply Elton.
This is not worth trying to automate via code. I remember when the subject first came up, it was for client-side report downloading. That too was a built-in function, but you changed it in a new release to have a “save-as” dialog come up. Not only can we change the name, we can change what folder it is saved to.
This is what I’m asking for the CSV Show If functionality. If you are already working on that for the CSV download button, then I will wait for it.
Please advise when you know more about an ETA.
Thanks…
Ron Mittelman
I just got updated to version 3. Can you tell me if this functionality is now included, so I can select a destination and file name for downloading the CSV file?
As you can imagine, this type of flexibility would be great for any type of reporting or downloading.