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!
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.
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.
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.
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.
Once you've specified your SQL queries, copy and paste your SQL commands, and execute them
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.
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.
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.
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"
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.
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.
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.
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
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.
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.
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.
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
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.
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".
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.
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.
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!
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