Using Tcl to inquire Excel tables

[Alexandru]

Hi,

I have a bunch of Excel tables exported from Salesforce.
This is like a data dump. Everything ever made in Salesforce is in those Excel tables.

I want to use Tcl to inquire things saved in those Excel tables.

What would be the quick and dirty solution for this task?

Should I first import Excel data into a database such as SQL Light or should I do this in pure Tcl, perhaps using dicts?

Thanks
Alexandru

[Robert Heller]

At Mon, 27 Nov 2017 11:46:36 -0800 (PST) Alexandru <alexandr...@meshparts.de> wrote:

>
> Hi,
>
> I have a bunch of Excel tables exported from Salesforce.
> This is like a data dump. Everything ever made in Salesforce is in those Excel tables.
>
> I want to use Tcl to inquire things saved in those Excel tables.
>
> What would be the quick and dirty solution for this task?

Using oocalc (or localc) at the command line:

oocalc --headless --convert-to csv table.xlsx

OR use oocalc/localc or Excel itself and "Save As" a csv file using the point
and click interface.

Then you can use the struct::matrix and the csv packages from tcllib:

package require struct::matrix
package require csv

set fp [open "table.csv" r]
set headers [::csv::split [gets $fp]]
set m [::struct::matrix]
$m add columns [llength $headers]
::csv::read2matrix $fp $m
close $fp


At this point all of your data is in $m

Read the man page for struct::matrix:

man matrix

for info on how to do stuff with the matrix (eg access cells, etc.).

>
> Should I first import Excel data into a database such as SQL Light or should I do this in pure Tcl, perhaps using dicts?
>
> Thanks
> Alexandru
>

--
Robert Heller -- 978-544-6933
Deepwoods Software -- Custom Software Services
http://www.deepsoft.com/ -- Linux Administration Services
hel...@deepsoft.com -- Webhosting Services

[Alexandru]

Thanks Robert, This is a new approach to me.
But I want to inquire data over multiple files.
Something like a Database inquiry over multiple tables.
This is why I asked if I must use a true database.
Or is there another useful package in pure Tcl that could work e.g. with dicts?

[Gerald Lester]

> Thanks Robert, This is a new approach to me.
> But I want to inquire data over multiple files.
> Something like a Database inquiry over multiple tables.
> This is why I asked if I must use a true database.
> Or is there another useful package in pure Tcl that could work e.g. with dicts?
>

Personally, I'd read the Excel from Tcl via the either Tcom or
tdbc::oldbc (see http://nikit.tcl.tk/page/Tclodbc) and creating an
SQLite DB (again via tdbc).

Then data mining the SQLite DB either with Tcl script(s) or another tool.

--
+----------------------------------------------------------------------+
| Gerald W. Lester, President, KNG Consulting LLC |
| Email: Gerald...@kng-consulting.net |
+----------------------------------------------------------------------+

[Mike Griffiths]

I've been using the Cawt extension (https://wiki.tcl.tk/37468) at work for creating Excel spreadsheets, but it can also read them, and I highly recommend it. (It uses COM, so it's Windows-only, though.)

[Alexandru]

I just realized, I can also get those Excel files as CSV files.
So reading is simple using csv package should be no problem.
The more important task is to perform database like inquiries over at least two tables.

As Gerald stated, converting to SQLite might be a way.
If nothing else is proposed here, I will give it a try with SQLite...

Thank you all for the fast help.

[Robert Heller]

You just load multiple files into multiple matrixes (one matrix for each
file). Then access the multiple tables in your program. There is no need to
use a database, unless you are really more comfortable with SQL than Tcl...

What exactly do you want to do? All we know at present is that you have some
Excel files that you want to "query" (whatever you mean by that).

> Or is there another useful package in pure Tcl that could work e.g. with
> dicts?
>

[Alexandru]

A very simple example:

Table 1, Col 1: Costumer Name
Table 1, Col 2: Costumer ID
Table 2, Col 1: Costumer ID
Table 2, Col 2: Sent EMails

Query:
Which emails were sent by a specific customer name

In true life, the queries might be much more complicated.

[Robert Heller]

OK, it sounds like going from csv to SQLite and then doing SQL queries makes
sense.

[Rich]

Alexandru <alexandr...@meshparts.de> wrote:
> A very simple example:
>
> Table 1, Col 1: Costumer Name
> Table 1, Col 2: Costumer ID
> Table 2, Col 1: Costumer ID
> Table 2, Col 2: Sent EMails
>
> Query:
> Which emails were sent by a specific customer name
>
> In true life, the queries might be much more complicated.

If you end up with "more complicated" then your best bet is to load
your CSV files into sqlite, create appropriate indexes, and run sql
queries against the data.

For your simple example, you'd have:

table1: id, name
table2: id, sent_emails

And the query would be:

select table1.id, name, sent_emails from table1, table2
where table1.id = table2.id;

Which would give you:

id, Name, sent emails

as the output 'table'.

Read up on SQL for 'more complicated' stuff.

[Alexandru]

Thanks Rich,

We have started with the implementation of the process you describe.

Maybe we can develop this to a new package from this for general use.
I'll keep you informed.