Submissions EXCEL/CSV reports: Separating payment info or products into each column
-
audabeeAsked on May 07, 2013 at 12:12 AM
Is there a way of getting each "product's" items in its own cell/column in the submissions report, rather than all in one cell?
I'm anticipating around 200-300 people filling in my form with multiple products and I'll need to collate all the details - I'm trying to automate the process as much as I can.
Thanks!
-
EltonAnswered on May 07, 2013 at 02:09 AMJotform Support
Hi,
Unfortunately we do not have that option. They are indexed into a single cell currently.
However, this workaround will probably help.
In a column adjacent to the column with the product listings (if the product listings are in A1-A13) insert the following formula, let says in G1 (or any other free column),
=SUBSTITUTE(A1,CHAR(10),"^")
Video Tutorial: http://screencast.com/t/DWOvVgveXM4
Copy this formula down to G13. This will copy the data in A1 through 13 into the corresponding cells in A. Now higlight the data in A1-A13 and copy the cells (ctrl+c), after that go to Edit --> Paste Special -> Values then OK or Paste Values. This pastes the actual values of the cell instead of its formula. Now you have a column of data with ^ substituted for every carriage return (end of line).
Highlight A1-A13, then Data --> Text to Columns. When the Wizard comes up, select Delimited on the first screen and Other on the next screen. In the blank space next to Other, insert ^. Now go to Finish, and your products should be listed in separate columns.
Hope this helps. Thank you!
-
EltonAnswered on May 07, 2013 at 02:12 AMJotform Support
Sorry if I forgot to mention. You can do all this when you export your submissions to excel and open it on your MS Office Excel Application. Thanks!
-
audabeeAnswered on May 07, 2013 at 04:09 AM
That. Is. Brilliant.
Thank you so much - I am much relieved to know I can get around the issue.
Awesome!
-
EltonAnswered on May 07, 2013 at 05:09 AMJotform Support
Your're welcome!
We will also inform you once this option is implemented in JotForm. Thank you!
-
NDPrepAnswered on October 01, 2013 at 02:24 PM
I am trying to do the same thing, but I when I seperate it I would like it to each field to go to the same column. We have multiple options for purchase, so when the columns seperate I want all the "Total Number of Attendees" in column AA and "Sponsor a Teacher" in column BB, etc. How can I do that?
-
JonathanAnswered on October 01, 2013 at 04:00 PMJotform Support
@ NDPrep
Hi,
Please create you own thread for this so that we can provide you assistance. Use this link http://www.jotform.com/contact/ .
Provide as much details as you can.We will attend to your post at once.
Thanks.
-
seanmccthyAnswered on September 10, 2014 at 04:55 PM
I consider you to be a brilliant support member Elton. You make everything easy to understand.
-
ChristenanderseiteisraelsAnswered on February 11, 2016 at 10:32 AM
I'm also having the issue, that audabee posted some 3 years ago, still no fix, this is not a difficult issue - typeform offers the solution I need, it would be sad having to leave jotform without this feature as it creates too much of a difficulty for me...
-
davidAnswered on February 11, 2016 at 12:50 PMJotform Support
The data is indeed still collected and exported in a single cell. However, the workaround presented in this thread should still be valid. If you would like assistance setting up a sheet for your form, please create a new thread, letting us know which form you are working with and we will be happy to help.
-
parentassociationAnswered on February 21, 2016 at 09:02 PMI also have the same problem, and don't understand why 3 years is not enough time to figure out a better workaround than separating the data in XL. I have to report on the individual totals for multiple items that are all represented in ONE column in your report. I do this daily. No, repeating these steps in XL each day is not an adequate solution. Certainly if you concatenate fields to pass data to PayPal, you have the fields separately to report to you jot form users.
-
JonathanAnswered on February 21, 2016 at 11:41 PMJotform Support
@ parentassociation
We will attend to your concern separately here http://www.jotform.com/answers/777620
Thanks.
-
USTAPNWAnswered on February 26, 2016 at 11:06 PMI have the same problem! I have two areas that I want to separate. One is the two-product payment area. The other is a question in the form that has four fields in a widget. I have tried to separate with the delimited method and it has not worked. In fact, I lost data; only the first product remained listed.
-
EltonAnswered on February 27, 2016 at 12:22 AMJotform Support
-
USTAPNWAnswered on February 29, 2016 at 10:10 PM
Thank you. This worked with some tweaking. I had several commas separating data rather than '^'. I entered the comma as a delimiter and it worked.
-
JonathanAnswered on March 01, 2016 at 12:02 AMJotform Support
Thank you for letting us know that it has now worked.
Contact us anytime for any assistance you need.
-
diligentfollowupAnswered on November 18, 2016 at 02:44 PM
None of this is helpful. Is there any application on the planet where shoving all the collected data into 1 CSV cell is useful? It's like the post office collecting everyones mail, transferring them to clear overhead projector slides, stacking them all on top of each other, and projecting the whole shmear onto a wall saying, "Here's your mail." Worthless. This needs to get fixed.
-
diligentfollowupAnswered on November 18, 2016 at 03:00 PM
Why can't the CSV file look exactly like the table that gets spit out into the PDF or email body, with every data type in its own happy little cell? I don't get it. Super frustrating. Data processing is just as important as the data collection.
-
davidAnswered on November 18, 2016 at 03:40 PMJotform Support
It seems you have posted in multiple threads with this same request.
https://www.jotform.com/answers/993361
If you have further questions, please follow up in that thread and we will be happy to help.