cancel
Showing results for 
Search instead for 
Did you mean: 

Convert form output to Excel

digital
Grafter
Posts: 94
Registered: ‎11-04-2007

Convert form output to Excel

I have a form with three kinds of 'inputs': free text entry, single choices from drop-down menus and multiple choice through tick boxes. The form currently emails the entries to me and I enter the results into an Excel sheet by hand. Free text is copied and pasted, drop-down choices ('Yes', 'No' and 'Don't Know', for example) are entered into the appropriate column as a '1' to enable totalling, with multiple choice being treated similarly. Where an input has been ignored, the form sends back 'Left blank' or, in the case of free text nothing and so nothing is entered into that column in the spreadsheet.
This is fairly time consuming and open to error so it would be helpful if the process could be automated. Is there a (fairly) easy way to do this either at the time the data is generated or once the email has been received with the response to each question in the form
'Favourite colour: yellow
Is today Friday: yes
Name: Bilbo Baggins'
TIA!
5 REPLIES 5
digital
Grafter
Posts: 94
Registered: ‎11-04-2007

Re: Convert form output to Excel

For the benefit of anyone else trying this, I solved the problem by listing the variables for export within the email with \t between each of them. This created a block of text in the email which, when copied and then pasted into Excel using 'Paste special' and then 'text', placed each variable in consecutive cells.
spraxyt
Superuser
Superuser
Posts: 10,063
Thanks: 1,369
Fixes: 75
Registered: ‎06-04-2007

Re: Convert form output to Excel

Thanks, I hadn't realised Excel had the capability to paste tab-separated text that way.
I have created tab-separated text files (written from PHP) which Excel will read as a new worksheet, but those were for lots of lines.
David
digital
Grafter
Posts: 94
Registered: ‎11-04-2007

Re: Convert form output to Excel

There's just one issue which has arisen today and that is wherever anyone puts a ' into a free text response it appears in the email with a \ in front of it. Really annoying, but I can't see why. charset="utf-8" is set, but I don't see how that can be causing it.
Anyone?
spraxyt
Superuser
Superuser
Posts: 10,063
Thanks: 1,369
Fixes: 75
Registered: ‎06-04-2007

Re: Convert form output to Excel

I don't think the charset will have any bearing on that. The backslash is added to 'escape' the apostrophe so the system knows it is to be taken as part of the text string and *not* interpreted as a delimiter. It's probably added when PHP processes the form to create the email. Normally a knowledgeable post-processor would remove them after transmission - but setting that up probably falls to you.
David
jelv
Community Veteran
Posts: 26,786
Thanks: 990
Fixes: 10
Registered: ‎10-04-2007

Re: Convert form output to Excel

When using CSV files the convention is that if the character following one of the separating commas is a double quote this is a text string and everything up to the next double quote comma is taken as being part of the string. If you have double quotes embedded you double them up. Also because the text is quoted you can embed commas. E.g.
123,"ABC""D,EF","456"

gives three columns: numeric 123, text ABC"D,EF, text 456
You could try manual keying a test file to see if Excel interprets this correctly - I'm guessing if it does that using it with tab separated files would work as well.
Edit: I believe you can also have new lines embedded in to the quoted text strings.
jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£14.40/month)
Mobile: iD mobile (£4/month)