Automatically update Excel with form entries using Web Query

Excel is a great way to view form entries but downloading CSV files from the back end and importing them into Excel is a chore. Fortunately it can be automated and getting new data and reports is as simple as clicking Excel > Data > Refresh All

Excel has a Web Query that will scrape an HTML table (Excel > Get External Data > From Web). This has some undocumented limitations,

• Text strings longer than 48 characters may cause the addition of erroneous rows
• Text strings containing paragraph marks, line breaks or non-standard characters may cause the addition of erroneous rows

The Web Query works best is the only thing on the page it is getting the data from is a plain HTML table. The steps in our solution are,

1. Create a blank page template
2. Create a Formidable view of your form called MyView (say) that has these settings,
   1. View format: "All entries"
   2. Before content <table><tbody><tr> and then <th>field label</th> for each field you want to output
   3. Content: <td>[5]</td> for each field "5" for example that you wish to output
      1. dates should have be like [5 format="Y-m-d"] to promote accurate importation.
   4. After Content: </tbody></table>
3. Create a new page called MyPage (say)
   1. Choose the "blank" template
   2. Add the view MyView
   3. change the slug to something obscure if necessary to enhance security
   4. View the page and make sure all the fields you want are there and the table is good. View the page source to check for any invisible issues.
4. Open Excel
5. Click Data > Get External Data > From web and enter the URL for your page
   1. https://mysite/MyPage
   2. After wizard has downloaded a preview of the table click Import and choose where to put the data
6. Excel will populate the worksheet with a range containing your form data. You can refresh this at any time to see new entries
   • If Excel fails to return data or you have extra rows you will need to visually inspect the source of the page and/or remove fields until you get Excel can import the data correctly. Typically you will need to remove free-text fields.
7. Create Pivot Tables and Pivot Charts to analyse your data

How could this be made better?

Everything above is vanilla Formidable and Excel. A bit of coding on the WordPress side could change dates and times into ISO date time format and aggressively clean and truncate text fields to increase the reliability of import. Another useful possibility would be to generate CSV output which would increase portability and promote reliable ingestion of data.