How to Read in a Csv File With Columns Containing Commas

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

Introduction

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

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

Prerequisites

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

Import CSV File Using Command Line

Step ane: Admission MySQL Trounce

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

                mysql –u username –p              

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

Pace 2: Create MySQL Tabular array for CSV Import

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

Select a database by entering the following control:

                USE database_name;              

Replace database_name with the name of the database you lot are importing data into. Use the following control to create a new table:

                CREATE TABLE table_name (             id INT NOT Zilch AUTO_INCREMENT,             column_1 VARCHAR(255) Non Naught,             column_2 DATE NOT NULL,             column_3 DECIMAL(10 , 2 ) Cypher,             column_4 INTEGER,             Master KEY (id) );                              

Replace the table_name value with the name 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 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, delete, or modify the data types used in this example to fit your needs. Visit the official MySQL documentation on data types to discover out more.

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 '/home/export_file.csv' INTO Tabular array table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n' IGNORE 1 ROWS;              

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

• LOAD Information INFILE – Defines the location of the CSV file to exist 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 can use the LOAD DATA LOCAL INFILE statement instead.
• INTO TABLE – This indicates the destination table you are importing the CSV file into. Modify the table_name to the proper noun of your table.
• FIELDS TERMINATED BY – By default, comma-separated value files use a comma to identify private data values. If your export file uses a different delimiter, you can modify this value.
• ENCLOSED Past – This specifies that a double-quote mark " surrounds values.
• LINES TERMINATED Past – Apply this line to specify the code for a line break.
• IGNORE 1 ROWS; – Many CSV files export with the column labels as the starting time 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 terminate 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 use the graphical interface to import a CSV file.

1. Access cPanel and launch phpMyAdmin.

two. Utilize the left pane to select the database and table yous are importing the CSV file into.

three. Use the top carte to select Import.

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

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

vi. Select Go to initiate the CSV import.

Conclusion

Y'all now know how to import CSV files into MySQL, both from the control line or by using phpMyAdmin. The methods outlined in this tutorial enable y'all to move data betwixt 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 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 past providing applied advice and using an engaging writing style.

medeirosaftefuld66.blogspot.com

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

Share this post