8

I would like to read a CSV file from the shell as if it was an SQL Database table.

Is this possible without having to import the CSV file content to a SQL enviroment? Maybe there is some kind of linux based tool that can work it out...

I know it sounds like a tricky question, but I'm trying to avoid installing a SQL server and stuff. I have some limitations.

Any clue?

Improve this question
3
  • What exactly are you trying to do with the file? Do you want to query it? Do you want to get certain data out of it? Commented Feb 25, 2014 at 14:37
  • 1
    There is no classical "unix tool" for such task. But many languages (think of them as "shells" too) offer something into this direction. Look around for database clients that can use a csv file as a backend. This would allow you to access the file content by a small script instead of having to import the file into some sql server like sqlite. Commented Feb 25, 2014 at 14:39
  • There's also textql Commented Nov 27, 2019 at 1:52

2 Answers 2

Reset to default
7

There is also csvsql (part of csvkit)!

It can not only run sql on given csv (converting it into sqlite behind scenes), but also convert and insert into one of many supported sql databases!

Here you have example command (also in csvsql_CDs_join.sh):

csvsql --query 'SELECT CDTitle,Location,Artist FROM CDs JOIN Artists ON CDs.ArtistID=Artists.ArtistID JOIN Locations ON CDs.LocID = Locations.LocID' "$@"

showing how to join three tables (available in csv_inputs in csv_dbs_examples).

(formatting with csvlook also part of csvkit)

Inputs

$ csvlook csv_inputs/CDs.csv 

| CDTitle  | ArtistID | LocID |
| -------- | -------- | ----- |
| CDTitle1 | A1       | L1    |
| CDTitle2 | A1       | L2    |
| CDTitle3 | A2       | L1    |
| CDTitle4 | A2       | L2    |

$ csvlook csv_inputs/Artists.csv 

| ArtistID | Artist  |
| -------- | ------- |
| A1       | Artist1 |
| A2       | Artist2 |

$ csvlook csv_inputs/Locations.csv 

| LocID | Location  |
| ----- | --------- |
| L1    | Location1 |
| L2    | Location2 |

csvsql

$ csvsql --query 'SELECT CDTitle,Location,Artist FROM CDs JOIN Artists ON CDs.ArtistID=Artists.ArtistID JOIN Locations ON CDs.LocID = Locations.LocID' "$@" | csvlook

Produces:

| CDTitle  | Location  | Artist  |
| -------- | --------- | ------- |
| CDTitle1 | Location1 | Artist1 |
| CDTitle2 | Location2 | Artist1 |
| CDTitle3 | Location1 | Artist2 |
| CDTitle4 | Location2 | Artist2 |
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

5

Take a look at https://github.com/harelba/q, a Python tool for treating text as a database. By default it uses spaces to delimit fields, but the -d , parameter will allow it to process CSV files.

Alternatively you can import the CSV file into SQLite and then run SQL commands against it. This is scriptable, with a bit of effort.

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.