When You Upload Through Myphpadmin to You Have to Create Key

Home » SysAdmin » How to Import a CSV file into a MySQL database?

Introduction

A CSV (Comma Separated Values) file uses commas to separate different values within the file. The CSV file is a standard format when transferring a table to a different system or importing information technology to some other database application.

This tutorial shows yous how to import a CSV file into your MySQL database in a few brusque steps.

Prerequisites

• Admission to a command line / terminal window
• A CSV file containing the data you lot desire to import
• A MySQL user account with FILE and INSERT privileges
• Pre-configured phpMyAdmin account (optional)

Import CSV File Using Command Line

Pace 1: Access MySQL Shell

Access your concluding window and log into MySQL using the following command:

                mysql –u username –p              

Replace username with your bodily username. The arrangement prompts you to enter the password for your MySQL user. Entering the correct password gives you lot admission to the MySQL client.

Step 2: Create MySQL Table for CSV Import

The columns in your MySQL table need to match the information from the CSV file you lot plan to import. If you lot already have a table fix for the CSV import, you lot can skip to Step iii of the tutorial.

Select a database past inbound the post-obit control:

                USE database_name;              

Supplant database_name with the name of the database you are importing data into. Employ the following command to create a new tabular array:

                CREATE Tabular array table_name (             id INT Not NULL AUTO_INCREMENT,             column_1 VARCHAR(255) Non Nada,             column_2 DATE Non Zippo,             column_3 DECIMAL(10 , 2 ) NULL,             column_4 INTEGER,             PRIMARY Fundamental (id) );                              

Supersede the table_name value with the proper noun y'all want to use for your table. The column_n variable represents the names for each column in your CSV file, and you should edit them appropriately.

• In this example, column_1 is formatted for text.
• column_2 is formatted for dates.
• column_3 is formatted for currencies. The numbers in the parentheses indicate the maximum size of the value and the decimal places.
• column_4 is formatted for whole numbers.

You lot can add, delete, or modify the data types used in this example to fit your needs. Visit the official MySQL documentation on data types to observe out more than.

Step iii: Import CSV into MySQL Tabular array

Import the data from the CSV file into the MySQL database, using the post-obit lines:

                LOAD DATA INFILE '/home/export_file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED Past '"' LINES TERMINATED BY '/north' IGNORE 1 ROWS;              

Allow u.s.a., in plow, explore the actions behind each of these lines:

• LOAD Data INFILE – Defines the location of the CSV file to be imported. Change the path (between the quotes) to match the path and filename of your CSV file. If the CSV file is located on the local machine, you lot can utilise the LOAD DATA LOCAL INFILE statement instead.
• INTO Tabular array – This indicates the destination tabular array you are importing the CSV file into. Change the table_name to the proper name of your tabular array.
• FIELDS TERMINATED Past – By default, comma-separated value files use a comma to identify individual data values. If your consign file uses a different delimiter, y'all can change this value.
• ENCLOSED Past – This specifies that a double-quote mark " surrounds values.
• LINES TERMINATED Past – Use this line to specify the code for a line interruption.
• IGNORE ane ROWS; – Many CSV files consign with the column labels equally the get-go line. This command tells MySQL to ignore the first row as you have already created your table with the appropriate column headings. The semicolon at the end specifies the end of the command for MySQL to execute.

Import CSV File with phpMyAdmin

If your server or database uses phpMyAdmin, you tin can utilise the graphical interface to import a CSV file.

1. Access cPanel and launch phpMyAdmin.

2. Utilise the left pane to select the database and tabular array you lot are importing the CSV file into.

three. Use the top bill of fare to select Import.

4. Click Cull File and browse to the CSV file location.

5. Use the Format drop-down menu to selectCSV and the Format-specific options to define options for the individual columns.

6. Select Become to initiate the CSV import.

Conclusion

You at present know how to import CSV files into MySQL, both from the command line or past using phpMyAdmin. The methods outlined in this tutorial enable you lot to motility data between systems and different database applications.

Was this article helpful?

Yes No

Vladimir Kaplarevic

Vladimir is a resident Tech Writer at phoenixNAP. He has more than vii years of experience in implementing e-commerce and online payment solutions with various global Information technology services providers. His articles aim to instill a passion for innovative technologies in others past providing applied advice and using an engaging writing mode.

coonmagnstowiter.blogspot.com

Source: https://phoenixnap.com/kb/import-csv-file-into-mysql

Share this post