Any user who want's to Import other languages data into databases needs to overcome many challenges. Developers or administrators who are frequent visitors to database forums will find that importing data from Unicode languages such as Arabic, Chinese, Korean etc. is the most frequently asked questions in several database forums
Today we are going to show you how simple it is to import data from any Unicode format into MySQL, MS SQL Server, Oracle, MS Access, FoxPro using Data Loader the database import tool.
Data Loader can directly transfer Unicode / Non Unicode data from one database to another database automatically.
We will assume that we have Arabic data in TAB delimited format and we need to import it into a table in MySQL database.
Before we begin let's assume that
We are working on Windows 7
The MySQL Database is installed on the same machine (Doesn't make difference if it is install on another machine if it is your case. The only thing you need, is that you are able to connect to MySQL from this Windows PC
MS Excel is installed
Data Loader software is also installed.
The first thing required is that the target MySQL database where you want to import data is Unicode enabled.
If you want to load the data into already existing MySQL database then make sure its COLLATION is set to any Unicode supported collation. If the database is already existing and its collation does not support Unicode then, you can anytime change it to Unicode by giving an ALTER statement.
Like for example, you can give the following SQL command to change the collation
SQL>
ALTER
DATABASE
`test`
COLLATE
'utf8_general_ci';
If the target MySQL database does not exist and you need to create it then you can create it with a collation which supports Unicode characters. Like for example you can create a new MySQL database with the following command
SQL>
CREATE
DATABASE
`test`
COLLATE
'utf8_general_ci';
Once our target database is ready, now let's get to the next step.
Since we have Arabic data in a TAB delimited format and its content is as shown below
Now we will open this Text File in Excel
To do that
Start MS Excel
Place cursor in any empty cell and click Data Menu
Then Click on Get
External Data -> From Text in the toolbar as shown below
As soon as you click on this button Excel will prompt you to select the text file from where you want to import the data into Excel Sheet. Select your desired file and click Open.
Then Excel will show
you a dialog box as show below, in this dialog box select appropriate
character set. By default the character set will be set as Windows ANSI.
But since our data is in Unicode format we should select Arabic
character set
Click Next
Upon Clicking Next
button, the Import Wizard will ask you to choose the format of the
delimited file. i.e. whether it is Tab delimited or Comma Delimited and
so on. You can also specify the Text Qualifier used in your file.
Select appropriate options and click Next button as show above
In the Next screen
Excel will allow you to define the data types of the fields which. You
can select the columns of text as shown in lower part of the dialog
screen and select the appropriate data type in the above screen.
Make appropriate selection as per your data. In our case we have
defined first two columns as Text and left the third column to General
Now click Finish button to import the data into Excel
After Finish, Excel
sheet will be populated by the data from your file as shown below
Finally Save this Excel file in MS Excel XLSX format
Now Start Data Loader and Click Create New Session link
In the "Select Source and Destination database type" screen select Excel as source and MySQL as Destination and click Next as shown below
In the Next screen. Data Loader will prompt you to select the XLSX file to import the data from. And It will also asked you select the worksheets you want to import into MySQL.
Click Browse button and select the Excel file which we saved in the above step and click select sheet1$ and click Next button.
Upon clicking Next button, Data Loader will now prompt you to connect to the target MySQL database by typing your server address, username and password. Once you connect to the MySQL server successfully, Data Loader will populate databases dropdown list with the available databases in the server.
Select the database which we have discussed in the beginning of this article which supports Unicode characters.
In this example we are using testutf8 as our database
After clicking Next Button, Data Loader will show the Source & Destination Tables screen. In this section you can choose target table where the data should be loaded and you can even define column mappings by clicking "Col Mapping" button.
Since we don't need any changes other than the defaults, so we will just click Next Button
In the Next screen, Data Loader will prompt you to define Scheduler settings of this Session. You can choose these settings according to your requirements.
Since we just want to do this conversion only once. We will choose "Run it now only and don't save" option and click Next button.
Now we will come to the final step of Data Loader. We just need to click "Start Loading" button to start the conversion. You can view the progress in the text box as shown below
Once the job is finished, you can click Browse DB button to view the contents of the just transferred data.
So this finishes our importing of Arabic data from CSV / Text file to MySQL.
In this article we have shown how to move Unicode data from Text file to MySQL. In this article we have make use of Excel to first convert the Text file to Excel and then load the data from Excel to MySQL.
If we have the Unicode data in any other database then there is no need to use Excel and Data Loader can directly convert the Unicode data from MS SQL Server, Oracle, Access, FoxPro, MySQL to MySQL, MS SQL Server, Oracle, FoxPro.
Thanks for reading.