Thread

We’re using Mark Huot’s File fieldtype (to upload an MP3 to a field), and I’m preparing to update—finally—to ExpressionEngine 2. I understand I need to use another fieldtype for this/these field(s) in EE2, that’s not my problem.

I’ve run the update successfully in a sandboxed environment but have noticed—by looking in the database—that the fields using the File field type have extra carriage returns (see screenshot, notice the scrollbar), which, when pulled in a template, get displayed or added. For instance {field} gets parsed as content[return, return, return, etc.].

I’ve gone back and checked the pre-updated database to ensure that the returns weren’t just added as part of the update process; they weren’t. So I’m left assuming that the extension/fieldtype correctly parses them on the template level in EE1.x.

Is there a way I can strip all the extra returns with a query?

(I’ve already tried converting the field prior to updating to, say, Text Field, and that didn’t work.)

Hey Sean,

What about exporting the data into a csv file using a plugin (http://devot-ee.com/add-ons/csvee/), does the csv file contain carriage returns? If not, you can import it back into EE using this module (http://devot-ee.com/add-ons/datagrab/ - commercial addon, but worth it if you have 100’s of entries)

{exp:csvee delimiter=","
filename="mp3.csv"
query="SELECT 
t.title as Title,
d.field_id_107 as MP3
FROM exp_weblog_data d
INNER JOIN exp_weblog_titles t  ON d.entry_id = t.entry_id 
WHERE d.weblog_id='X' 
ORDER BY t.title ASC"}

Also, if you’re having issues downloading the csv, see Ryan’s fix

What about exporting the data into a csv file using a plugin (http://devot-ee.com/add-ons/csvee/), does the csv file contain carriage returns?

Good suggestion. I tried it and the returns are stripped in the CSV. I’ll definitely consider re-importing a CSV via DataGrab (already have a license) as a last resort. The implications seem pretty high—we have thousands of entries.

Any other thoughts/ideas before I start formulating a DataGrab solution?

The only other idea I can think of is to do a find/replace query. Not sure if this will work or not, but backup your DB before you try it.

update table_name set field_id_x = replace(field_id_x, '\n', '')

Try ‘\r’ also if ‘\n’ doesn’t work

The only other idea I can think of is to do a find/replace query. Not sure if this will work or not, but backup your DB before you try it.

update table_name set field_id_x = replace(field_id_x, '\n', '')

Try ‘\r’ also if ‘\n\’ doesn’t work

Yep! Just as you replied, I ran a similar query and it did the trick. Basically exactly what you suggested, but with CHAR(13) in place of \n. For good measure, I also ran the same query with CHAR(10).

Here’s the solution:

UPDATE exp_channel_data SET field_id_XX = REPLACE(field_id_XX, CHAR(10), '')

And:

UPDATE exp_channel_data SET field_id_XX = REPLACE(field_id_XX, CHAR(13), '')

Thanks for your help.

I’m glad you were able to fix it! I felt I needed to return the favor for your article on EE and local dev from many moons ago. I still use that same setup! 😊

Thanks Siebird. Glad you have it working for you now Sean. Feel free as always to start a new thread if you have any more questions