Well, Database and Excel both handles a large amount of data. Suppose you required a new table in MySQL and the content is available in MS Excel Spreadsheet then you will need to migrate the files from MS Excel to MySQL. Apart from copying each row and column of excel sheet to MySQL table, you can directly export it with the help of MySQL for Excel.
MySQL for Excel is a free tool that allows importing MySQL Data to MS Excel, Export MS Excel Data to MySQL, Edit MySQL Database and appending the database tables. Thus, a single tool can be used for to and from the migration of MySQL to Excel and Excel to MySQL and that is even without running a query. This saves a lot of time and ensures complete excel rows are transferred to MySQL without any error.
Users can specify the rows, columns and cells to export to MySQL. Thus, this is the best means for MS Excel to MySQL conversion.
Steps Involved in MS Excel to MySQL Conversion
- Establish Connection to MySQL Server
- Export the items to MySQL
Move MS Excel Table to MySQL Database
- Before migration, we need to connect to the SQL server to which data is to be exported. Check the steps given below
- Open the MS Excel Sheet which is to be exported
- Now go ‘Data Tab’ and then click on ‘MySQL for Excel’ icon. Users must install this in advanced
- When you click on MySQL for Excel’ icon a new sidebar window will open on excel sheet
- It comprises of MySQL Server
- Double click on it and then enter the password to connect with MySQL Database
Export MS Excel Records to MySQL Data
- Now Select the Data in MS Excel
- Click on the option ‘Export Excel Data to New table’
Set SQL Table Details
- A new screen will open here enter the table name
- Add a primary key column and select columns to export
- For selective column migration click on the header name
- Specify column name and ID
- Now click on Advanced Options and select data rows, formatted value types, VARCHAR length
- Click on Export to move the Excel tables to MySQL Table
This is the easiest method to transfer your Excel files to MySQL table for free. However, you must be cautious during file migration and specify each and every detail clearly. There is no need to copy each data as an entire excel table can be exported to MySQL in one go.