Piping into MySQL as UTF-8

Want a quick, lazy, command line way to pipe iso-8859-1 ( or any character encoding ) SQL into MySQL as UTF-8? Here's a little four-liner for you!

Scenario: 

You send an export of your content database to be translated into French.  When it comes back you want to pipe it back into MySQL but the text you copy out of Excel's not UTF-8.

We often handle foreign language data given to us as Excel spreadsheets and use CONCATENATE() to build up the SQL to pour the data into MySQL... it's not pretty but it's pretty quick and flexible.  We use the following four lines in our pipe to make sure the data's UTF-8 when it goes in.

#!/usr/local/bin/perl -w
use strict;
use Encode qw( from_to is_utf8 );
while (<>){
from_to( $_, "iso-8859-1", "utf8");
print;

Usage:

All you need to do is save that somewhere sensible and add it into your pipe:

#cat foo*.sql | ~/to-utf8.pl | mysql -p database

Question:

Anyone know why some French hyphens and apostropies are still difficult?

Grow your business

Find out how Deep Blue Sky can grow your business.

  1. Digital benchmark
  2. Digital roadmap
  3. Digital engineering

Write a comment.

Responses. (1)

  1. S T

    Simon

    Cool ...

    I'm going to try this ... I have to do this a lot ... I get a list of wines in excel and to get them into MySQL as UTF I import into Access then export as a CSV using the advanced option to set the text as UTF. Only then can I get it into MySql with all the weird chracters intact. Your suggestion looks a bit quicker! Thanks.