How to import and export a dataset in PhpMyadmin
Phpmyadmin
Phpmyadmin is a web application provided directly with the XAMPP package (https://www.apachefriends.org/download.html). It is used for the management of SQL tables. In fact, it allows you to create databases, tables, import and export data and run queries. In the default installation of XAMPP, Phpmyadmin is accessible at the following address: http: // localhost/phpmyadmin.
The initial screen of the application (shown in the following figure) is divided into two parts: on the left there are all the databases contained in MySQL (at the beginning it is empty), while in the right part, at the top there is a menu (in the figure indicated with a red rectangle), from which you can access the various operations.
Database Creation
To create a new database, in the top menu you need to select the database option. A screen will be shown with all the databases existing in the system (see figure below). In addition there is a text box (highlighted in the figure) that allows you to create a new database. You can also choose the character encoding. To support utf8 encoding, you should select the utf8_general_ci option.
Creating tables
To create a table, first you need to select the database you want to work with. Therefore, in the left menu, select the desired database. In the right part of the screen, all the tables in the database will appear. If the database is empty, no table will appear. At the bottom, after the list of all the tables, a small form appears, in which you can create a table (as shown in the figure below).
To create the table, just enter the desired name and the number of fields in the table. Click on the execute button. At this point another screen appears (see figure below) where you can enter the names of the columns of the table, the type (for example varchar, integer etc.), if the column is of the auto_increment (A_I) type, i.e. it is increased automatically by the system with a progressive number, each time a new record is inserted. In addition, you can specify other options, such as specifying whether the field cannot be null and any default value.
Importing a dataset
It is possible to import an existing dataset into a database. The default setting of Phpmyadmin allows you to import only files with a maximum size of 2,048 KBytes. In order to extend the maximum size, you need to edit the PHP configuration file, which is called php.ini. This file is located at:
- C: \ xampp \ php \ php.ini for Windows
- /Applications/XAMPP/xamppfiles/etc/php.ini for Mac
- /opt/lampp/etc/php.ini for Linux
Once the file is opened, the following lines must be changed:
- line 442: max_execution_time = 30 -> max_execution_time = 300
- line 452: max_input_time = 60 -> max_input_time = 600
- line 460: memory_limit = 128M -> memory_limit = 1024M
- line 735: post_max_size = 8M -> post_max_size = 512M
- line 1241: mysql.connect_timeout = 3 -> mysql.connect_timeout = 300
The upload_max_file_size parameter may also need to be changed. For example, set this parameter to 20M.
At this point, the system is configured to be able to load large files for which we can proceed.
First, in the menu on the left you need to select the database, without selecting any tables. Then in the menu at the top right you need to select import. A screen like the one shown in the figure below appears.
Select the button choose file and from your file system select the file containing the dataset you want to import (for example http://dati.toscana.it/dataset/lista-comuni-colpiti). If the file extension is .sql, you can directly press the run key. If instead the file extension is different, for example .csv, some precautions must be taken. First, you need to know what the field separation character is in the .csv file. By default, the character used is the, (comma), but it may happen that another character has been used, such as the; (semicolon). To find out what font is used, you can open the file separately with a program such as notepad or notepad ++ (or textWrangler for Mac) and see what font is used.
At this point in the format menu of the Phpmyadmin screen select csv. Another small menu appears (shown in the following figure), in which the separation character of the columns must be specified (fields terminated with). In addition, you can also tick the box The first line of the file contains the names of the fields in the table (if this option is not activated, the first line will become part of the data), so as to already have the names of the fields.
Press the execute key to start the import procedure. If all went well, the table was successfully imported.
Once the table has been imported, it is necessary to verify that the structure of the table is correct. In the left menu select the newly imported table (it should be called TABLE 1), and then in the right menu select Structure. Verify that the type of the fields is correct. In the case of the figure below, for example, the system has assigned the type varchar to latitude and longitude, which is not good.
To modify the type of a field (for example latitude and longitude), you need to select the modify key associated with the field. A screen like the one shown below opens:
In the Type pull-down menu, select DECIMAL, and in the Length / Values box, specify the length of the decimal number. You must specify the total number of digits of the decimal number and the number of digits after the comma. In our case the total number of digits is 8, while the number of digits after the comma is 6. Then write in the 8.6 field (eight digits in total, of which 6 after the comma). Press save. Do the same for longitude.
At this point you must specify the key of the table. To set a key as primary, just select the Primary key in the field you want to define as a key:
Now you can rename the whole table. In the top menu select Operations and from here rename table to:
Exporting a table or database
To export a table (or a database), select the table (or the entire database) in the left menu and in the top menu select the export option (see figure below). Then choose the format (for example SQL) and press the Run key.
