Errors galore

erimar posted 7 years ago in Import/Export
I ran the export of a database and created a separate sql file for each table. (385 tables) I then dropped the tables from the database. After that I ran the import of the sql files and non of the files will import. They are full of sql syntax errors. First of everything is double quoted where there should be no "'s at all. I do a mass replace to remove all the quotes. Then there are tons of 'r'n in the data parts of the script. If I do a mass replace of 'r'n with a ', this seems to fix this error. But by doing that the data does not get imported into the proper areas. In blob type tables where emails are contained it is hopeless because if the emails contain any type of , " / or anything it is to corrupt to import.
What are the 'r'n ?
Why can it not export fields containing ,"/ in a ' ' manner so it is not treated as a separate field every time it comes across one?
Why does it "" everything that does not require quotes?

REPLACE INTO "folders" ("id", "name", "parent_folder") Values
has to be
REPLACE INTO folders (id, name, parent_folder)Values
('1d17a251-7cde-89ba-d0e8-49cb93bc6d24','Sent Emails','efd0575d-7250-1b47-14e8-49cb9313065f'r'n,0,0,1,)
needs to be
('1d17a251-7cde-89ba-d0e8-49cb93bc6d24','Sent Emails','efd0575d-7250-1b47-14e8-49cb9313065f',0,0,1)

I could see if I had used different versions, upgrade sql or something but this was just a export data and import data test without changing any settings or versions of anything.

ansgar posted 7 years ago
You are obviously using an older version of HeidiSQL and selected "ANSI SQL" as target compatibility. You should be able to import that file when it starts with setting SQL mode to ANSI.
erimar posted 7 years ago
That works for most of it but I am still getting the 'r'n errors. O am assuming these could be some type of carriage returns or something?
I am not sure
Notice some of the 'r'n some spots are 'r'n'r'n'r'n'r'n'r'n
in some of the files
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'r'nNo Addendum for Specialist received as of 4-20-09'r'nSymix Customer Number: 9' at line 1 */
ansgar posted 7 years ago
ANSI should escape chars with single quotes, so you're when assuming these are carriage returns. Throw them away or try to use backslash instead.
erimar posted 7 years ago
Everything worked. Thank you, I threw them away. It was pain staking because there were average 34000 lines per sql file and some areas the 'r and 'n was valid in the body of the data when someone imported a email that had quotes. Lesson learned.

Thank You for your help.

Please login to leave a reply, or register at first.