Forum

Thread tagged as: Question, Problem, Forms

Downloading form CSV problems caused by commas...

I'm on Perch 2.8.34 with Forms app 1.8.3...

We have a form with a text field on it, when the visitor enters a comma in the course of his message, throws off the CSV outputs as the commas are read as field delimiters.

Is there a way to work around this? or is it a bug?

thanks for your help, Monty

Monty Lewis

Monty Lewis 2 points

  • 3 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

The values should be quoted. Is it a number field?

no, a textarea.

    <perch:label for="Your-dinner-course">Your dinner course: </perch:label>
    <perch:input type="textarea" id="Your-dinner-course" label="Your dinner course" />

Here's an actual example of text in that field that's getting split up:

Here's the rundown:
Gluten-free kids hamburger with baked potato instead of fries.
This plate was a bit of a letdown. I realize that asking for a burger plate with no fries and no bun is a bit of a setup but I do think it could be done better. The burger came on a piece of lettuce instead of a bun. We were sad to hear that Hale St didn't have any GF buns or even bread to serve with the burger - seems like an easy way to make a GF dish feel a bit more "normal". The burger itself was quite dry and overcooked as well which stood out quite a bit on a plate with so few items. The baked potato was great - perfectly cooked and really delicious with the special tartar sauce that we asked to be added to the plate. (side note here: I noticed after we got home that we were charged an extra 50 cents for the tartar sauce which isn't a big deal but made me feel a bit like i was being nickel and dimed. We've never been charged for it before that i know of)**^**

Kids fish and chips.**^**
The fish was great but the fries on the plate looked like they were all the leftovers from the bottom of the batch - small,**^** random pieces. Not a big deal but I wonder if they would be better used chopped up and included in another dish. They just didn't present very well. I even noticed my youngest remarking that he got the "leftover fries" (although that didn't stop him from eating them.)**^** 

Steak sandwich.**^**
There was a split decision on this dish. My son loved it. I agreed with him that the sauce and caramelized onions were very tasty but thought that the cut of meat itself was not a good one and that the steak was overcooked.**^**

Jambalaya.**^**
This dish looks great but was a pretty big letdown for us. Before ordering i heard the host talking to some diners about how great it was and how often he ordered it for himself so we decided to try it. The dish was just too spicy. I don't mind a spicy dish if the heat brings some good flavor but this dish was so hot that you couldn't pick up any nuance or subtlety whatsoever. The rice was cooked well and its texture played nicely against the crunch of the corn but i can't say that i was able to decipher much of the flavor at all. We actually didn't eat all of it. I love jambalaya and would never order it again based on the dish that we were served.**^** 

I've added **^** characters where the CSV output text is getting broken into additional fields.

So, I was wrong, it's not commas... It seems to be getting thrown off by the return after the close paren? and then starts outputting the returns.

Drew McLellan

Drew McLellan 2638 points
Perch Support

Is it using CR or LF?

how would I tell?

Drew McLellan

Drew McLellan 2638 points
Perch Support

Probably not easily. I'll take a look at the code.

ok. so thinking it was the line returns that was throwing off the data in the CSV output, in responses.export.pre.php I changed:

            // fields
            foreach($Response->fields() as $key=>$field) {
                if (!in_array($key, $headers)) $headers[] = $key;
                $pos = array_search($key, $headers);
                $row[$pos] = '"'.str_replace('"', '\"', $field->value).'"';
            }

to

            // fields
            foreach($Response->fields() as $key=>$field) {
                if (!in_array($key, $headers)) $headers[] = $key;
                $pos = array_search($key, $headers);
                $row[$pos] = '"'.str_replace('"', '\"', $field->value).'"';
                $row[$pos] = '"'.preg_replace('/\r\n|\r|\n/', " ", $field->value).'"';
            }

Does that seem like the right solution? Seems to work. -- Monty

Drew McLellan

Drew McLellan 2638 points
Perch Support

That would take the line breaks out entirely, rather than escaping them. It will depend what you're importing into, as CSV isn't actually a standard.

But leaving the line breaks in breaks the CSV output, no?

For me, this is a better solution, but what you're saying is it wouldn't necessarily be better for everyone, right?

Drew McLellan

Drew McLellan 2638 points
Perch Support

But leaving the line breaks in breaks the CSV output, no?

It depends where you're importing it to, as CSV is not a standard.

ok. got it.

So best solution would be to make a change to the form app file and, as the form app gets updated, remember to bring the change over, too.

Because in this case, the CSV needs to be read into a spreadsheet and line returns in text fields interfere with that.

Drew McLellan

Drew McLellan 2638 points
Perch Support

I'm struggling to muster up enough interest in CSV files to form an opinion. You should probably just do what works for your purposes.

Ha! Well, it took me quite a bit of mustering to even mention it! :)