Loading data with new lines into MySQL fails [closed]

Question

I'm loading data into a MySQL database. It is a shared calendar that is used with the baikal calendar.

I dumped the DB, and vim checked it and I can see recent events.

When I load the data into MySQL I lose most of the events.

I can see the data has a lot of lines ending in a character sequence:

\r\n

For example:

BEGIN:VCALENDAR\r\nVERSION:2.0\r\nPRODID:-//Apple Inc.//Mac OS X 10.12.2//EN\r\nCALSCALE:GREGORIAN\r\nBEGIN:VTIMEZONE\r\nTZID:America/Toronto\r\nBEGIN:DAYLIGHT\r\nTZOFFSETFROM:-0500\r\nRRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=2SU\r\nDTSTART:20070311T020000\r\nTZNAME:EDT\r\nTZOFFSETTO:-0400\r\nEND:DAYLIGHT\r\nBEGIN:STANDARD\r\nTZOFFSETFROM:-0400\r\nRRULE:FREQ=YEARLY;BYMONTH=11;BYDAY=1SU\r\nDTSTART:20071104T020000\r\nTZNAME:EST\r\nTZOFFSETTO:-0500\r\nEND:STANDARD\r\nEND:VTIMEZONE\r\nBEGIN:VEVENT\r\nCREATED:20161206T015502Z\r\nUID:94FA4491-C68F-4751-B14B-A5E7B4B0370E\r\nDTEND;TZID=America/Toronto:20161209T080000\r\nTRANSP:OPAQUE\r\nX-APPLE-TRAVEL-ADVISORY-BEHAVIOR:AUTOMATIC\r\nSUMMARY:new caldav\r\nDTSTART;TZID=America/Toronto:20161209T070000\r\nDTSTAMP:20161206T015502Z\r\nSEQUENCE:0\r\nDESCRIPTION:some notes\\n\r\nBEGIN:VALARM\r\nX-WR-ALARMUID:2C8FDA92-BFB4-4295-86AB-2AE3E09D42A2\r\nUID:2C8FDA92-BFB4-4295-86AB-2AE3E09D42A2\r\nTRIGGER:-PT15M\r\nX-APPLE-DEFAULT-ALARM:TRUE\r\nATTACH;VALUE=URI:Basso\r\nACTION:AUDIO\r\nEND:VALARM\r\nEND:VEVENT\r\nEND:VCALENDAR\r\n','94FA4491-C68F-4751-B14B-A5E7B4B0370E.ics',1,1485446304,'2dc6a43af5125cc1cdcb668ea61cc505',981,'VEVENT',1481284800,1481288400,'94FA4491-C68F-4751-B14B-A5E7B4B0370E'

I know I need to escape these characters when reading the below, but its new to me and I do not fully understand how I should do:

mysql -uroot -p cal < cal.sql

https://www.oreilly.com/library/view/mysql-cookbook/0596001452/ch10s05.html

Could someone kindly give me and example of how I should escape these characters? They will need to be preserved for the database.

Answer 1

(this is a comment but space limited in comments box)

This really is a programming task and hence off-topic here. However it will closed if you post as-is on stackoverflow.

You have shown us a RFC5545 record which has already been escaped.

• You've not told us how this record is encoded in the extract.
• You've not told us how it needs to be encoded within the mysql database.
• You have not told us the structure of the database.
• The construct you have shown us for how you loaded the data into the database will choke when presented with the input you have shown us. If some records got loaded, that's because the data records were embedded in an SQL statement. You haven't shown us that.
• You haven't told us if the example record was successfully imported or not.
• If records did not load they would have generated eror messages. You've not told what those errors are.

If you provided explicit examples of the SQL statements for the records which did not load then there's a chance that we might be able to tell you how to fix the problem (but no guarantee).