Giter VIP home page Giter VIP logo

textql's Introduction

TextQL

Build Status Go Report Card

Allows you to easily execute SQL against structured text like CSV or TSV.

Example session:

textql_usage_session

Major changes!

In the time since the initial release of textql, I've made some improvements as well as made the project much more modular. There've also been additional performance tweaks and added functionality, but this comes at the cost of breaking the original command-line flags and changing the install command.

Changes since v1

Additions:

  • Numeric values are automatically recognized in more cases.
  • Date / Time / DateTime values are automatically recognized in reasonable formats. See Time Strings for a list for accepted formats, and how to convert from other formats.
  • Added join support! Multiple files / directories can be loaded by listing them at the end of the command.
  • Directories are read by reading each file inside, and this is non-recursive.
  • You can list as many files / directories as you like.
  • Added flag '-output-file' to save output directly to a file.
  • Added flag '-output-dlm' to modify the output delimiter.
  • Added "short SQL" syntax.
    • For the case of a single table, the FROM [table] can be dropped from the query.
    • For simple selects, the SELECT keyword can be dropped from the query.
    • This means the v1 command textql -sql "select * from tbl" -source some_file.csv can be shortened to textql -sql "*" some_file.csv

Changes:

  • The flag '-outputHeader' was renamed to '-output-header'.

Removals:

  • Dropped the ability to override table names. This makes less sense after the automatic tablename generation based on filename, joins, and shorter SQL syntax changes.
  • Removed '-source', any files / paths at the end of the command are used, as well as piped-in data.

Bug fixes:

  • Writing to a directory no longer fails silently.

Key differences between textql and sqlite importing

  • sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.
  • textql supports quote-escaped delimiters, sqlite does not.
  • textql leverages the sqlite in-memory database feature as much as possible and only touches disk if asked.

Is it any good?

Yes

Requirements

  • Go 1.4 or later

Install

Latest release on Homebrew (OS X)

brew install textql

Build from source

go get -u github.com/dinedal/textql/...

Docker

First build the image.

docker build -t textql .

Now use that image mounting your current directory into the container.

docker run --rm -it -v $(pwd):/tmp textql [rest_of_command]

Alias

You can add the following alias to your system to provide quick access to TextQL:

alias textql='docker run --rm -it -v $(pwd):/tmp textql '

AUR

Using an AUR-Helper

yaourt textql-git

Building from PKGBUILD First clone the repository.

makepkg -cs

Then install the package using pacman or your favorite Package Manager

Usage

  textql [-console] [-save-to path path] [-output-file path] [-output-dlm delimter] [-output-header] [-pretty] [-quiet] [-header] [-dlm delimter] [-sql sql_statements] [path ...]

  -console
        After all statements are run, open SQLite3 REPL with this data
  -dlm string
        Input delimiter character between fields -dlm=tab for tab, -dlm=0x## to specify a character code in hex (default ",")
  -header
        Treat input files as having the first row as a header row
  -output-dlm string
        Output delimiter character between fields -output-dlm=tab for tab, -dlm=0x## to specify a character code in hex (default ",")
  -output-file file
        Filename to write output to, if empty no output is written (default "stdout")
  -output-header
        Display column names in output
  -quiet
        Suppress logging
  -pretty
        Pretty print output
  -save-to file
        SQLite3 db is left on disk at this file
  -sql string
        SQL Statement(s) to run on the data
  -version
        Print version and exit

I want stdev, average, other functions

Just follow the install directions at go-sqlite3-extension-functions and textql will automatically load this library.

Full function list:

  • Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi.
  • String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter.
  • Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile

License

New MIT License - Copyright (c) 2015, 2016 Paul Bergeron http://pauldbergeron.com/

See LICENSE for details

textql's People

Contributors

abhijeetiyengar avatar adamkorcz avatar aniket-pradhan avatar badbart avatar chayward avatar chocolateboy avatar danstreeter avatar dartb avatar ddeka2910 avatar dinedal avatar haraldnordgren avatar jimmyfrasche avatar johnlunney avatar lazywei avatar mattn avatar mause avatar mem avatar naoyafurudono avatar stoned avatar szaydel avatar tai avatar vovcacik avatar yuanyangwu avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

textql's Issues

detect format of tabular files

Would be more ergonomic not to have to specify -dlm. And -output-dlm default to the same as -dlm would make a lot of sense, too.

Cannot build on Ubuntu 14.04

➜ go get -u github.com/dinedal/textql/...
# github.com/mattn/go-sqlite3
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte

Column name with "index" throws a syntax error

Hi, this is a nice utility you've built, thanks.

I just wanted to let you know that header columns with name like "index" throws:

2014/12/29 12:00:22 near "index": syntax error

I renamed it from "index" to "fooindex" and it worked!

Apache Drill vs textQL

Hi
Seems that all or most of the capabilities of your tool being already covered by the Apache Drill + in addition Apache Drill can do a join between csv table to oracle or any other data source that you will define.

Regards,
Dor

"too many errors" build on Mac OS 10.6.8

Sounds great! But it doesn't build on Mac OS 10.6.8.

Macintosh-4:~ emv$ export CC=clang
Macintosh-4:~ emv$ go get -u github.com/dinedal/textql
# github.com/dinedal/textql
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol close$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol access
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol getcwd
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol stat$INODE64
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fstat$INODE64
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol ftruncate
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fcntl$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol read$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol pread$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol write$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol pwrite$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fchmod$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol unlink
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol mkdir
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol rmdir
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol mmap$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol munmap$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for close$UNIX2003 (type 32 rtype 76)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for access (type 32 rtype 76)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for getcwd (type 32 rtype 76)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for stat$INODE64 (type 32 rtype 76)
too many errors

Can't specify first column name of UTF-8 with BOM files

When opening files encoded in UTF-8 with BOM, specifying first column name causes a no such column error.

$ file nobom.csv bom.csv
nobom.csv: ASCII text
bom.csv:   UTF-8 Unicode (with BOM) text

$ cat nobom.csv
col1,col2
1,2
3,4

$ cat bom.csv
col1,col2
1,2
3,4

$ textql -header -sql 'select col1' nobom.csv
1
3

$ textql -header -sql 'select col1' bom.csv
2017/11/04 13:14:57 no such column: col1

Document `stdin` table name for piped input

Not a huge problem, but I had to spend a little more effort than ideal figuring out to run an update statement against the stdin table for a piped CSV. It would be ideal if this was documented.

Easy enough to answer with a query:
blah blah blah | textql -sql "SELECT name FROM sqlite_master WHERE type='table'"

But, of course, I tried the short syntax, pipe, checking the readme, and searching the code thinking it might just be a random string before I thought about just querying.

Usage

This looks mighty useful.
I have a project with lots of CSV incoming that I need to convert to golang struts.

Are you thinking of making some examples ?
Or maybe you know of other github projects that used this ?
Oh and can it run as a lib ?

Filename as source table

From https://news.ycombinator.com/item?id=7177417 user kyberias

I suggest another approach:
textql -query "select count(*) from persons.csv"
...because csv files are basically tables. This would also allow you to do joins on multiple files:
textql -query "select p.name, c.name from persons.csv p inner join companies.csv c on p.companyid = c.id"

Will need to drop file-ext to implement

Syntax error if header contains -

If you try to open a file with -header and one of the headers contains a "-" you get a syntax error and can not start a console or execute sql

seems interesting, but ...

it's very interesting doing sql like query on simple csv/tsv files but it would be more interesting if it can be done WITHOUT having a sql database behind there.

what i would like is to get rid of having dependencies towards any sql database but still accepting sql like queries . so the idea is to serve queries by interpreting sql on the fly...
for example, having an embedded sql interpreter engine for sql like queries, xquery interpreter engine for xquery like queries ... etc.

How to increase precision of decimal fields?

If I have this file as input:

amount
10.01
20

then doing a sum gives a low-precision output:

$ textql -header=true -sql="select sum(amount) from tbl" < input.csv
30.009999999999998

Is there a way to increase the precision, in order to get 30.01 here? (like with say BigDecimal in Ruby)?

UTF-8 Byte Order Mark with leading double-quote results in parsing error

As an FYI, it doesn't seem to like the Byte Order Mark with a leading double quote:

$ od -c sample.csv
0000000 357 273 277   "   i   d   "   ,   n   a   m   e   ,   v   a   l
0000020   u   e  \n   1   ,   "   A   n   d   r 303 251   ,   R 303 242
0000040   "   ,   5  \n   2   , 303 216   P 303 250   ,   1   6  \n
0000057
$ textql -source sample.csv -sql 'select * from tbl'
2014/10/27 14:52:38 line 1, column 1: bare " in non-quoted-field
$ od -c sample.csv
0000000 357 273 277   i   d   ,   n   a   m   e   ,   v   a   l   u   e
0000020  \n   1   ,   "   A   n   d   r 303 251   ,   R 303 242   "   ,
0000040   5  \n   2   , 303 216   P 303 250   ,   1   6  \n
0000055
$ textql -source sample.csv -sql 'select * from tbl'
id,name,value
1,"André,Râ",5
2,ÎPè,16

I'm using Windows 7, cygwin, go 1.3.3.

Support SQL UPDATE queries

This tool is incredibly useful to me as I have to deal with very large csv files which Excel really struggles with. One use case I have sometimes is that I want to map some values in the file. Right now I would have to write something like:

SELECT col_a, col_b, col_c, CASE WHEN col_d = 'foo' THEN 'bar' WHEN ... END col_d, col_e, col_f, col... FROM mycsv

It would be so useful if I could just write it as an update query:

UPDATE mycsv SET col_d = CASE WHEN col_d = 'foo' .... END

The output should just be dumping the updated table to stdout or the specified output file.

Syntax error when there is a "." in header

Hi,

I'm not sure is this bug related to textql or csv?

➜ cat reproduce.csv
"hello.a","bla"
1.2,3.4
➜ textql -source reproduce.csv -header -sql "select * from tbl"
2014/02/11 18:16:48 near ".": syntax error

Index support

This would increase query speed significantly, investigate if / how this might work

Factor out into a library.

I definitely have a use for this in my own project. It would be nice if textql was a library I could import, and then you would have a textql/textql directory that contained main.go that would import the library and provide a CLI interface to it.

Support REGEXP

It would be great to support the REGEXP operator:

WHERE x REGEXP <regex>

Looks like sqlite doesn't have it by default and needs an application-defined function but it will be incredibly useful to have this feature!

no such function: stddev

Can you help me understand why the following code does not work?

textql -header -output-header -sql "avg(Pct) as avg_pct" ALL_RNA_QC.csv
textql -header -output-header -sql "stddev(Pct) as stddev_pct" ALL_RNA_QC.csv

Here's the output

avg_pct
85.15
2016/02/02 12:02:28 no such function: stddev

Syntax error when there is a ":" in header

Probably linked to #21

$ echo 'foo,bar,foobar\n2,3,4' |textql -header -sql "select * from tbl"
2, 3, 4
$ echo 'foo,bar,foo:bar\n2,3,4' |textql -header -sql "select * from tbl"
2014/02/11 16:11:30 near ":bar": syntax error

Thx

query csv files without header

How do you query a csv file without header? Ideally, something like this would be nice:

textql -sql "$1, $2 where $2 > 100" data.csv

hyphen in filename causes syntax error

textql -dlm=tab -header -sql "select hostname,ip order by hostname" my-nodes.txt
2017/05/30 00:35:23 near "-": syntax error
mv my-nodes.txt my_nodes.txt
textql -dlm=tab -header -sql "select hostname,ip order by hostname" my_nodes.txt
[[ expected output ]]

Put main in separate package

This with the goal of invoking textql programatically.

Since this will be part of v2, I'm just creating this so that it is in the record. Please feel free to close this issue and the associated PR.

Startup

How do I start textql on my ubuntu

What am I doing wrong?

 22:38:45 fluxcapacitor:/projects$ export CC=clang
 22:39:20 fluxcapacitor:
/projects$ go get -u github.com/dinedal/textql

github.com/dinedal/textql

github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol close
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol access
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol getcwd
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol stat$INODE64
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fstat$INODE64
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol ftruncate
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fcntl
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol read
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol pread
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol write
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol pwrite
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fchmod
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol unlink
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol mkdir
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol rmdir
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol mmap
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol munmap
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for close (type 32 rtype 120)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for access (type 32 rtype 120)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for getcwd (type 32 rtype 120)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for stat$INODE64 (type 32 rtype 120)
too many errors

SIGILL: illegal instruction while trying to install.

When I try to install using:
go get -u github.com/dinedal/textql

I get:

SIGILL: illegal instruction
PC=0x81a6489

math.init·1()
/usr/local/go/src/pkg/math/pow10.go:34 +0x19 fp=0xb74e3f34
math.init()
/usr/local/go/src/pkg/math/unsafe.go:21 +0x3e fp=0xb74e3f38
strconv.init()
/usr/local/go/src/pkg/strconv/quote.go:442 +0x44 fp=0xb74e3f4c
go/parser.init()
/usr/local/go/src/pkg/go/parser/parser.go:2441 +0x49 fp=0xb74e3f58
main.init()
/usr/local/go/src/cmd/go/zdefaultcc.go:6 +0x44 fp=0xb74e3f98
runtime.main()
/usr/local/go/src/pkg/runtime/proc.c:213 +0xd6 fp=0xb74e3fcc
runtime.goexit()
/usr/local/go/src/pkg/runtime/proc.c:1394 fp=0xb74e3fd0

eax 0x0
ebx 0x1891b080
ecx 0x189010a0
edx 0x1891b080
edi 0x1891b080
esi 0x20
ebp 0x0
esp 0xb74e3f30
eip 0x81a6489
eflags 0x10206
cs 0x73
fs 0x0
gs 0x33

Unable to install

I'm getting the following errors when installing on OSX

~/go/src/github.com/omnipresent $ echo $GOPATH
/Users/omni/go
~/go/src/github.com/omnipresent $ echo $GOROOT
/usr/local/go
~/go/src/github.com/omnipresent $ go version
go version go1.2 darwin/386
~/go/src/github.com/omnipresent $ export CC=clang
~/go/src/github.com/omnipresent $ go get -u github.com/dinedal/textql
# github.com/dinedal/textql
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol close$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol access
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol getcwd
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol stat$INODE64
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fstat$INODE64
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol ftruncate
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fcntl$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol read$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol pread$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol write$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol pwrite$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol fchmod$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol unlink
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol mkdir
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol rmdir
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol mmap$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unexpected reloc for dynamic symbol munmap$UNIX2003
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for close$UNIX2003 (type 32 rtype 76)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for access (type 32 rtype 76)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for getcwd (type 32 rtype 76)
github.com/mattn/go-sqlite3(__DATA/__data): unhandled relocation for stat$INODE64 (type 32 rtype 76)
too many errors

I can't seem to install it?

help
i use :

GO : go1.2.2.darwin-amd64-osx10.8.tar.gz
OS : Darwin MAC-ISMAIL.local 13.2.0 Darwin Kernel Version 13.2.0: Thu Apr 17 23:03:13 PDT 2014; root:xnu-2422.100.13~1/RELEASE_X86_64 x86_64

i.sebbane in ~
☢ go get -u github.com/dinedal/textql
package github.com/dinedal/textql
imports bytes: unrecognized import path "bytes"
package github.com/dinedal/textql
imports database/sql: unrecognized import path "database/sql"
package github.com/dinedal/textql
imports database/sql/driver: unrecognized import path "database/sql/driver"
package github.com/dinedal/textql
imports encoding/csv: unrecognized import path "encoding/csv"
package github.com/dinedal/textql
imports encoding/hex: unrecognized import path "encoding/hex"
package github.com/dinedal/textql
imports errors: unrecognized import path "errors"
package github.com/dinedal/textql
imports flag: unrecognized import path "flag"
package github.com/dinedal/textql
imports fmt: unrecognized import path "fmt"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports io: unrecognized import path "io"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports runtime: unrecognized import path "runtime"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports runtime/cgo: unrecognized import path "runtime/cgo"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports strings: unrecognized import path "strings"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports syscall: unrecognized import path "syscall"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports time: unrecognized import path "time"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports unsafe: unrecognized import path "unsafe"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports io/ioutil: unrecognized import path "io/ioutil"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports log: unrecognized import path "log"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports os: unrecognized import path "os"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports os/exec: unrecognized import path "os/exec"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports os/user: unrecognized import path "os/user"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports path/filepath: unrecognized import path "path/filepath"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports regexp: unrecognized import path "regexp"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports strconv: unrecognized import path "strconv"
package github.com/dinedal/textql
imports github.com/mattn/go-sqlite3
imports unicode/utf8: unrecognized import path "unicode/utf8"

github pages

Hello,

I see you have a git branch for github's pages but I don't know the URL to your github page.

Could you please share it and I would also recommend linking to it from your github page.

Thanks!

man page is missing from "brew install textql"

Edwards-MacBook-Air:~ emv$ brew install textql
==> Downloading https://homebrew.bintray.com/bottles/textql-2.0.2.mavericks.bottl
######################################################################## 100.0%
==> Pouring textql-2.0.2.mavericks.bottle.tar.gz
🍺  /usr/local/Cellar/textql/2.0.2: 5 files, 10M
Edwards-MacBook-Air:~ emv$ man textql
No manual entry for textql

A quick search through the available doc found the usage string but no man page.

License

I know it is minor but can you add under which license you develop this? Thanks.

Joins over two files

First of all, textql is awesome!

I haven't been able to figure out how to do a JOIN in a single command (if that's even possible), as a concrete example I have two TSV files, one for child mortality by country and another for GDP by country:

→ ls
mort.tsv  gdp.tsv

I can do queries over each file, sorting, selecting, limiting, etc.

→ textql -dlm=tab -header=false -source=gdp.tsv -sql='select c1, c0 from tbl order by cast(replace(c1,",","") as integer) desc LIMIT 5'
16,244,600,000,000, United States
8,227,102,629,831, China
5,959,718,262,199, Japan
3,428,130,624,839, Germany
2,612,878,387,760, France

What I would like to do is an inner join on the two files on the country column.

From looking at the source code, I see that there is one table_name variable, does this mean that only one table is supported?

Infer `NUMERIC` type

It'd be nice to have an option that instructs textql to scan the first row and treat any unquoted number character as a NUMERIC data type. For the example in the demo:

id,name,value
1,Paul,5
2,Jeff,16
3,Dimitri,-3

Invoking with a -infer-numeric option would generate the following create statement:

CREATE TABLE IF NOT EXISTS tbl (
   id NUMERIC,
   name TEXT,
   value NUMERIC
)

This would allow executing functions such as SUM without having to cast columns.

build from source failed

Hi,

I tried to build textql on Ubuntu 14.04 64bit:

$go get -u github.com/dinedal/textql/...

github.com/mattn/go-sqlite3

Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte
Go type not supported in export: [0]byte

Not sure why it failed. Can anyone help?

Thanks,
Gerald

tab delimiter not working

I tried to query a tab separated value using Textql, but unfortunately got an error.

[kveeramuthu@trinityqa1-235347 bin]$ cat ~/sample_data.tsv
id name value
1 kousi 10
2 umesh 20
3 don 30

[kveeramuthu@trinityqa1-235347 bin]$ ./textql -source ~/sample_data.tsv -header -dlm=tab -sql "select sum(value) from tbl" -verbose=true
Column 696420206e616d652020202076616c7565 renamed to id__name____value
Data loaded in: 174.98us
2014/06/09 18:48:38 no such column: value

[kveeramuthu@trinityqa1-235347 bin]$ ./textql -source ~/sample_data.tsv -header -dlm=tab -sql "select * from tbl" -verbose=true
Column 696420206e616d652020202076616c7565 renamed to id__name____value
Data loaded in: 178.048us
1 kousi 10
2 umesh 20
3 don 30
Queries run in: 143.194us

I was able to successfully run a query on the ';' separated values. Just TSV is not working.

[kveeramuthu@trinityqa1-235347 bin]$ cat ~/sample_data.txt
id;name;value
1;kousi;10
2;umesh;20
3;don;30
[kveeramuthu@trinityqa1-235347 bin]$

[kveeramuthu@trinityqa1-235347 bin]$ ./textql -source ~/sample_data.txt -header -dlm=';' -sql "select * from tbl" -verbose=true
Data loaded in: 196.183us
1, kousi, 10
2, umesh, 20
3, don, 30
Queries run in: 186.977us

[kveeramuthu@trinityqa1-235347 bin]$ ./textql -source ~/sample_data.txt -header -dlm=';' -sql "select max(id), max(name) from tbl" -verbose=true
Data loaded in: 176.664us
3, umesh
Queries run in: 140.822us

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.