Dropbase
Published in

Dropbase

4.5 Ways to Convert Excel Files to Databases Quickly

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.

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.

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:

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.

Importing your Excel file

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

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.

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.

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:

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store