집 >데이터 베이스 >MySQL 튜토리얼 >Importing XML, CSV, Text, and MS Excel Files into MySQL_MySQL
My most recent articles,Importing XML Data into MySQL Tables Using a Stored ProcedureandEnhance Your MySQL XML Import Procedures using Prepared Statements, explored how capable stored procedures were in importing XML-formatted data. At the end of those articles, I concluded that as a DIY solution, stored procs are indeed a viable option. For those of you less inclined to write and maintain your own import code, there are tools that can markedly simplify the importing of data from various sources. In today’s article, I’m going to demonstrate how to use the Navicat Database Admin Tool to acquire data from XML, .csv, .txt, and Excel files.
In previous articles I employed a free MySQL GUI front-end called HeidiSQL. It was a great product, but, as some readers were apt to point out, it’s only available on Windows platforms. In an effort to meet the needs of the majority of readers, I opted to go with a product that runs on all the major OSes, namely the big three: Windows, Mac, and Linux.
Navicat is also a well-maintained product with an extremely large user base. Both those traits mean that bugs will be stamped out very quickly. Not that there would be many bugs left to find; at version 11.0.17, it’s a highly stable product at this point.
Note that this is a commercial product and requires a license after the free trial of 30 days. By that time you should have a much better idea whether or not it’s something that you want to invest in or not.
The trial version of Navicat for MySQL may be downloaded from thecompany’s website. The 30-day trial version of the software is identical to the full Enterprise Edition so you can get the full impression of all its features. Moreover, registering with PremiumSoft via thelocation 3links gives you free email support during the 30-day trial.
After you’ve downloaded the installation program, launch it and follow the instructions on each screen of the wizard to complete the installation.
To start working with your MySQL database, you must first establish a connection to it using the connection manager. To do that:
Figure 1: The New Connection Dialog
That will give you access to all the databases running on that server.
Figure 2: Selected Database
Alternatively, you can create a completely new database as follows:
Figure 3: The New Database Dialog
You could use the New Table wizard to create the target table, but I’ll give you the table definition to make things easier.
DROP TABLE IF EXISTS `menu_items`; CREATE TABLE `menu_items` ( `id` int(11) NOT NULL, `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL, `price` decimal(5,2) DEFAULT NULL, `description` varchar(255) CHARACTER SET latin1 DEFAULT NULL, `calories` smallint(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Figure 4: navicat_imports_db Table
We are now ready to import some data.
In keeping with the theme of my last two articles, I’ll start with XML.
The XML document that I’ll be using today is a sample document of menu items. It contains information about some typical breakfast foods.
<?xml version="1.0" encoding="UTF-8"?><breakfast_menu> <food id="1"> <name>Belgian Waffles</name> <price>$5.95</price> <description>Two of our famous Belgian Waffles with plenty of real maple syrup </description> <calories>650</calories> </food> <food id="2"> <name>Strawberry Belgian Waffles</name> <price>$7.95</price> <description>Light Belgian waffles covered with strawberries and whipped cream</description> <calories>900</calories> </food> <food id="3"> <name>Berry-Berry Belgian Waffles</name> <price>$8.95</price> <description>Light Belgian waffles covered with an assortment of fresh berries and whipped cream</description> <calories>900</calories> </food> <food id="4"> <name>French Toast</name> <price>$4.50</price> <description>Thick slices made from our homemade sourdough bread</description> <calories>600</calories> </food> <food id="5"> <name>Homestyle Breakfast</name> <price>$6.95</price> <description>Two eggs, bacon or sausage, toast, and our ever-popular hash browns</description> <calories>950</calories> </food></breakfast_menu>
In Navicat, imports are accomplished using the Import Wizard. It will guide you through all of the steps based on the type of data source selected. One way to launch the wizard is to right-click on target table and then selectImport Wizardfrom the popup menu:
Figure 5: Import Wizard Command
Figure 6: Import Wizard Data Format Screen
Figure 7: Import Wizard Target Table Screen
Figure 8: Import Wizard Field Mappings Screen
Figure 9: Import Wizard Import Mode and Advanced Properties Dialog
Figure 10: Import Wizard Start Screen
Now that you’ve gone through all of the steps to setup your import process, you don’t have to repeat them every time you want to import some records. You can save it via the Save button. That will allow you to run your import as a Scheduled Job as well.
Figure 11: Scheduled Jobs List
Click the menu_items table to see its contents, which include our imported data.
Figure 12: The menu_items Table with Imported XML Data
At this time Navicat is limited to a single level of XML data. Hopefully, that will be improved upon in an up-coming release.
A Fixed Width text file is a data transfer format that is often used with mainframe data feeds. In a Fixed Width text file, fields are stored in specific positions within each line of data. For example, in each line of the breakfast_menu row structure below, thenamefield occupies the fifty character positions of 11 through 60 inclusive:
_10 chars_ _50 chars_ _10 chars_ _255 chars_ _10 chars_ |||| || id name pricedescription calories
The greatest advantage of the Fixed Width format is that there are no delimiters that could appear in the data, as with CSV files.
Follow this procedure to create a Fixed Width file and import its contents into MySQL:
id name pricedescriptioncalories 1 Belgian Waffles $5.95Two of our famous Belgian Waffles with plenty of real maple syrup 650 2 Strawberry Belgian Waffles$7.95Light Belgian waffles covered with strawberries and whipped cream 900 3 Berry-Berry Belgian Waffles $8.95Light Belgian waffles covered with an assortment of fresh berries and whipped cream 900 4 French Toast$4.50Thick slices made from our homemade sourdough bread 600 5 Homestyle Breakfast $6.95Two eggs, bacon or sausage, toast, and our ever-popular hash browns 950
Figure 13: Import Wizard Field Delimiter Screen
Figure 14: The menu_items Table with Imported Fixed Width Data
The CSV ("Comma Separated Value") file format originated in Microsoft Excel, but has since become a pseudo standard throughout the industry, even among non-Microsoft platforms. As is the case with most exchange formats since XML, CSV files have been relegated to that of legacy format. Modern applications that include an export format tend to use XML today.
Here is the breakfast menu data again, this time using the CSV format.
id,name,price,description,calories 1,"Belgian Waffles","$5.95","Two of our famous Belgian Waffles with plenty of real maple syrup",650 2,"Strawberry Belgian Waffles","$7.95","Light Belgian waffles covered with strawberries and whipped cream",900 3,"Berry-Berry Belgian Waffles","$8.95","Light Belgian waffles covered with an assortment of fresh berries and whipped cream",900 4,"French Toast","$4.50","Thick slices made from our homemade sourdough bread",600 5,"Homestyle Breakfast","$6.95","Two eggs, bacon or sausage, toast, and our ever-popular hash browns",950
The CSV format is a lot more compact than fixed widths because each field only needs to be as long as its content. It’s also easier to parse because of the clearly identified delimiter. Although the comma is used by convention, really any character may be used. Whatever character you do opt for, be extra careful that it does not appear in any of the data because that will wreak havoc on the import process! In fact, the description for the Homestyle Breakfast above does contain several commas. To get around this, we can either substitute a different delimiter, or enclose all string data within quotes, as I did.
The process for importing CSV files is very similar to text data except that on screen three, the delimited and fixed width radio buttons are disabled so thatDelimitedis the only option.
Figure 15: Import Delimiter Screen for CSV Data
Other than that, the two formats are really quite interchangeable.
Although Excel provides the CSV format for transferring data, Navicat can import directly from Excel.
On the File Type screen of the Import Wizard, notice that there are actually two radio buttons for Excel: one for .xls files and one for the newer .xlsx 2007 and later format. Choose the one for your version of Excel.
One Excel file may contain numerous workbooks, so you can import from more than one at a time.
From there, the process is not much different than for any other file type. Just make sure that your fields are correctly mapped and that you start from the second row if you have column headers in your data.
For Database Administrators who are not inspired to write and maintain their own import procedures, Navicat does a good job of importing data from external data sources.
See all articles by Rob Gravelle