When working with databases it is always necessary to import data or schemas. In this article we describe the process of importing data from a text file into a MySQL database, and also we discuss questions concerning problems with MySQL import and the ways of solving these problems. We will give a detailed description of the Data Import tool of dbForge Studio for MySQL, describe the capabilities of this tool and illustrate its usage.
What problems can be experienced when importing data from a text file?
To specify all problems one can experience when importing data from a text file it’s necessary to remember the specificity of storing text data:
- data in text files is always formatted, and formatting is free;
- tabular data in text files can be presented together with its header, i.e. with column names and certain formatting;
- the type of data in text files can not be distinguished, that’s why there are problems with types conversion and their correct formatting;
- if data in a file is presented not in the form of a grid, but as a free text, then it’s rather difficult to separate out the needed data block to find the correspondence with the needed column in the table.
MySQL import solution offered by Devart
Data Import functionality is widely presented in dbForge Studio for MySQL. The instrument allows to import data from CSV, Txt, Excel, Access, DBF, XML, ODBC formats. The capability of importing from ODBC will allow a user to migrate data from other database providers. In this article we will give a detailed description of the process of importing data from a text file.
Data in a text file can be:
- stored as columns of fixed width;
- separated by a delimiter;
- of free format.
There are settings for each of these variants in Data Import Wizard of dbForge Studio for MySQL (Figure 1). As you can see on the image below, you can setup data splitting in a file by three ways:
- manual – data is splitted manually. This mode allows you to place the splitting markers as you consider it necessary;
- fixed width – splitting by columns of fixed width;
- delimiter – splitting text in accordance with a delimiter symbol. In this case you can choose Tab, space or your own symbol as a delimiter (for example, it is “;” for CSV).
Figure 1. Options for import data from text file
Also on the Options page of dbForge Studio for MySQL Data Import Wizard you can select file encoding, set the quotation symbol for strings, choose the header position (on Figure 1 the header is marked with a blue line), the number of strings that should be skipped in the beginning of the file (on Figure 1 it is shown as a rectangle with red lines), and the possibility to import even lines (in our example we want to skip horizontal division of records with the “-” symbol).
It is necessary to state that when importing data from a text file all extra spaces will be removed from all values automatically. If you want to save some spaces in data, the quotation symbol specified on the Options page of Data Import Wizard will help you to do this. All data marked with quotation symbols will be imported in original form without cutting.
The result of splitting can be viewed on the next page of the wizard – the Mapping page (see Figure 2).
Figure 2. Mapping splitted data to table columns
This page allows to set the columns of the selected table to which the data received after splitting a text file should be imported. On the image we can see that the mapping was set automatically owing to the header with column names from the text file. But it is not always the case.
In case when automatic mapping did not manage to map columns correctly, you can choose the needed column from the splitted text file manually using a drop-down list in the upper columns list (as you can see on Figure 2).
You have such opportunity if data in text file could be splitted by a delimiter. But if splitting is performed manually or by columns of fixed width, the situation in this case is a bit different (see Figure 3).
Figure 3. Mapping custom data to table columns
Of course you can perform mapping automatically using the Fill Mapping button (to perform this action correctly there should be a header before data), but to setup the mapping manually you should choose a column in the table and a block of data in the file preview window and press the Map button. As a result the position and size of the block from the file, data from which will be imported to the selected column, will be shown in the upper columns list.
As you can see from Figure 3, you can specify if a column is a key column in the upper list. This criterion will be used when working with modes (see the “Additional capabilities of data import in the dbForge Studio for MySQL” chapter of this article).
After setting the mapping of columns and data from a file, the wizard offers to setup data formats (see Figure 4). This point is exteremly important when working with a text file, because data formats are not specified there (see the “What problems can be experienced when importing data from a text file?” chapter).
Figure 4. Common and columns data formats
On the Data Formats page you are offered to set common formats (setting the null value, thousand separator, decimal separator, boolean values, and date and time) as well as setting data formats for each column (it is necessary if any column contains data stored in a format different from the common format). When starting MySQL import, data will be loaded into a table in a column format with the settings listed on this page regardless the fact that all data was textual.
Additional capabilities of MySQL import in dbForge Studio
Data Import Wizard offers additional capabilities of data import. These are: possibility to import data into an existing or a new table, import modes, handling errors that may occur during the import process, logging of data import process.
When importing data to an existing table one should keep in mind that this table may already contain data, and conflicts that may appear in case data in key columns is identical should be somehow resolved. For this dbForge Studio offers special modes:
- Append. All records will be added to the table;
- Update. All records keys of which match the keys specified on the Mapping page (see Figure 3) will be updated;
- Append/Update. This mode combines two previous modes. In case a record exists in the table it will be updated, otherwise it will be added;
- Delete. All matching records will be deleted;
- Repopulate. This mode allows to delete all records existing in the table before importing data from the source.
In case of a new table Data Import Wizard will create a table with the columns available in the source. A user can always correct the names and types of the columns and their other settings with the help of Column Editor on the Mapping page (the Edit button on the toolbar).
To complete the description of Data Import Wizard we would like to describe the last page of the wizard that allows to write the import report to the log file and handle errors that may occur during the import process (see Figure 5).
Figure 5. Errors handling and log settings
Errors handling consists of specifying how the application should behave when an error is encountered. It can: show a message asking about further actions, ignore all errors, or abort the import process at the first error.
All information concerning the import process with all errors that may have been encountered will be written to a log file if the corresponding option was checked. This option will help you to identify the problems and take necessary steps to resolve them.
Conclusion
In this article we tried to give a detailed description of Data Import tool available in dbForge Studio for MySQL on the example of a text file. For truth’s sake we should say that text file is one of the most difficult formats for data import, and that’s why we’ve written such a detailed description of the process of importing it. The import process of other formats has similar functionality, but with some additional specific options.
PlanetMySQL Voting: Vote UP / Vote DOWN
