4

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 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 at 13:37
5

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

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.

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.