Moving html data from mysql (wordpress) to sqlserver

Question

I am upgrading a website to asp.net which have at least 100K posts in wordpress. I could not find any related topic for moving so i wanted to share my experience.

Big data is not a problem, however, some of wordpress tables have html data containing quotes (both single and double), &nbsp's, tab characters and so on. I have tried many ways for both exporting however, exporting to SQL file will not work for me (at least, i could not able to work with it, it causes so many troubles).

What are your specific troubles with exporting to a SQL file? — Dennis van der Schagt, Aug 05 '15 at 11:23
i am not the owner of project. it was started with wordpress. I am just upgrading to a newer platform as a web developer. — Oğuzhan Kahyaoğlu, Aug 05 '15 at 12:51
For problems: quotes in an exported SQL file results of inability to import to SQL Server. Replaced quotes does not match. It is not as simple as you think. Base64 images also exists, so on. — Oğuzhan Kahyaoğlu, Aug 05 '15 at 12:52

score 0 · Answer 1 · answered Aug 05 '15 at 11:21

Best way to move data is exporting to CSV files, seperately for each table. While exporting, you have to:

Custom Export
Different and unique strings for both "Columns separated with" and "Columns enclosed with" (I used ############ and @@@@@@@, respectively)
Check "Remove carriage return/line feed characters within columns"
Check "Put columns names in the first row"
Download export file

After downloading, replacing will take place:

Tabs with spaces \t -> space
Double quotes to single " -> '
###### -> \t
@@@@@@@ -> "

Finally, encoding conversion is required. myadmin output is ANSI file and it is corrupt. Sql server might not able to handle it. To resolve it, first convert to UTF-8 and convert to ANSI again (In notepad++, it has options in "Encoding" menu).

While importing to SQL server, you must have to select text file as source. Select related csv files and while importing, take care of column lengths. Sql server will make all your columns varchar(50) default. Exported data will have much more larger columns. You have to adjust them in import wizard manually. Use DT_Text (not DT_NTEXT) for string values.

I know this process will result in some data loss (tabs and double quotes) however, it is wordpress' html editor's fault. Html data should be stored as encoded in database for these purposes...

score 0 · Answer 2 · edited May 23 '17 at 11:43

go with a linked server created on the target system; this way the import process can be driven by SQL-Server and will hopefully produce a result ready to use without requiring too many steps & checks.

there are many SO posts about interacting with MySQL from SQL-Server:
Can't create linked server - sql server and mysql
SELECT * FROM Linked MySQL server
Do I have to use OpenQuery to query a MySQL Linked Server from SQL Server?

Moving html data from mysql (wordpress) to sqlserver

2 Answers2