Importing tab-delimited text files or connecting to ODBC

Importing tab-delimited text files or connecting to ODBC

[Maks Romih]

Hi all!

I have a question about lisp in Emacs for Windows

What would be the best way to import some tables, like a tab delimited
copy-paste chunk from excel. I would like to get the table into Emacs
Lisp so that a table would be a big list of rows, where a row would be
a list of atoms, either raw strings or, when possible, converted to
Lisp numbers and symbols.

I'm considering programming some low level function like

    >(defun read-tab-delimited (fn)
    >  (find-file fn)
    >  (let (row rows)
    >    (while (re-search-forward "\\([^	
    >]*\\)\\([	
    >]\\)" nil t)
    >      (let ((token (match-string 1)))
    >	(push token row)
    >	(when (equal (match-string 2) "
    >")
    >	  (push (nreverse row) rows)
    >	  (setq row nil))))
    >    (kill-buffer nil)
    >    (nreverse rows)))
    
but I don't like it. I would still have a lot to work with converting
strings into numbers, converting pairs of double quotes into single
quotes, etc.

It would be much better if I could somehow call ODBC from emacs.  Then
I would simply connect to some Microsoft Access MDB or any other
relational database, and pump the data from there.

If the ODBC is impossible to call from emacs, I would maybe use CLISP
and CLSQL to connect to ODBC, convert the data and then load data into
emacs with the Elisp function read. I'm not sure if CLSQL and CLISP
for Cygwin works, this I'm going to ask on the comp.lang.lisp.

If someone of you had similar problems and have some idea, I would be
greatly thankful for any suggestions.

Maks.

Re: Importing tab-delimited text files or connecting to ODBC

[Markus Triska]

Hi!

Maks Romih wrote:

> What would be the best way to import some tables, like a tab delimited
> copy-paste chunk from excel. I would like to get the table into Emacs
> Lisp so that a table would be a big list of rows, where a row would be
> a list of atoms, either raw strings or, when possible, converted to
> Lisp numbers and symbols.

You can use R (www.r-project.org) to handle a number of different table 
formats uniformly. For example, given test.txt like this:

# directory size unit
/home/tester1 200 MB
/home/tester2 3000 KB
/home/tester3 250 MB

you can read it as a data frame:

 > data <- read.table("test.txt", as.is=c(T,F,T), skip=1)

You then use a function like this to print it the way you want:

elisp <- function (df) {
         cat("(")
         for (i in 1:(dim(df)[1])) {
                 cat("\n(")
                 for (j in 1:dim(df)[2]) {
                         val <- df[i,j]
                         if (is.numeric(val)) {
                                 cat(" ", val, " ", sep="")
                         } else {
                                 cat(" \"", val, "\" ", sep="")
                         }
                 }
                 cat(")\n")
         }
         cat(")\n")
}


 > elisp(data)
(
( "/home/tester1"  200  "MB" )

( "/home/tester2"  3000  "KB" )

( "/home/tester3"  250  "MB" )
)

These steps can be automated using R's batch facilities that you can 
call from Emacs. Some of the other conversions you mention can probably 
also be handled more directly in the R code.

All the best,
Markus.

Re: Importing tab-delimited text files or connecting to ODBC

[Thien-Thi Nguyen]

Maks Romih <maksr@snt.si> writes:

> If someone of you had similar problems and have some idea, I
> would be greatly thankful for any suggestions.

check out EDB (latest is 1.26p2), in dir:

  http://www.glug.org/people/ttn/software/edb/

particularly the files examples/names.{dat,edb,fmt} and
README, which has instructions on how to build and load
things for experimentation (w/o need for "make install").

if you find problems w/ EDB, please let me know about them
so that i can fix them (or write excuses for their persistence
in the fine manual ;-) for the next release.

thi

Re: Importing tab-delimited text files or connecting to ODBC

[Maks Romih]

Markus Triska <triska@gmx.at> writes:

> Hi!
> 
> Maks Romih wrote:
> 
> > What would be the best way to import some tables, like a tab delimited
> > copy-paste chunk from excel. I would like to get the table into Emacs
> > Lisp so that a table would be a big list of rows, where a row would be
> > a list of atoms, either raw strings or, when possible, converted to
> > Lisp numbers and symbols.
> 
> You can use R (www.r-project.org) to handle a number of different
> table formats uniformly. For example, given test.txt like this:
> 
> # directory size unit
> /home/tester1 200 MB
> /home/tester2 3000 KB
> /home/tester3 250 MB
> 
> you can read it as a data frame:
> 
>  > data <- read.table("test.txt", as.is=c(T,F,T), skip=1)
>
> . . .
>
> All the best,
> Markus.

Thank you for the reply.

But I see I would need to specify the column types for every file I
want to load. This I don't like. The program should infer the types of
the columns automatically, either from the data itself or from the
data dictionary over ODBC.

Maks.

Re: Importing tab-delimited text files or connecting to ODBC

[Maks Romih]

Thien-Thi Nguyen <ttn@glug.org> writes:

> Maks Romih <maksr@snt.si> writes:
> 
> > If someone of you had similar problems and have some idea, I
> > would be greatly thankful for any suggestions.
> 
> check out EDB (latest is 1.26p2), in dir:
> 
>   http://www.glug.org/people/ttn/software/edb/
> 
> particularly the files examples/names.{dat,edb,fmt} and
> README, which has instructions on how to build and load
> things for experimentation (w/o need for "make install").
> 
> if you find problems w/ EDB, please let me know about them
> so that i can fix them (or write excuses for their persistence
> in the fine manual ;-) for the next release.
> 
> thi

Very interesting! I was sure that emacs should have something about
databases and I searched many times about it on the internet but never
found anything useful. It's somewhat hidden in the myriad of emacs
packages.

I will take a look, of course, however I'm afraid that I still won't
find what I ask at the moment. Edb seems more a standalone database
but I want to have some tool to connect to or import from other data
sources, especially relational DBMS-es, excel, MS Access, etc.

Thank you for suggestion.

Maks.

Re: Importing tab-delimited text files or connecting to ODBC

[Eric Pement]

Maks Romih wrote:

> [...] I want to have some tool to connect to or import from other data
> sources, especially relational DBMS-es, excel, MS Access, etc.

I think the difficulty with your search being fulfilled is that you are
hoping to have Emacs, which is GNU-founded "free software", find a way
to import data structures created by proprietary, non-free software. I
suggest that there could be a philosophical hesitation for some people
in getting free software to support commercial, non-free software. I
think we're lucky to get GNU Emacs available for WinNT!

-- 
Eric Pement

Re: Importing tab-delimited text files or connecting to ODBC

[Maks Romih]

"Eric Pement" <pemente@northpark.edu> writes:

> Maks Romih wrote:
> 
> > [...] I want to have some tool to connect to or import from other data
> > sources, especially relational DBMS-es, excel, MS Access, etc.
> 
> I think the difficulty with your search being fulfilled is that you are
> hoping to have Emacs, which is GNU-founded "free software", find a way
> to import data structures created by proprietary, non-free software. I
> suggest that there could be a philosophical hesitation for some people
> in getting free software to support commercial, non-free software. I
> think we're lucky to get GNU Emacs available for WinNT!
> 
> -- 
> Eric Pement

I'm exploring these possibilities exactly because I'm not satisfied
with "non-free software".

Maks.

Re: Importing tab-delimited text files or connecting to ODBC

[Tim McNamara]

Maks Romih <maksr@snt.si> writes:

> "Eric Pement" <pemente@northpark.edu> writes:
>
>> Maks Romih wrote:
>> 
>> > [...] I want to have some tool to connect to or import from other
>> > data sources, especially relational DBMS-es, excel, MS Access,
>> > etc.
>> 
>> I think the difficulty with your search being fulfilled is that you
>> are hoping to have Emacs, which is GNU-founded "free software",
>> find a way to import data structures created by proprietary,
>> non-free software. I suggest that there could be a philosophical
>> hesitation for some people in getting free software to support
>> commercial, non-free software. I think we're lucky to get GNU Emacs
>> available for WinNT!
>
> I'm exploring these possibilities exactly because I'm not satisfied
> with "non-free software".

I would recommend comparing what file types Emacs can import into the
major modes of interest to you, and then seeing what the unfree
applications can export.  For example, there are many types of
spreadsheet and database file formats that are cross-compatible.  CSV,
SYLK, etc.  Many unfree applications do support file types that free
software applications can read.

Re: Importing tab-delimited text files or connecting to ODBC

[Thien-Thi Nguyen]

Maks Romih <maksr@snt.si> writes:

> I will take a look, of course, however I'm afraid that I still
> won't find what I ask at the moment. Edb seems more a standalone
> database but I want to have some tool to connect to or import
> from other data sources, especially relational DBMS-es, excel,
> MS Access, etc.

it is true that EDB is (still) weak in terms of dealing directly
w/ external processes; you must read from, and write to, a file.
however, this stipulation is not so onerous since emacs allows you
to define file handlers (based on stylized filenames) that can
mediate between EDB and external processes.

to play, look at variable `file-name-handler-alist', and the
source code for the handful of builtin handlers listed there.
EDB itself messes around a bit w/ this handler alist, but very
very simply -- better to study e.g. `tramp-file-name-handler'.

probably a neat hack would be to wrap `x-get-selection' (or
whatever is equivalent for your platform) for reading and the
analog for writing, so that you could select some spreadsheet
range, invoke EDB on it, and save it back into the spreadsheet
(kind of like emacsclient for spreadsheet ranges).

another item for the TODO list, i suppose...

thi

Re: Importing tab-delimited text files or connecting to ODBC

[Peter S Galbraith]

Tim McNamara <timmcn@bitstream.net> wrote:

> Maks Romih <maksr@snt.si> writes:
> 
> > "Eric Pement" <pemente@northpark.edu> writes:
> >
> >> Maks Romih wrote:
> >> 
> >> > [...] I want to have some tool to connect to or import from other
> >> > data sources, especially relational DBMS-es, excel, MS Access,
> >> > etc.
> >> 
> >> I think the difficulty with your search being fulfilled is that you
> >> are hoping to have Emacs, which is GNU-founded "free software",
> >> find a way to import data structures created by proprietary,
> >> non-free software. I suggest that there could be a philosophical
> >> hesitation for some people in getting free software to support
> >> commercial, non-free software. I think we're lucky to get GNU Emacs
> >> available for WinNT!
> >
> > I'm exploring these possibilities exactly because I'm not satisfied
> > with "non-free software".
> 
> I would recommend comparing what file types Emacs can import into the
> major modes of interest to you, and then seeing what the unfree
> applications can export.  For example, there are many types of
> spreadsheet and database file formats that are cross-compatible.  CSV,
> SYLK, etc.  Many unfree applications do support file types that free
> software applications can read.

If you export the Excel data as CSV, then  you can use csv-mode.el to
read them in Emacs.  It's likely that table.el can import them too.

If you use Debian, install the package emacs-goodies-el to get both
those add-ons.

Peter