Import excel to mysql online

About XLS conversion to SQL queries

The Excel to SQL Converter was created for online transform Excel files into SQL(Structured Query Language) queries to insert in the database. This awesome tool supports custom table name and several MySQL commands to insert. No need to download or install any software.

As a result, you will receive several queries:

• SQL query to create a table. But this is just an example of "create table"; in fact, it is not recommended to execute this query, because it does not contain indexes, keys, valid types, etc.
• Insert or Replace queries.

How it Works?

Just select the options you need and upload your Excel(XLS or XLSX) file and you will instantly get SQL queries.

After conversion, you can execute these commands in your SQL server and add all data to your database.

Learn four (and a half) ways to quickly turn your Excel data into databases or SQL queries. Streamline your data uploading, transformation and loading with these handy tools.

Data in Excel can be analyzed and visualized within the software but you might want to export the data to a database or query the data using SQL. You could be getting files that by themselves don't exceed Excel's row limit but when combined can't fit in one file. Sure, you could use an Excel trick to open large files but what if you have to do this every week? Then you'd still need a data pipeline with as much automation as possible.

We're going to cover 5 ways that either export your Excel data to a database or to a file that can be imported easily by a database.

0.5: Convert your Excel file to a csv, then use csv tools

Most tools recommend that you convert your Excel file to a csv and then export to a database. This is because .csv is a lot more common in data science and is not locked down to Microsoft products. So if the Excel file you have allows for it to be saved as a csv, we recommend you do.

It's very easy to convert a single worksheet Excel file to csv. You click File > Save As and choose csv from the dropdown list of options. However, if you try and save a multiple worksheet file, you get this:

You could do what Excel says or write up a quick VBA script to save every worksheet in a file as a csv. Here's an article that outlines how to do that. Disclaimer: downloading and running VBA code from the internet might be hazardous. Do so at your own risk!

Once you have a csv file then you can follow this guide to convert it into a database or SQL. Why is this only 1/2 of an option? Because you can't save an Excel file as a csv all of the time! It may not fit in with the rest of your pipeline, perhaps you plan on adding to the same Excel file later on, or you don't want to have duplicate files with the same data.

In the above cases, use the other methods below.

1: Using SQLizer to create a .sql file

SQLizer is an online tool that lets you create a MySQL (or a PostgreSQL, SQL Server) script given an Excel file less than 5000 rows. The tool outputs a SQL file with a table definition (using CREATE ... syntax) and as many INSERT statements as there are rows in the Excel file. Here's how to use it:

1. Head to https://sqlizer.io/#/ , click on Select your file..., and navigate to the file you want to upload.

2. Choose MySQL from the database type, along with verifying all of the checkbox options.

3. Change the Database Table Name if you want and click on the Convert My File button and wait while it processes.

4. After it's done, you should see something like this:

5. You can then download the file and import it from within MySQL Workbench using File > Open SQL Script and selecting the created SQL script.

And that's it! SQLizer does have a 5000 row limit in its free version but offers paid plans with API access to integrate with data pipelines. SQLizer is recommended if you either don't need automation or if you're comfortable with working with APIs for automating your data pipeline.

2: Use Dropbase to create an Instant Database

Dropbase lets you import an Excel file, apply some data cleaning steps, and load it to a database extremely fast. You never have to clean the same data twice because Dropbase saves your processing steps as a pipeline to be used in future files of the same schema.

Creating a new workbook

Log in to your account at app.dropbase.io. To create your first new workspace, click on the workspace's dropdown menu in the top left and then select Create New workspace.

Then click on Create New Workbook in the new workspace. This will create a new workbook where you can import your Excel file.

Importing your Excel file

After clicking on your new workbook, you will see this screen:

Click on Import Data in the right sidebar which will open up an Import file window. Drop your Excel file there and click Upload File. In addition to flat files like Excel and csv, Dropbase also allows importing data sources from a URL or web app, as well as other Dropbase databases.

The Express Option: Export to database directly without any processing steps

Dropbase also has an express option of exporting a file to a database. Click on the Databases tab, and drop your Excel file in the Select Table column. This will launch a window that lets you verify your column names. If it's all looking good, give the table a name, and click on Create Table. You're done! Your Excel data is now in the database. The below video gives an overview of this express option.

‍

Apply processing steps and then export to database

You can now apply some pre-built processing steps to your file such as deleting a column, renaming one, or sorting. Dropbase also allows you to create a custom processing step using SQL. Select the processing step either from clicking on the column name or on the + button in the right sidebar. Click on Run to process the steps and view the changes immediately in your data.

Finally, give your database a name and click on Load to Database. You're done! If you now navigate to Workspace settings, you'll see the access credentials to connect the database with a BI tool of your choice.

Dropbase works best if you are going to be working routinely with Excel files and want to automate the cleaning steps. However, if all you want is to export an Excel file as is to an existing database, then check out the next option.

3: Export to SQL Server

If you have a SQL Server as your relational database management system, there are two main ways to export an Excel file to SQL Server. The first way assumes you can convert it to a text file, so we will avoid that. The second way lets you keep your Excel file as is. We'll be using SQL Server's Import and Export Wizard to help, but there are other ways if you're comfortable with some amount of coding.

1. Open up SQL Server Management Studio (SSMS) and connect to a Database Engine. Note: if you're just trying out SSMS, be sure to download a SQL Server edition and then use "localhost" under server name.

2. Right click on a Database and under Tasks, select "Import Data". Be sure not to click "Import Flat File" since this, for some reason excludes Excel files. This will open up the Import Wizard.

3. Click on "Next", and select "Microsoft Excel" from the dropdown menu of Data sources

4. Click on the "Next" button and if it works for you, congrats! It didn't work for me and I got this error message instead:

5. Which is a common error when you're switching between 32-bit and 64-bit versions of software. Not to worry! All you have to do is install an official Microsoft Access Database Engine, restart your machine, and you're done.

Your Excel file is now imported as a table in a database accessed through SSMS. The downside of this route is that it doesn't work if you use any other database management software. If you don't care about using a relational database management software in the first place, then you can just use Airtable. Consider using Dropbase or the SQLizer option if you do.

4: Use Airtable to visualize your data

Although Airtable doesn't give you an actual database, you can still export an Excel file to it. You can then analyze and visualize the data in Airtable. Here's how:

1. You can either create a blank Base and then upload your file or select the Microsoft Excel option from Quickly upload. Let's say you choose to Start from scratch.

2. Click on the Add or import button and select Microsoft Excel

3. Drop your file in and verify that all the columns are imported correctly.

4. Your Excel data is now in Airtable and ready to be analyzed.

Using Airtable has more limitations than other options in this list. First, there's a 5MB limit on Excel files for all levels. Other options in this list, like Dropbase, has a 50MB limit for Excel files (for now!). Secondly, there's no way in Airtable to append Excel data to an existing table. You could create two different tables and then create a merged view on top of that, but you'll never have a single source of truth for your data.

So Airtable is not recommended if you want to work with Excel files regularly or if they're larger than 5MB.

Closing thoughts

These are 5 different ways to convert your Excel file to a database. I hope it was useful! If you'd like to check out how Dropbase works with Excel files, sign up for a free trial here.

‍

How do I import an Excel spreadsheet into MySQL?

How do I convert Excel data to SQL query?

How do I connect Excel to online database?

How do I convert an Excel spreadsheet to a database table?