How to import and export a dataset in PhpMyadmin

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

Creating tables

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

  • 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

Top 1000 Medium Writer in May, June and July 2021. I write on Data Science, Python, Tutorials and, occasionally, Web Applications.