Download Csv File on Command Line From and Upload to Another Sire

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

Introduction

A CSV (Comma Separated Values) file uses commas to dissever dissimilar values inside the file. The CSV file is a standard format when transferring a table to a dissimilar system or importing it to some other database application.

This tutorial shows you lot how to import a CSV file into your MySQL database in a few short steps.

Prerequisites

• Access to a command line / terminal window
• A CSV file containing the information you want to import
• A MySQL user business relationship with FILE and INSERT privileges
• Pre-configured phpMyAdmin account (optional)

Import CSV File Using Command Line

Step 1: Access MySQL Trounce

Admission your last window and log into MySQL using the following command:

                mysql –u username –p              

Supercede username with your bodily username. The system prompts yous to enter the password for your MySQL user. Entering the correct password gives you lot access to the MySQL client.

Pace 2: Create MySQL Tabular array for CSV Import

The columns in your MySQL table need to match the data from the CSV file you plan to import. If you already have a table set for the CSV import, you can skip to Pace 3 of the tutorial.

Select a database past entering the post-obit command:

                Apply database_name;              

Replace database_name with the name of the database you are importing information into. Utilise the post-obit command to create a new tabular array:

                CREATE Tabular array table_name (             id INT Non Cipher AUTO_INCREMENT,             column_1 VARCHAR(255) Not NULL,             column_2 Appointment NOT Cypher,             column_3 DECIMAL(10 , two ) Cipher,             column_4 INTEGER,             PRIMARY Primal (id) );                              

Supercede the table_name value with the proper name y'all want to use for your table. The column_n variable represents the names for each cavalcade in your CSV file, and you lot should edit them accordingly.

• In this instance, 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 can add together, delete, or change the data types used in this example to fit your needs. Visit the official MySQL documentation on data types to find out more than.

Step three: Import CSV into MySQL Table

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 BY '"' LINES TERMINATED BY '/due north' IGNORE 1 ROWS;              

Let us, in plough, 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 lucifer the path and filename of your CSV file. If the CSV file is located on the local car, yous can use the LOAD Data LOCAL INFILE argument instead.
• INTO Tabular array – This indicates the destination table y'all are importing the CSV file into. Change the table_name to the name of your tabular array.
• FIELDS TERMINATED BY – Past default, comma-separated value files use a comma to place individual data values. If your export file uses a different delimiter, yous tin modify this value.
• ENCLOSED BY – This specifies that a double-quote marker " surrounds values.
• LINES TERMINATED BY – Use this line to specify the code for a line break.
• IGNORE i ROWS; – Many CSV files consign with the cavalcade labels as the beginning line. This command tells MySQL to ignore the kickoff row as y'all have already created your table with the appropriate column headings. The semicolon at the end specifies the cease of the command for MySQL to execute.

Import CSV File with phpMyAdmin

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

one. Access cPanel and launch phpMyAdmin.

2. Employ the left pane to select the database and table yous are importing the CSV file into.

three. Use the top menu to select Import.

four. Click Choose File and scan to the CSV file location.

5. Use the Format drop-down bill of fare to selectCSV and the Format-specific options to ascertain options for the individual columns.

six. Select Get to initiate the CSV import.

Conclusion

You lot now know how to import CSV files into MySQL, both from the control line or past using phpMyAdmin. The methods outlined in this tutorial enable yous to move data between systems and different database applications.

Was this article helpful?

Yes No

Vladimir Kaplarevic

Vladimir is a resident Tech Author at phoenixNAP. He has more than than 7 years of experience in implementing eastward-commerce and online payment solutions with various global IT services providers. His articles aim to instill a passion for innovative technologies in others by providing practical advice and using an engaging writing way.

sandersyebere00.blogspot.com

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

Share this post