Import data into MySQL from any file

by

How to Import data into MySQL from any file:
Mysql –u root <db.sql (for database and tables)
Mysql –u root <data.sql (for data into tables)

To import data into MySQL from a file, you can use the LOAD DATA INFILE statement. Here’s a basic example of how to use it:

sql
LOAD DATA INFILE 'path/to/your/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- if your file has a header and you want to ignore it

Explanation:

  • LOAD DATA INFILE: This is the MySQL statement used to import data from a file into a table.
  • 'path/to/your/file.csv': Specify the path to your file.
  • INTO TABLE your_table: Specify the table where you want to import the data.
  • FIELDS TERMINATED BY ',': Specify the field delimiter. In this example, it’s a comma, but you can change it based on your file’s format.
  • ENCLOSED BY '"': Specify the character used to enclose fields, often used when dealing with CSV files.
  • LINES TERMINATED BY '\n': Specify the line terminator, which indicates the end of each record in the file.
  • IGNORE 1 LINES: This option is used if your file has a header line that you want to skip.

Make sure that the MySQL user has the necessary permissions to read the file, and the file is accessible to the MySQL server. Additionally, be cautious about security concerns when using LOAD DATA INFILE and only load data from trusted sources.

Remember to adjust the statement according to your specific file format and table structure.