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!

  • 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

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 | |

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).


| improve this answer | |
  • Do you know if it is safe for commercial user? – Vish_er Apr 13 at 17:08
  • @Vish_er Licence is GPLv3 on github, you can also contact the author and ask – Benjamin Crouzier Apr 14 at 14:38

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 | |

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 | |

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]
              [-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 | |

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 http://www.raqsoft.com/html/file-processor.html

| improve this answer | |

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 | |

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.