Posts Tagged ‘excel’
FormSmarts allows customers to collect online form submissions on the cloud and download aggregated entries in a format, CSV, which is supported by all spreadsheet applications (including Microsoft Excel, Apple Numbers and Apache OpenOffice) and many other data analysis tools. We’ve recently added the option to export form responses to native Excel format (.xlsx or .xls) instead of the default CSV for Excel.
The main reason we decided to support the new export format is to solve an issue with phone number fields that may not be displayed correctly when Excel opens a CSV file. The Excel XLSX format also has a few other benefits, but we expect most members to continue using the default CSV format in the foreseeable future. Please read on to find out if and when you would benefit from using the new format.
Benefits of the Excel .xlsx Format
Phone Number Fields
Provided you have set the Allowed Content (data type) of phone input fields to Phone Number, FormSmarts stores phone numbers as character strings rather than numbers to ensure any leading zero is not dropped from phone numbers. Unfortunately, Excel interprets character strings that only contain digits as numbers, thus removing leading zeros. Members who collect phone numbers on their forms, especially in locales where phone numbers may start with a zero (some US states and part of Asia & Europe), should set the Export Format to the Native Excel option in the Report Generator settings, as illustrated above.
If you find out when opening a CSV spreadsheet from FormSmarts that leading zeros were dropped from phone numbers, update the export format and create another report. Provided phone input fields were correctly created as phone number fields, the correct information, with any leading zeros, is stored on FormSmarts. No information was lost, any errors is due to the Excel import process and can be easily fixed by generating a new report after changing the export format.
All settings of the Report Generator but the timezone are kept in your browser, so as long as you use the same browser and allow FormSmarts to store information in your browser, your export preference will be saved from one session to the next.
Form Results with Data in a Non Western-European Language
When a spreadsheet contains data in a language other than English or another Western-European language, customers need to make sure when opening an Excel/CSV report from FormSmarts they select the UTF-8 (Unicode) encoding in Excel’s Import Wizard. The content of the spreadsheet may otherwise not be displayed properly. Alternatively, exporting form entries to a native Excel (.xlsx, .xls) spreadsheet removes the need to select the correct text encoding.
Note that FormSmarts stores data with a universal encoding (UTF-8) that supports all languages, so if you find out that a report appears corrupted when opening the spreadsheet, open the file again and select the UTF-8 encoding or switch the export format to Native Excel: no data was lost or corrupted.
The native Excel format offers a few other benefits and we expect their number to grow in the future:
- The URLs of form attachments are clickable links
- The FormSmarts Reference Number (the penultimate column in the spreadsheet) links to the form response on FormSmarts. Use this link to add a note to a form submission, check where it was submitted from (IP address, approximate location), export it to PDF, print it, delete it and more. As we add more functionality to the page showing individual form responses (like the ability to tag and edit a form response), we expect more and more customers to rely on links in the spreadsheet as part of their workflow.
The native Excel format is supported with Business Plus accounts and above.
If you’re using FormSmarts’ Export to Excel feature, you may have already noticed that we’ve updated the report generator. As well as several other minor changes, we’ve added a second way to specify the date range over which the report is generated (in addition to selecting a start and end date) by choosing a preset time period. This is meant to further simplify the task of generating a report. The preset periods available are Today, Yesterday, This Week, Last Week, Last Seven Days, This Month, Last Month, Last 30 Days.
For the time period options that depend on the first day of the week (This Week, Last Week), we use the default for your country, as set in your account. You can change this in the Report Generator under More Options.
Give Us Feedback
FormSmarts allows users to collect online form responses on the cloud and export them to Excel at their convenience with the Report Generator.
Enable Form Data Storage for Export to Excel
Export to Excel requires that you turn on data storage for each of the forms for which you would like to use this feature.
For privacy reasons, form submissions were not saved on the cloud by default in the past. For Business accounts created after March 20, 2017, storage is now enabled by default. This does not apply to Pro accounts, for which storage is turned off by default and must be enabled for each form if needed. Note that the storage space available with Pro accounts is limited.
To enable stored form results and allow Excel reporting, visit the Submit Actions tab of the Form Details screen of each of the forms for which you would like to access form responses online, as shown in the video below.
You will then be able to download Excel reports from stored form responses in a few clicks with the report generator. Business-level members may also view individual form responses online and download a PDF Report.
To enable storage of form entries by default when you create a new form, check Turn on cloud storage for all new forms in the Settings Tab of the form builder. This option is only available in Business accounts and is turned on by default for accounts created after March 20, 2017.
Updating Form Results
To ensure you get consistent and up-to-date data when you export form results as an Excel spreadsheet:
- If you find out a form response has incorrect or out-of-date information, edit it and amend the data
- After you submit a test run, remember to remove it.
Excel Report Generator Settings
Show Field Names in the First Line
If checked, the first line of Excel reports will show the name of each field. This option is enabled by default.
The report generator allows you to specify the date range over which a report is generated by either picking a start & end date or choosing a preset period of time (This Week, Last Seven Days,…). For the time periods that depend on the first day of the week (This Week, Last Week), we use the default first day of the week for your country (as set in your account). You can change this under More Options.
Preset time periods, date ranges and the form submission timestamp shown in Excel reports are now using your time zone (as defined in your computer settings). If you would like to generate a report using a different timezone, select the relevant city in the Timezone drop-down list. Note that the city selected by default may not be your city of residence, but it will be a city in the same timezone.
Export Format: CSV or XLS
Choose the reports format from Comma-Separated Values (CSV), the default, or the Native Excel format (XLS or XLSX).
Importing an Excel Report When the Spreadsheet Contains Non-Western European Characters
If an Excel (CSV) report contains text in a language other than English, you may need to change the character encoding to Unicode (UTF-8) in Excel’s Text Import Wizard, as illustrated below. The report may not display correctly for languages other than English unless the encoding is set to Unicode (UTF-8).