Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.soft-sys.math.mathematica > #5147

Re: Changing excel table with Wolfram Mathematica

From "Hans Michel" <hmichel@cox.net>
Newsgroups comp.soft-sys.math.mathematica
Subject Re: Changing excel table with Wolfram Mathematica
Date 2011-09-22 11:35 +0000
Organization Steven M. Christensen and Associates, Inc and MathTensor, Inc.
Message-ID <j5f6i7$qae$1@smc.vnet.net> (permalink)
References <201109191106.HAA19601@smc.vnet.net> <j59p4t$rn$1@smc.vnet.net> <201109210934.FAA13192@smc.vnet.net>

Show all headers | View raw


Alexey:

The poster did not have any constraint in his question that related to
performance. 

I could have posted something like this
In[3]:= lexx = {{1, 1, 1, 1}, {1, 1, 1, 1}, {1, 1, 2, 2}, {1, 1, 2, 2}}

Out[3]= {{1, 1, 1, 1}, {1, 1, 1, 1}, {1, 1, 2, 2}, {1, 1, 2, 2}}

In[4]:= lexx // TableView

WRI still undocumented feature
(http://forums.wolfram.com/mathgroup/archive/2009/Oct/msg00186.html)

Looks like Excel cells but not much is publically known as to what WRI
intent is on this view. On can interactively edit cells.

But this is not what the poster asked. 

As to your question. I would not know which JDBC connection from Mathematica
to Excel vs JDBC connection from Mathematica to HSQL is faster or more
memory efficient.

If I had to hazard a guess, I would say a SQL compliant database with good
indexing capability running in-memory should in principle out perform a
somewhat proprietary file based structure that has to be deflated upon
opening and using non-standard SQL-like language for ODBC communications.

I was merely trying to point the poster towards a solution path.

Hans 
PS
Nice post on SO. Have you tried your solution using ToSymbolicXML and
FromSymbolicXML to take advantage of WRI internal representations.

-----Original Message-----
From: Alexey Popkov [mailto:lehin.p@gmail.com] 
Sent: Wednesday, September 21, 2011 4:35 AM
Subject: Re: Changing excel table with Wolfram Mathematica

Hans,

Very interesting, thank you for pointing this out! But what is about
performance? Is working with Excel file is approximately as fast and
memory efficient as working with Mathematica's built-in SQL database?

Alexey

P.S. It is quite simple to work with Mathematica's built-in SQL
database by using a set of simple supporting functions presented here
("SQL Approach" section):
http://stackoverflow.com/questions/5287817/the-best-way-to-construct-a-funct
ion-with-memory/5291299#5291299

On 20 Sep, 14:15, "Hans Michel" <hmic...@cox.net> wrote:
> Every once in a while someone asked this question.
>
> No is the answer to your constrained question:  using Mathematica Built-In
> functions to update just a few Excel worksheet cells in an Excel workbook
> without overwriting the file on using Export.
>
> Without your constraints you can Import the entire worksheet, make changes
> to the values you need, Export the entire worksheet out. OR
>
> You can use DatabaseLink. Treat Excel file as a database. You would use a
> JDBC (ODBC) connection to the Excel worksheet. Then using Excel's SQL-like
> language you can Update the required cells.
>
> It is very hard to provide details of using DatabaseLink in this way,
> because depending on platform and user's skill set and level of experience
> using ODBC it is a painful process to explain and work through.
>
> But this is where I would point you towards to start:
>
> http://reference.wolfram.com/mathematica/DatabaseLink/tutorial/Databa...
> ctions.html#27756
>
> In "Setting Up the Connection" section the image of the ODBC Datasource
> Administrator (depending on your system)
>
> "Excel Files" is a choice. So one can connect to an Excel file.
>
> If the poster pursues the DatabaseLink route, I would advise the poster to
> first make a successful ODBC connection to an Excel file and do an update
of
> a cell and close the connection. Do this if time permits using external
> tools Java program. When you get familiar with the concept and its use I
> would say then you are ready to use DatabaseLink. Otherwise there will be
a
> barrage of questions stemming from rookie mistakes such as ("Now I can't
> open my Excel file?" Did you close the connection.)
> ("It is not working?" Do you have the correct ODBC-JDBC Driver for the
> application your are connecting.)
>
> If you understand what I am pointing towards then go ahead and jump right
> into DatabaseLink.
>
> Hans
>
>
>
>
>
>
>
> -----Original Message-----
> From: Kuba [mailto:drazen.kubato...@zg.htnet.hr]
> Sent: Monday, September 19, 2011 6:06 AM
> Subject: Changing excel table with Wolfram Mathematica
>
>     I would like to change some cells in Excel table with Mathematica. I
> know that can be done with "Mathematica link for Excel", but is any way
how
> can I change this with Mathematica built-in functions? For example:
>
>     Table from A1 to D4
>
> 1 1 1 1
> 1 1 1 1
> 1 1 2 2
> 1 1 2 2
>
> I wont change cells from C3 to D4, to set new value. For example 2 becomes
> 3.
> It is important that all other cells stay untouched.
> Thank you very much.


Back to comp.soft-sys.math.mathematica | Previous | Next — Previous in thread | Find similar | Unroll thread


Thread

Re: Changing excel table with Wolfram Mathematica "Hans Michel" <hmichel@cox.net> - 2011-09-20 10:15 +0000
  Re: Changing excel table with Wolfram Mathematica Alexey Popkov <lehin.p@gmail.com> - 2011-09-21 09:40 +0000
  Re: Changing excel table with Wolfram Mathematica "Hans Michel" <hmichel@cox.net> - 2011-09-22 11:35 +0000

csiph-web