- SNAPCymruAsked on August 29, 2014 at 05:54 AM
I'm trying to run a system whereby submissions are exported into excel where they are used as data which we can harvest statistics from.
However this is complicated by the confused and seemingly random formatting of the entries from multiple tick check boxes.
Is there anyway to combat this?
- KadeJMAnswered on August 29, 2014 at 11:22 AM
Is this the form you are exporting to excel and experiencing this problem?
I inspected this and I think you might be referring to the included tags that ended up in your exported form data?
Have you considered trying to use our Google Spreadsheets Integration as an alternative? It has a similar spreadsheet feel but shouldn't have that problem.
- KadeJMAnswered on August 29, 2014 at 11:46 AM
I tried to replicate the problem after testing this further, but so far I am unable to do so.
- SNAPCymruAnswered on September 01, 2014 at 04:43 AM
Yes that's the form I'm struggling with. It's not insurmountable, it's just that I have to do a lot of data scrubbing before I can really work with it. Although the Long HTML codes that ended up in there were a result of me updating the forms while things were being submitted. It's the "<br>" codes which are causing me consistent problems on multiple entry check boxes.
I'll look into the google spreadsheet, but I've spent a week programming formula for excel so I'm not exactly aching to start over fresh on a new bit of software.
My company is planning on upgrading our account once I've got this up and running as we expect to process about 3000 forms a year. So with that amount of data the more time I can save in transferring the better.
- JotForm SupportWelvinAnswered on September 01, 2014 at 06:59 AM
We don't recommend adding HTML formatting in the dropdown, checkbox and radio button option if you are planning to use the data into an Excel Spreadsheet or CSV or even in the Google Spreadsheet Integration. Note that the excel spreadsheet cannot parse those formatting, and you will see the codes instead of the desired result from the form view.
- JotForm SupportWelvinAnswered on September 01, 2014 at 07:00 AM
If you want to style the options, you can inject a custom CSS codes to the form: http://www.jotform.com/help/117-How-to-Inject-Custom-CSS-Codes.
Here's an example form customization: http://www.jotform.com/help/75-Customize-your-Form-using-Custom-CSS-Codes.
- SNAPCymruAnswered on September 01, 2014 at 07:28 AM
I did not add HTML. That was a glitch created by my editing of the form while submissions were incoming.
I do not care about style. I simply want my data to export cleanly, without additional codes which interfere with my ability to process it.
- KadeJMAnswered on September 01, 2014 at 09:54 AM
Excel and Google are similar so some of the formulas might work together but not all of them as far as I know. I've decided to do some more testing of this to see if I can figure out why you are getting the brackets and I will be with you shortly.
- KadeJMAnswered on September 01, 2014 at 10:46 AM
So I was finally able to replicate the same problem, but unfortunately after tearing into this deeper I could not find the cause.
The closest I can think of is that perhaps it is might be related to the length of the characters being used since at first that didn't happen. But after checking into it more I found if I replicated it using longer answers in my tests which is what seems to break it so far.
I think perhaps there might be some sort of parsing error with your data read into excel. However, I'm not sure if this is a problem with the data we are sending or if it is with excel because it looks fine on our end until you look at it in excel.
I've sent a report about this problem to our dev team and if we're able to find anything wrong we'll let you know with an update as soon as we can.
Another New Test with Similar:
- SNAPCymruAnswered on September 01, 2014 at 10:54 AM
Thanks for looking into it.
I'm thinking that for the moment I'll use Google integration and just copy the data out of there and into excel as it doesn't appear that the problem occurs in Google.
It's not interfering too much with my analytics but it makes the Raw data troublesome to look at correctly. What's odd about it is that it's not consistent. Entries with exactly the same options selected sometimes have it and sometimes don't.
- KadeJMAnswered on September 01, 2014 at 12:09 PM
You are welcome and yes that is why I had suggested the Google Integration as an alternative for the time being.
Additionally, I do get what you mean by the fact that it's been troublesome and if it's found to be a problem on our end we will find and get it fixed as soon as possible.
Something else that I wanted to point out which I found for your excel file here http://office.microsoft.com/en-us/excel-help/top-ten-ways-to-clean-your-data-HA010221840.aspx which might possibly be helpful in case you want to give it a try.
- alp_denizAnswered on September 02, 2014 at 02:59 PM
The issue has been fixed today. Please let us know if the issue still exists.
Thank you very much for letting us know.