Can You Upload the Spreadsheet Data to My Sql

Import Excel File Into a MySQL Database

Created: December-28, 2021 | Updated: March-28, 2022

1. Use the LOAD DATA Statement to Import Information
2. Method 2: Apply Sqlizer.io to Import Data
3. Method 3: Import Data Using phpMyAdmin
4. Conclusions

This article will teach how to import an excel file into a MySQL database using Workbench, Control-Line Prompt, and phpMyAdmin. This tutorial volition take yous through a step-by-step guide of the top 3 easiest ways to import excel files into the MySQL databases.

Use the LOAD DATA Statement to Import Data

Using the LOAD Data statement in MySQL Workbench, you must take an empty tabular array in your MySQL database.

Nosotros have already created a table named tb_students. This table has id, firstname, lastname, gender, and city equally column names.

Nosotros will convert our file type from .xlsx to .csv to import data into the MySQL database. To do this, open your excel file that has the information.

Click on File->Save As. Make sure that y'all take selected CSV (Comma delimited)(*.csv) and and so printing the SAVE.

Come across the following screenshot.

You now have your data in the *.csv file. We will use the control below to import information from the .csv file into the MySQL database (using Workbench).

          # MySQL Version 8.0.27 LOAD Data INFILE 'C:/ProgramData/MySQL/MySQL Server viii.0/Uploads/top v easiest ways to import excel file into mysql database.csv' INTO TABLE tb_students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE i ROWS;                  

In the above LOAD DATA statement, the data from the given file volition exist collected and inserted into the mentioned table. The offset row will be ignored (the column names) because we already accept them while creating a table in MySQL Workbench.

In this example, the .csv file's fields are terminated by a comma. Yours may be terminated by a tab or single space.

If so, supplant the line FIELDS TERMINATED Past ',' with FIELDS TERMINATED BY '\t' or FIELDS TERMINATED By ' ' respectively. Let's await at the *.csv file'south data and so the MySQL data that are given below.

Data in .CSV File:

Data in MySQL Table:

You can also employ the LOAD Information argument from Control Line (CMD). We take created a new table named tb_students_cmd to practise the command line'due south LOAD Information statement.

Apply the LOAD Data statement to import data via a command-line prompt.

Allow's read the id from tb_students_cmd to confirm that the information is imported. Encounter the following screenshot.

If you see whatsoever error nearly security considerations while using the LOAD Information statement, then you may visit this website to encounter the possible solutions.

Method two: Use Sqlizer.io to Import Data

This method is useful if yous don't want to catechumen your file type from .xlsx to .csv. Become to sqlizer.io, follow the instructions as follows (or as per your needs).

Select your excel file and MySQL database blazon. Tell the sqlizer.io by checking or unchecking My File has a Header Row.

Check Utilise CHECK IF TABLE EXISTS if you lot desire to. If you want to use the agile worksheet, check Utilize the agile worksheet.

If not, so tell the worksheet name. We are non using information technology for this tutorial and prefer to tell the worksheet proper name, students.

Make sure to tick Catechumen the whole worksheet to import the whole information. Otherwise, give it the jail cell range.

You can write your table name (requite it a unique name) in which you want the data to be imported. Then, click on the Catechumen My File button.

You volition meet the following screen. You can either download the queries or copy them to execute wherever you want.

We can see the post-obit screenshot to ostend that the data is imported, and we tin read that.

Method 3: Import Data Using phpMyAdmin

If you lot are using phpMyAdmin, then yous can import data using the following steps (see the given screenshots).

To practice, we have created a new tabular array named tb_students_phpmyadmin. It has v columns named id, firstname, lastname, gender, and city.

Click on the Import tab and select your *.csv file. The Format would be CSV, but you can select co-ordinate to requirements.

Provide Format-specific Options and click on Go at the lesser-right corner.

You can run into that all the records are being imported inserted in the table named tb_students_phpmyadmin as follows.

Let'due south see whether the data is imported or not. And here information technology is! We take imported data.

Conclusions

Finally, nosotros have ended that you have to use dissimilar methods to import data into MySQL.

We saw two methods for importing data from the .csv file to MySQL. It includes the LOAD Data statement (in workbench and command line) and using phpMyAdmin.

We used sqlizer.io to import data from excel files to MySQL, which is very easy to use also.

Write for usa

DelftStack articles are written past software geeks like yous. If you also would similar to contribute to DelftStack by writing paid articles, you tin can bank check the write for united states page.

Related Article - MySQL Workbench

Create New Database in MySQL Workbench

Related Article - MySQL Import

Create New Database in MySQL Workbench

Import a CSV File Into a Table in a MySQL Database

hernandezmunter.blogspot.com

Source: https://www.delftstack.com/howto/mysql/import-excel-file-into-mysql-database/

Share this post