22

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!

Improve this question
3
  • 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, 2019 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, 2019 at 13:37
  • DBeaver along with DuckDB driver can run SQL queries directly on CSV files. Here is an example (This is for parquet but csv will be similar) - stackoverflow.com/a/74010417/6563567
    – ns15
    Oct 11, 2022 at 8:22

14 Answers 14

Reset to default
16

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

Improve this answer
4
  • Do you know if it is safe for commercial user?
    – Vish_er
    Apr 13, 2020 at 17:08
  • 1
    @Vish_er Licence is GPLv3 on github, you can also contact the author and ask
    – Benjamin Crouzier
    Apr 14, 2020 at 14:38
  • A Visual Studio Code extension for this would be cool.
    – Shawn Eary
    Jan 23 at 17:43
  • q works well for simple SQL queries. It currently doesn't support CTEs (WITH clause), doesn't support FROM subqueries, doesn't support IS DISTINCT FROM, etc. Also it doesn't seem to be actively maintained. duckdb is faster and doesn't have these limitations
    – Shai Coleman
    Sep 19 at 9:19
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.

Improve this answer
2
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.

Improve this answer
4

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.

Improve this answer
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.

Improve this answer
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.

Improve this answer
3

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

Improve this answer
2

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.

Improve this answer
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

Improve this answer
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!

Improve this answer
1
  • This worked wonderfully! Ty! Also, BigQuery through Google Cloud connects to Google Studio for visualization.
    – Vish_er
    Feb 12, 2021 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

Improve this answer
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).

Improve this answer
1

DuckDB can do this very efficiently.

Simple example: duckdb -c "SELECT MAX(Temperature) FROM 'temperature_log.csv'"

You can join multiple CSV (or TSV or other variants) sources as if they were tables in a relational database and operate on them with SQL as you would in a traditional RDBMS.

DuckDB is Free Software, distributed under the MIT license.

Improve this answer
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Sep 2 at 19:09
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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