Replacing Long Strings In SQL Exports and Other Large Text Files

I’m migrating a web forum from one MySQL server to another, and it’s usually a straight-forward task consisting of exporting the data from one server importing an other server using phpMyAdmin.  This forum has been particularly active, so the SQL dump file containing the discussion table is almost two gigabytes. This wouldn’t be such a big deal if I were just importing into an identical database, but “business requirements” dictate that I change the name of the database.

Normally I would use Find/Replace in Dreamweaver, but being the bloated application it is, it kept throwing “out of memory” errors at me each time it hit the discussion export file. I suspected Notepad++ wouldn’t fare much better, so I started to Google for a solution.

A lot of options came up for Linux and Perl (which has always been awesome at parsing text), but I really didn’t want to have to move the file off my Windows 7 workstation over to the LAMP virtual server and just add more complexity to the confusion.

So I hit Google again with with a modified search (I just added Windows). I popped open a few tabs and browsed through a couple of options that would require yet-another GUI program installed on my machine. Some looked promising, but shareware always comes with a price, so I started digging into some of the more “hardcore” alternatives.

StackOverflow is one of my absolution favorite developer resources, and when I saw the link to “How can you find and replace text in a file using the Windows command-line environment?“, I knew my chances were good for finding a solution. StackOverflow contributor Mile Schall proposed a solution using Windows Power Shell, which proved to work extremely well.

First, anyone who isn’t familiar with Windows PowerShell should get familiar fast. It’s major upgrade over the old command prompt, gives access to some nifty .NET functionality, and will generally make life easier for anyone working with Windows Servers and Clients.

Following the StackOverflow advice, I launched PowerShell on my Windows 7 machine by going to:

START > All Programs > Accessories > Power Shell > Power Shell

I then navigated to the directory containing my SQL dump file, and entered the following command (of course the target text strings and file names have been changed for privacy):

   Get-Content SQLexport.sql | ForEach-Object { $_ -replace "replace this string", "with this string" } | Set-Content SQLexport2.sql

After a few minutes, the PowerShell prompt returned a new “SQLexport2.sql” file appeared in the directory.

This snippet has worked great for my large SQL files, and I suspect it will also work well for other large text-based files.

Now, the next trick is to get by the 2 MB file limit in phpMyAdmin so I can import the updated 2 gigabyte SQL dump file back into MySQL.

Leave a Reply