How to import .csv files in database from phpMyAdmin July 15, 2020

This guide will discover the steps required to import .cvs files in database from phpMyAdmin.

Follow the steps to import .csv files in database:

1. Save Your File in the Correct Format:
   1. Convert your Excel file to a .csv format.
   2. Do not save it as a workbook or any other format.
   3. Verify the file using a text editor (e.g., Notepad) to ensure each column is separated by a comma (,).
2. Prepare Your Data:
   1. Remove column headers and blank spaces.
   2. Ensure every row and column contains valid data.
   3. The structure of the .csv file should exactly match your database table. If your table has n columns, your CSV file must also have n columns.

Steps to Import a .CSV File into a Database:

1. Upload the .csv file: Use FTP to upload your .csv file to the public_html directory.
2. Access phpMyAdmin:
   1. Open phpMyAdmin.
   2. Select your database.
   3. Click on the SQL tab.
3. Run the SQL Query:
   Execute the following query, replacing the placeholder with the actual file path:

   LOAD DATA LOCAL INFILE 'Full path to .CSV file'
   INTO TABLE city
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n';

   Example:

   LOAD DATA LOCAL INFILE '/home/username/public_html/filename.csv'
   INTO TABLE city
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n';

4. Click on “Go” to execute the query.

That’s it! Importing .csv files into a database using phpMyAdmin is simple. Its user-friendly interface makes managing large datasets efficient and hassle-free.