How to load xls/xlsx file to MySQL database using sql code

Posted by

To load data from an Excel (.xls or .xlsx) file into a MySQL database, you can use the MySQL LOAD DATA statement. However, MySQL does not directly support Excel files, so you’ll need to convert the Excel data into a CSV (Comma-Separated Values) format first. Then, you can use the LOAD DATA INFILE statement to import the CSV data into the MySQL database. Here’s a step-by-step guide:

  1. Convert Excel to CSV:
  • Open the Excel file and save it as a CSV file. Choose “CSV (Comma delimited) (*.csv)” as the file format while saving.
  1. Upload the CSV File:
  • Make sure the CSV file is accessible from the MySQL server. You can upload the CSV file to the server or ensure it is in a directory that MySQL can access.
  1. Use LOAD DATA INFILE SQL Statement:
  • Connect to your MySQL database using a MySQL client or command-line tool.
  • Use the following SQL statement to load the data from the CSV file into a MySQL table:
LOAD DATA INFILE 'path/to/your/csv/file.csv'
INTO TABLE your_table_name
IGNORE 1 ROWS; -- If the CSV file contains a header row, use IGNORE 1 ROWS to skip it

Replace 'path/to/your/csv/file.csv' with the actual path to your CSV file, and your_table_name with the name of the MySQL table where you want to load the data. Ensure that the table structure matches the CSV data columns in the correct order.

The FIELDS TERMINATED BY ',' specifies that the fields in the CSV file are separated by commas. You can change the delimiter if your CSV file uses a different character to separate fields.

The ENCLOSED BY '"' indicates that fields enclosed by double quotes. If your CSV file uses a different character for enclosing fields, adjust it accordingly.

The LINES TERMINATED BY '\n' specifies that each line in the CSV file ends with a newline character. If your CSV file has a different line termination character, update it accordingly.

  1. Verify Data Load:
  • Once the LOAD DATA statement is executed, verify that the data has been successfully loaded into the MySQL table.

Please note that when using LOAD DATA INFILE, you need appropriate permissions to access the file system and read the CSV file. If your MySQL server is running on a hosted platform, make sure you have the necessary permissions or consult with your hosting provider.

Additionally, ensure that the data in the CSV file matches the table’s structure in terms of column names and data types to prevent any data import issues.