11

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

10 Answers 10

7

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.

1
5

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 – Benjamin Crouzier Apr 14 '20 at 14:38
2

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.

2

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.

2

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

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.

1

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

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

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.

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.