Data tips

5 Ways to Convert CSV Files to Databases Quickly

Learn five methods to quickly turn your CSV data into databases or SQL queries. Streamline your data uploading, transformation and loading with these handy tools.

Turning offline data files like CSV's into live data sources can be a daunting task, especially for users who have never dealt with databases or who don't have a lot of database management experience. For most of us, we just want a way to skip all the technical, code-intensive tasks, and start working with our data live in a database. This article will show you five different ways that you can quickly and easily turn your offline data into databases!

1. Quickly Turn CSV's in SQL Queries with ConvertCSV

If you've already set up your SQL database and are just hoping to quickly turn your CSV into in SQL queries, ConvertCSV allows you to quickly turn your CSV into a series of SQL queries, with minimal SQL knowledge needed. If you need to set up a database from scratch, I suggest you move to Part 2 of this article.

Choose the data source (CSV) that you wish to convert

ConvertCSV gives you options to paste the data in, upload a file locally, or input a URL where the CSV data is located. For this example I've used a CSV that contains Apple's recent stock prices.

CSV to SQL import step

Choose input options to ensure the SQL queries are properly created

This step allows you to indicate to the program if the first row is a list of column names, if there are a number of lines at the beginning you wish to skip, and how the file is delimited.

CSV to SQL input options step

Choose output options to format your data

ConvertCSV gives you a number of options to change the data types of columns, to sort your data, and how to treat any empty fields. It also allows you to name the table, and indicate whether you are creating a new table, or if you are inserting into an existing table. With all this optionality, you will be able to meet most of your basic data processing needs.

CSV to SQL output step

Copy the SQL queries, and run them in your database

Once you've specified your SQL queries, copy and paste your SQL commands, and execute them

CSV to SQL output copy and paste step

2. Build Instant Databases with Dropbase

If you haven't set up a database yet, and are looking to turn the CSV file directly into a database, your best option is to use Dropbase. Dropbase allows you to quickly upload CSV file, apply data validation and cleaning steps, and load it to a database provided to you by Dropbase, no coding or scripts required. This process transforms your CSV into an instant database that you are given the credentials to start using immediately and connect with any business intelligence, data visualization, or analytical reporting tool.

Create a New Workspace

After creating a Dropbase account, your first step is to create a new Workspace, which will allow you to start turning your CSV's into instant databases.

Creating a new workspace with Dropbase

Import your CSV file

Once the Workspace has been created, you will go ahead and import the CSV that you want to turn into a database. Dropbase allows you to simply drag and drop the file into the database manager, or create a table with the schema builder. Dropbase automatically detects the file delimiter, but you have the option to specify one. Once you are ready to upload the file, press the Next button.

Drag and drop CSV file in Dropbase
Drag and drop a CSV file to create a database table

Import sales CSV in Dropbase
Set file import options

Apply cleaning steps

Dropbase makes it incredibly easy for you to clean and filter the data you want to upload. Using a number of prebuilt data cleaning functions, you can change data types, remove outliers, remove null values, filter and sort data, find and replace values, etc.

✏️ Pro Tip: If you just want to upload the CSV as-is, you don't need to do any cleaning steps, just go straight from the import step to the load to database step! Just click on "Load to database"

Dropbase cleaning steps UI
Apply cleaning steps to the data

Load to Database, instantly

Once you're happy with the formatting of your data, the load to database step is simple. Simply run any outstanding cleaning step and then then click load to database! Once the data is loaded, Dropbase will automatically show you the database table created with a preview of the data you just loaded.

Load to database using Dropbase
Load data to the database

Gather Database Credentials and start using your data!

This data is now live in a cloud database that you can access from any tool. Simply click on the "Integrate" menu to gather your database credentials. Using these credentials, you can connect your data to any of your favorite data tools.

Database integrations Dropbase
Gather database credentials to connect Dropbase to your favorite data tools

3. Using Command Line Arguments to Convert CSV to Database

If you're a more technical user, you can also explore using csvkit, a command line tool that allows you to manipulate CSV's. The library of commands is quite extensive, and the functionality that it would allow you to perform is quite extensive.

For the purposes of this article, I won't get too into depth about the process of setting up the tool and getting your environment set up. Instead, I will highlight two functions that might be useful for you to explore, and leave the documentation here for you to read more.

Import CSV into PostgreSQL

csvsql --db postgresql:///database --insert data.csv

Running this command will allow you to insert your CSV file into an already existing postgres database. To view more examples for specific usages, read the documentation on the csvsql command

Query CSV using SQL

csvsql --query "select date from data where price > 30" data.csv > new.cs

If your goal is not necessarily to create a database, but rather to run SQL queries on your CSV data, this function will allow you to return the results of an SQL query done against your CSV file, in the form of another CSV file.

4. Using Airtable to create Online Tables From CSV

Although Airtable doesn't create a database in the traditional sense, it can offer many of the same benefits. If you require having your own database this may not be the best option for you, but for users who just want to upload a CSV file and then be able to have that file in a shared environment that other members of your team can access, Airtable can be a great solution.

Create a new base, by importing from a spreadsheet

After creating your Airtable account, to create a database from a CSV, begin by adding a new base, and choosing to import from a spreadsheet.

Create a new airtable base

Choose the CSV file you wish to import

In a similar manner to Dropbase, simply drag and drop the CSV file into the drop-zone, to import the file.

Unfortunately, one of the limitations of Airtable is that they only allow uploads of 5MB CSV files, whereas other solutions on this list such as Dropbase allow for uploads up to 500MB in size.

CSV drag and drop area for airtable

Enjoy using your data in Airtable

Once you've completed the upload, your data is now live in a Base on Airtable for you to edit and share with your team!

The only other limitation is that if you want to upload and append more CSV data to the same Airtable base, you need to sign up for their Pro or Enterprise plan in order to access the CSV import app. So Airtable is not recommended if you are hoping to upload and append CSV files to your table on a regular basis

5. Use DBeaver to manage your database and convert your CSV

If you haven't used it before, DBeaver is an open source database management tool that supports users from a wide variety of backgrounds, from developers to analysts. If you're looking for a tool to manage your database, as well as to perform CSV to database imports, DBeaver is an excellent choice. You will need to learn how to set up a database, if you don't already know how to do so. I'd suggest using the documentation provided by DBeaver to get started with setting up your database. I'll go through the steps on how to import data from a CSV into your table in this article.

Choose the table you want to import to

For DBeaver, you can import CSV's to tables that are already set up. So create a table that matches the columns that your CSV contains, and then right click the table, and choose "Import Data".

Dbeaver import step

Specify the import format as CSV, and change any necessary settings

First choose the file type that you will be importing, in this case a CSV, and then go ahead and choose the CSV source file that you will be using.

dbeavver import wizard - import from CSV
import appl csv and see import conditions

Map the data from the CSV to your table in the database

Choose the columns in the CSV that map to the database columns, and choose if there are any columns that you want to ignore, or set a constant value for.

map csv data to database columns

Set any additional import options, and review your import

You will then be given the option to change any additional settings, and review your data once more before finishing the import process. Once you've clicked "Start", DBeaver will work it's magic, and your data will be imported to the table!

additional data import settings in dbeaver
data transfer steps dbeaver

And there you have it! Five different ways to solve the issue of turning your offline files like CSV's into database tables. I hope that these solutions we helpful in helping you turn offline data into online files.

Get started converting CSV, Excel, and JSON files to instant live databases with Dropbase

Sign up for free today, or contact us to chat about solving your data onboarding needs

Newsletter
Insights and updates from the Dropbase team.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
By signing up you agree to our Terms of Service