16

I have been trying to find tools which allows me to import a CSV file and perform SQL operations on it. I tried to import on SQL Dev 4 but it does not work without a local connection. The data is small, so I do not need heavy enterprise level software - just something to get the job done. Any recommendation would be highly appreciated!

2
  • There is a standard command line utility called join; here are the specifications. It has a couple of major caveats: 1. It can only deal with a single delimiter, so double-quoted fields in CSVs are out (and double-quoted fields containing commas or newlines are emphatically out); 2. The files must be lexicographically sorted on the join column (which means numerically sorted fields will throw errors). Despite these drawbacks, it's worth knowing as it's POSIX specified and pretty much everywhere.
    – Wildcard
    May 22 '19 at 2:09
  • Microsoft's Log Parser is quite powerful, but has unfortunately not been updated for about 15 years. :) Wish they would open source or an alternative discovered.
    – Henrik
    May 22 '19 at 13:37

13 Answers 13

9

The 'problem' with a CSV file is that the single 'table' doesn't have a name. Therefore, you'll need to define a table (including columns) and import the CSV file before doing any SQL queries on it.

Almost all database browsers offer import from CSV; if you want a lightweight one, I can recommend SQLite Browser. It's open-source, cross platform (Windows, macOS, and many Unix/Linux distros) and explicitly mentions

  • Import and export tables from/to CSV files

as one of its features.

2
8

Have a look at q

q is a command line tool that allows direct execution of SQL-like queries on CSVs/TSVs (and any other tabular text files).

https://github.com/harelba/q

2
  • Do you know if it is safe for commercial user?
    – Vish_er
    Apr 13 '20 at 17:08
  • @Vish_er Licence is GPLv3 on github, you can also contact the author and ask Apr 14 '20 at 14:38
4

If you have the flexibility to use some light scripting languages try -

  1. R script - It has sqldf library which can be used to run sql queries on dataframes. You just have to read your csv into a dataframe.

  2. Python - pandasql library has the same functionality over pandas dataframes.

4

Using PostgreSQL and DBeaver is another option.

PostgreSQL is a sophisticated, open source database system. It allows for importing CSV files through the COPY command and through graphical UIs such as DBeaver.

DBeaver is a free and multi-platform database management tool.

In DBeaver:

  • Create the database table for the CSV files. Make sure the table column names and data types match the CSV file columns.
  • Right-click on the table and select "Import table data".
  • Select CSV and the file to import.
  • Match the columns which are not an exact match if necessary.
  • Click Import.

See this guide for more detail.

3

SQLite is a nice lightweight tool that is supported by multiple languages. If you use Python, there are several tools in the Python Package Index (pypi.org) that make it easy to use (including https://pypi.org/project/querycsv/, which I wrote). If you use R, you can use read.csv to import the data to a data frame, and use the sqldf package to query it using SQL.

3

Try csvsql. It is part of csvsuite, a suite of command line tools written in python, since March 2019 tested against Python 3.7.

The csvkit tools are described in the book Data Science at the command line. If you pass standard input to csvsql, then the table is named stdin.

csvsql --help

Generate SQL statements for one or more CSV files, or execute those statements
directly on a database, and execute one or more SQL queries.

usage: csvsql [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b]
              [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-L LOCALE]
              [-S] [--blanks] [--date-format DATE_FORMAT]
              [--datetime-format DATETIME_FORMAT] [-H] [-K SKIP_LINES] [-v]
              [-l] [--zero] [-V]
              [-i {firebird,mssql,mysql,oracle,postgresql,sqlite,sybase}]
              [--db CONNECTION_STRING] [--query QUERY] [--insert]
              [--prefix PREFIX] [--tables TABLE_NAMES] [--no-constraints]
              [--no-create] [--create-if-not-exists] [--overwrite]
              [--db-schema DB_SCHEMA] [-y SNIFF_LIMIT] [-I]
              [FILE [FILE ...]]

There are other similar command line tools as well, but I don't remember the name of them.

2

Try RBQL - it provides SQL-like dialect with Python or Javascript expressions. You can use RBQL from the command line (RBQL in pip) And from Rainbow CSV text editor extensions which provide nice GUI and are available for VSCode, Atom, Sublime Text, and Vim. And you can also try RBQL online without installing anything - rbql.org

2

I know I'm late to the party, but I've created a Desktop app that can perform SQL on a bunch of CSV files.

It is a Desktop app because it can easily handle a 600MB CSV file (only taking ~15 seconds to load), for example.

Also, the CSV data never leaves your machine, which is important if your data is important.

Here it is: https://superintendent.app

1

Google/GCP BigQuery allows you to query CSV files that have not been imported (https://cloud.google.com/bigquery/external-data-sources). There would be a small cost for storage and costs per query, however.

If you end up having to import, then the SQLite answer may be better, of course!

1
  • This worked wonderfully! Ty! Also, BigQuery through Google Cloud connects to Google Studio for visualization.
    – Vish_er
    Feb 12 at 16:08
1

It would have been much simpler if you had used esProc SPL. It directly provides the ability of SQL query and calculation for files (CSV, TXT, Excel, etc.). For example:

$select * from scores.txt where class=10 //Filter
$select class,avg(english) as avg_en from scores.txt   group by class //Group and aggregate
$select sum(S.quantity*P.Price) as total from sales.txt as S join products.txt as P on S.productid=P.ID where S.quantity<=10 //join,filter,aggregate

esProc is also a professional tool for file data source processing. It can easily perform mixed calculations, importing into and exporting out of a database of various file data, such as TXT, Excel, XML, JSON, CSV, etc. The syntax is simple, in line with people’s natural thinking, and simpler than other advanced development languages. Please refer to https://esprocforbp.medium.com/directly-query-excel-text-files-using-sql-5315788231e4

1

Have a look at TextQ (disclaimer - I'm its developer). It can import a big CSV file and allows you to manage its schema/structure - e.g. rename or hide columns, parse dates and numbers, and index columns;

TextQ supports SQL queries using SQLite syntax. You can select, join, group by, etc.

For less technical users, TextQ offers UI Query Builder, which can export to SQL with a single click.

Any query result can be export to a new CSV file.

You can get it from the Mac App Store or Microsoft Store (coming soon).

0

Flatbase is a free web app that allows you to query CSV, JSON, and other flat files using SQL. You can also graph results and share with colleagues.

0

You might consider Querona (https://www.querona.io/. It is a data virtualization tool that has a built-in driver that allows for connecting to files like CSV, TXT, MSG stored in local/remote folders or HTTP(s) links. It automatically detects the schemas of the files and presents them to you as tables in a SQL Server virtual database (Querona emulates SQL Server so no SQL Server license is needed). You can use any SQL Server client to access data and issue T-SQL queries against all of the files (as tables). You can read more here: https://docs.querona.io/data-sources/data-services-files/csv-tsv-pdf-msg-txt.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.