Solved

Extracting tabular data from inspections

Corey
Rank
Userlevel 3
Badge +4

Has anyone found a good way to extract data from multiple inspections of a specific template into a tabular/grid format?

I’ve tried the following, all of which are not very functional:

  1. SC Data Exporter (new version with GUI) - This extracts the “inspections” in a CSV file and “inspection_items” in a separate CSV file. However, all question labels and response values are in a single column. You cannot use a pivot table on this due to needing each question label as a filter, row or column, and the response values for those separated out by label. 
  2. SC Analytics - The “Responses” tab allows you to see a list of inspections for a template. If click export - table as CSV, you get a spreadsheet of the inspections but without the responses. If you click “view responses” you can see charts and breakdowns of the answers for each question. However, I cannot see responses for a question based on the responses of another question - only all answers for each question regardless of how they answered the other questions.
  3. Power BI - We have a very good Power BI dashboard, which includes a page with a line chart of numerical responses and a “matrix” visualization that shows a row for each inspection with the questions across the top. Essentially de-normalizing the data. That is EXACTLY what we want, except that when you export the visualization to a CSV file, it doesn’t output in that format; it outputs in the normalized vertical column format of the raw data tables, just like it came out of the SC Data Exporter.

See the image for an example from Power BI which is formatted properly but can’t be exported that way.  I’ve also attached an image of how the exporter outputs.

 

 

 

icon

Best answer by Hojae Jung 5 April 2023, 03:13

View original
This topic has been closed for comments

10 replies

Hojae Jung
Badge +1

Hey @Corey, it’s me again haha

By the end of this month, the reporting team will release the new export format option, Excel, to all customers. This might be a great way to extract inspections data of multiple templates into a tabular or grid format.

If you would like, we would love to enable it for you so you can try it! Until the full release (end of March), there is a limit of 10 inspections per export with the Excel option. 

Please message me your SafetyCulture account email address and we look forward to your feedback on this.

Corey
Rank
Userlevel 3
Badge +4

Initially, this seems like it may be the option they need, with some requests:

  1. They’ve asked if it can be automated to run on a schedule for a specific template.  I can do that with the integration builder probably.
  2. They’ve asked when the limit will be increased from 10, which is not very useful at the moment.  1,000 should do the trick, so we look forward to that change coming soon.
  3. I am curious, since this is Excel rather than a CSV, if it can be formatted as a table with the filter/sort buttons on the headers in the export.  We can certainly do this ourselves after opening the file, but it is one less step if already done.
Hojae Jung
Badge +1

Morning @Corey, great to hear that you had a chance to try out the new Excel export option in SafetyCulture.

  1. Spot on, it will be best to use integration to automate the process. If you don’t mind asking, are they wanting to “Automatically export <x number> inspection reports every <x days/weeks/months/years> ? Also, who’s they in this context - would it be other administrators in your organization?
  2. Bulk export limits will be increased by end of this month! I will send you a private message or reply in this thread once it’s available for your organization.
  3. Awesome feedback. I will share this with our team.
Corey
Rank
Userlevel 3
Badge +4

I was asked by a plant manager and a quality manager at 2 different sites to be able to export data for sorting and analysis.  They are not administrators, but they support management of templates/reports at their sites.  

The plant manager wanted to “auto run” reports and exports based on a defined scope of data.  I took this to mean specific templates for specific date ranges, run on a specific frequency.  

I was also asked (once the limit is increased to 1,000) if there was a way to select more than just what is visible on the page (which is only 25).  I showed him the link next to it that says “select all ??? inspections.”

Hojae Jung
Badge +1

Hey @Corey, I hope you are having a great day so far.

Set it, and forget it → We look forward to supporting this level of automation with the extra configuration options you’ve mentioned from the web app in the near future. 

I will comment in this thread and send you a message once the “increased bulk export limits” (1,000 inspections per export for Excel)  becomes available to customers. You will most likely to have it by end of next week and I will keep you posted. 😊

Hojae Jung
Badge +1

Hey @Corey, just wanted to let you know that we are still working on it.

Hojae Jung
Badge +1

Hey @Corey, I hope you’re having a great week so far! 

We’ve enabled the increased bulk export limits for your organization. You should be able to bulk export in these formats:

  • PDF: 100 per export
  • Word: 100 per export
  • Excel: 1000 per export

Let us know how you go and thanks again for your patience!

Corey
Rank
Userlevel 3
Badge +4

The increase to 1,000 is great.  I tested selecting inspections from multiple templates, and it puts each on a separate worksheet.

The only problem I have still is that when I open the Excel file, I get an error.  It opens fine, but I have to accept the error the first time it is opened.  Once it performs the repair, if I save it, the next opening will not produce the error.

 

Hojae Jung
Badge +1

Awesome to hear @Corey! 

With the error, our team was able to replicate the same issue, and we’re looking into this at the moment. 🙂

Corey
Rank
Userlevel 3
Badge +4

I’ve run into additional issues with the Excel export. If you change a template’s questions, then you export to Excel inspections both before and after the change, it results in some of the columns being duplicated, as well as adding in random columns from logic-driven questions that weren’t answered.

Additionally, if you have questions that are buried in logic which were not answered (didn’t meet logic criteria for them to appear), then you export only inspections that did not have those questions, those columns will not be in the output. If you later do an inspection that does have those logic-driven questions, the columns for them appear.  This means we now have a shifting data structure.

In both cases, if we have a master workbook that has calculations or pivot tables, or if we pull this into Power BI, this changing structure spreadsheet will not work properly.

Terms & ConditionsCookie settings