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 cleaning and transformation steps, and load it to a free database provided to you by Dropbase. This process transforms your CSV into an instant database that you are given the credentials to start using immediately.

Create a New Project

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

Create a new dropbase project screen

Import your CSV file

Once the project has been created, you will go ahead and import the CSV that you want to turn into a database. Dropbase allows you to drag and drop the file into the editor, or import the data from an online source via a URL. You also have the option to specify the delimiter. Once you are ready to upload the file, press the Next button.

import a csv file to dropbase

Apply processing 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 and processing functions, you can change data types, remove outliers, remove null values, filter and sort data. You're also able to create custom cleaning steps using Python, if you want to take your data transformation to the next level.

✏️ Pro Tip: If you just want to upload the CSV as-is, you don't need to do any processing steps, just go straight from the import step to the export!

apply processing steps in dropbase

Export to Database

Once you're happy with the formatting of your data, the export step is simple. Choose your free personal Dropbase DB, create and name a new table to house your data in, and then click load to database!

export csv to instant database

Gather Database Credentials and start using your data!

With the data now live in the database, navigate to the "Your Databases" section of Dropbase to gather your database credentials. Using these credentials, you can now access your data live!

get your dropbase database credentials

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

January 12, 2022
Narayan Subramoniam
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