Trick to Convert Excel Sheet into MySQL
I know this trick has been old-fashioned or something, but it may be helpful when you’re on the way & expecting more to do some tricky things. All you need is Microsoft Access, MySQL ODBC drivers, optional 3rd party application to accessing MySQL from your desk & an excel files to import.
First of all, open Microsoft Access, create a blank database & save it into anywhere on your disk.
Next, doing importing process from File :: Get External Data :: Import & select an excel file you want to import.
On Import Spreadsheet Wizard form, check the First Row Contains Column Headings check box to make the first row of excel sheet file as field name on MySQL.
Next, select In a New Table option if you wish to store the records in a fresh table.
If you are not sure whether the row has been duplicated or not, select Yes (Duplicates OK) combo box. This will inserting all of records on the table.
Again, select No Primary Key option to by passing the primary key on the table. You might need this to smoothly the processing action. Don’t worry, since you’ll allowed to make any changes of it later.
Next, type in the box with the name of the table. I’m using Test_Import for the name.
If nothing goes wrong, the selected excel files are importing first to MDB successfully.
Now, final step is exporting it into MySQL from the MDB file you just created. You no longer need the first excel file. But, notice that you have to create an ODBC data source link from MySQL ODBC driver first & create a database also in MySQL (to make it more easiest to create, I’m using MySQLFront). Both named by DBImport for my example. Next, exporting process started from File :: Export from Microsoft Access menus. It will show an Export Table ‘Test_Import’ To… wizard form. Select ODBC Databases() from Save as type drop down box.
The Export form will automatically filled with table name. Nothing to do from here, just press OK button to display next wizard form.
On Select Data Source form, select a data source ODBC linked to MySQL you had created once. After pressing OK button, be patient & let the application works for you.
Finished. Now you already have records exported from excel file.
First of all, open Microsoft Access, create a blank database & save it into anywhere on your disk.
Next, doing importing process from File :: Get External Data :: Import & select an excel file you want to import.
On Import Spreadsheet Wizard form, check the First Row Contains Column Headings check box to make the first row of excel sheet file as field name on MySQL.
Next, select In a New Table option if you wish to store the records in a fresh table.
If you are not sure whether the row has been duplicated or not, select Yes (Duplicates OK) combo box. This will inserting all of records on the table.
Again, select No Primary Key option to by passing the primary key on the table. You might need this to smoothly the processing action. Don’t worry, since you’ll allowed to make any changes of it later.
Next, type in the box with the name of the table. I’m using Test_Import for the name.
If nothing goes wrong, the selected excel files are importing first to MDB successfully.
Now, final step is exporting it into MySQL from the MDB file you just created. You no longer need the first excel file. But, notice that you have to create an ODBC data source link from MySQL ODBC driver first & create a database also in MySQL (to make it more easiest to create, I’m using MySQLFront). Both named by DBImport for my example. Next, exporting process started from File :: Export from Microsoft Access menus. It will show an Export Table ‘Test_Import’ To… wizard form. Select ODBC Databases() from Save as type drop down box.
The Export form will automatically filled with table name. Nothing to do from here, just press OK button to display next wizard form.
On Select Data Source form, select a data source ODBC linked to MySQL you had created once. After pressing OK button, be patient & let the application works for you.
Finished. Now you already have records exported from excel file.
Labels: My Tricks
| PS: If you've benefit from this blog, you can support it by making a small contribution. |
Post a Comment
Leave comments here...