MSSQL to MYSQL table migration with extended character encoding using bcp and iconv

Oct 2nd, 2013

Sometimes automated tools like my favorite, MySQL Workbench, don't always migrate extended characters properly. I had a problem where a particular field would truncate after the German ü, the u with the umlaut.

I got the idea for this by reading: http://www.wolflabs.org/2012/08/09/migrating-unicode-data-from-mssql-to-mysql/ . I suggest looking that over so you can understand the basics and get iconv installed (don't forget to add it to windows PATH or you'll get something like 'iconv is not recognized...').

To use the scripts below, you will need to create the c:\tmp directory if it doesn't already exist. If you are replacing the table with new data, make sure to empty the mysql database table before you run the script. Text within brackets, ie. [text], should be replaced with the correct text for your database names, tables names, usernames, and passwords for both MSSQL and MYSQL.

Here is a .bat file that you can just run: 
bcp "select '**BOL**', a.* from [mssqlDatabaseName].[mssqlTableName] a;" queryout "C:\tmp\[mysqlTableName].csv.dump" -w -CRAW -t"**EOT**" -r"***EOL***" -S localhost -U "[mssqlUsername]" -P "[mssqlPass]"
 
iconv -f UTF-16LE -t UTF-8 < "C:\tmp\[mysqlTableName].csv.dump" > "C:\tmp\[mysqlTableName].csv"
 
del "C:\tmp\[mysqlTableName].csv.dump"
 
mysql -h localhost -u [mysqlUsername] --password=[mysqlPass] --database=[mysqlDatabaseName] --execute="ALTER TABLE [mysqlTableName] DISABLE KEYS; LOAD DATA LOCAL INFILE 'C:\\tmp\\[mysqlTableName].csv' INTO TABLE [mysqlTableName] CHARACTER SET utf8 FIELDS TERMINATED BY '**EOT**' ENCLOSED BY '' ESCAPED BY '' LINES STARTING BY '**BOL****EOT**' TERMINATED BY '***EOL***'; ALTER TABLE [mysqlTableName] ENABLE KEYS;"
 
del "C:\tmp\[mysqlTableName].csv"
Here is an example of how to do it in the Windows Powershell, so you can step through line by line and troubleshoot (probably a better way, I saw something called PowerShell ISE.) 
bcp "select '**BOL**', a.* from [mssqlDatabaseName].[mssqlTableName] a;" queryout "C:\tmp\[mysqlTableName].csv.dump" -w -CRAW -t"**EOT**" -r"***EOL***" -S localhost -U "[mssqlUsername]" -P "[mssqlPass]"
 
cmd /c 'iconv -f UTF-16LE -t UTF-8 < "C:\tmp\[mysqlTableName].csv.dump" > "C:\tmp\[mysqlTableName].csv"'
 
del "C:\tmp\[mysqlTableName].csv.dump"
 
mysql -h localhost -u [mysqlUsername] --password=[mysqlPass] --database=[mysqlDatabaseName] --execute="ALTER TABLE [mysqlTableName] DISABLE KEYS; LOAD DATA LOCAL INFILE 'C:\\tmp\\[mysqlTableName].csv' INTO TABLE [mysqlTableName] CHARACTER SET utf8 FIELDS TERMINATED BY '**EOT**' ENCLOSED BY '' ESCAPED BY '' LINES STARTING BY '**BOL****EOT**' TERMINATED BY '***EOL***'; ALTER TABLE [mysqlTableName] ENABLE KEYS;"
 
del "C:\tmp\[mysqlTableName].csv"

Tags

Migration

Internal References

Migrate
Migrating Old HTML files to Drupal

Article Type

General