How to Upload Csv File in Mysql
MySQL Import CSV File in Database/Table
How tin can we import CSV files in MySQL database or table?
MySQL has a feature that allows us to import the CSV file into a database or table. A CSV stands for comma-separated values, which is a plain text file that contains the list of data and allows us to relieve it in a tabular format. It is mainly used to exchange data between dissimilar applications. Sometimes it is also called as graphic symbol separated values. Generally, information technology is used with the comma character to delimit (divide) data, simply can likewise use other characters, like semicolons.
This file is very beneficial when nosotros need to export complex data from one application to a CSV file, and then import the file data into another awarding. We can utilize the CSV file with a diverse spreadsheet program similar MS Excel or Google Spreadsheets. A CSV file cannot allow saving formulas in this format.
Construction of CSV File
The structure of the CSV file contains a list of data that is separated by a comma operator. For example, the below data structure explains it more clearly:
To import a CSV file, nosotros volition apply the LOAD DATA INFILE statement. This statement is used to read a text file and import it into a database tabular array very quickly.
We take to ensure the following things before importing a file into the database table:
- An empty table where the information from the file will be imported.
- A CSV file that matches the society, number of columns, and data type in each column of the table.
- The user account connected with the database server in MySQL has a FILE and INSERT privileges.
Suppose we have a database named mytestdb. Now, we volition create an empty table named Address_Book in this database using the beneath query:
Now, we can verify whether the newly created table is empty or not using the SELECT argument that can be shown in the below output:
Adjacent, we will create a CSV file. The following address_book.csv file contains the get-go line as column headings, and other lines are the information to be inserted into the table.
Finally, the following statement allows u.s.a. to import data from the address_book.csv to the address_book tabular array.
From the above,
File Location: Information technology specifies the location of the CSV file into the system.
FIELD TERMINATED BY ',': It is used to indicate the field of the file, which is terminated past a comma.
ENCLOSED BY '"': It is used to specify the field of the file enclosed by double quotation marks.
LINES TERMINATED BY '\r\n': It is used to terminate each line of the CSV file.
IGNORE 1 ROWS: It is used to ignore the commencement line of the CSV file, which has column headings that are not required to import into the table.
We tin can now verify whether the newly created tabular array is filled with the imported data or not using the SELECT statement that tin be shown in the below output.
Transforming data while importing
Sometimes, while importing the CSV file into the database table, the data format does not match the corresponding cavalcade in the table. In such a case, we tin can transform that column past using the Gear up clause with the LOAD Data INFILE argument.
Suppose the date cavalcade of a CSV file is in the format of "dd/mm/yy", and information technology does not match the corresponding column of the target table while importing. To overcome this outcome, nosotros need to transform it into a MySQL date format using STR_TO_DATE() function as follows:
Importing CSV file from Client to MySQL Server
The LOAD DATA INFILE argument also allows us to import CSV file form customer (local organisation) to a remote MySQL Server.
When nosotros add the LOCAL clause in the LOAD Information INFILE statement, the client program tin can read the CSV file on the local system and sends information technology to the MySQL database server. This file will and then be uploaded into the temporary folder of the database server operating system such as C:\windows\temp on Windows and /tmp on Linux Os. These folders are non adamant or configurable by MySQL.
The following statement explains it more than clearly:
In the above, we tin can encounter that we have but added the LOCAL option in the statement. But while importing the big CSV file with the LOCAL pick, brand the file loading a niggling slower. It is because this option takes time to transfer the file on the database server.
When we utilise the LOCAL option, the MySQL user account does non demand to have the FILE privilege for importing it.
The LOAD DATA LOCAL INFILE statement has some security issues while importing the file from local to a remote server. In such a case, nosotros should exist aware of to avert potential security risks.
Importing CSV file using MySQL Workbench
MySQL Workbench has an in-built tool that allows us to import the CSV file data into a table. It also helps us to edit information before making changes in the database or table.
The post-obit are the steps that evidence to import CSV file data into a database tabular array:
Pace 1: First, we need to create a table in the desired database. It ensures that the table has the same number of columns every bit in our CSV file. Suppose, nosotros have a table named "address_book" that does not incorporate any information shown in the below paradigm:
Step 2: Once nosotros open the table, we can import the file using the import push button shown in the red color box of the below screen.
Click on the import button, and information technology will pop upwards the window screen where we need to choose a CSV file and so click on the Side by side button.
Step 3: In the next wizard, we need to select the destination tabular array and click on the Next button. Here, we have two options ane is to use an existing table, or nosotros can create a new table.
Pace 4: In the next wizard, we need to configure the import settings and click on Adjacent.
Step v: In the next wizard, nosotros have to monitor the execution process and click on Adjacent->Adjacent>Cease to consummate the procedure.
Footstep half dozen: We tin can verify the resulting weather the data imported successfully or not by executing the SELECT query.
NOTE: From the higher up techniques, nosotros can likewise load data from other text file formats.
Source: https://www.javatpoint.com/mysql-import-csv-file-in-database
0 Response to "How to Upload Csv File in Mysql"
Postar um comentário