How to Upload Text File to Sql Ubuntu

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 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 data yous want to import
• A MySQL user account with FILE and INSERT privileges
• Pre-configured phpMyAdmin account (optional)

Import CSV File Using Control Line

Step 1: Access MySQL Shell

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

                mysql –u username –p              

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

Pace 2: Create MySQL Table for CSV Import

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

Select a database by inbound the post-obit command:

                Utilize database_name;              

Replace database_name with the proper noun of the database you are importing data into. Use the post-obit control to create a new table:

                CREATE TABLE table_name (             id INT NOT NULL AUTO_INCREMENT,             column_1 VARCHAR(255) NOT NULL,             column_2 Date Non Null,             column_3 DECIMAL(x , two ) NULL,             column_4 INTEGER,             Primary KEY (id) );                              

Supersede the table_name value with the name you want to utilize for your tabular array. The column_n variable represents the names for each cavalcade in your CSV file, and you should edit them accordingly.

• 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 tin can add together, delete, or modify 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 3: Import CSV into MySQL Table

Import the data from the CSV file into the MySQL database, using the following lines:

                LOAD Information INFILE '/domicile/export_file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED By '/due north' IGNORE 1 ROWS;              

Permit us, in turn, explore the deportment backside each of these lines:

• LOAD DATA INFILE – Defines the location of the CSV file to be imported. Modify the path (betwixt the quotes) to friction match the path and filename of your CSV file. If the CSV file is located on the local automobile, you can utilise the LOAD DATA LOCAL INFILE statement instead.
• INTO TABLE – This indicates the destination tabular array you are importing the CSV file into. Change the table_name to the name of your table.
• FIELDS TERMINATED Past – By default, comma-separated value files use a comma to place individual data values. If your consign file uses a dissimilar delimiter, you tin modify this value.
• ENCLOSED BY – This specifies that a double-quote mark " surrounds values.
• LINES TERMINATED BY – Use this line to specify the lawmaking for a line suspension.
• IGNORE 1 ROWS; – Many CSV files export with the column labels every bit the offset line. This command tells MySQL to ignore the starting time row as yous have already created your table with the appropriate column headings. The semicolon at the end specifies the end of the control for MySQL to execute.

Import CSV File with phpMyAdmin

If your server or database uses phpMyAdmin, you can use 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 y'all are importing the CSV file into.

3. Use the elevation menu to select Import.

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

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

6. Select Go to initiate the CSV import.

Decision

You now 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 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 seven years of feel 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 fashion.

segernothiphop.blogspot.com

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

Bagikan Artikel ini