9

I have a function that is set up to write to a database with 10-20 standard variables with 10 fields each, but the application is going to a place where database access is not available. Can Mathematica read an Excel file and pick up the portions of the file that relate to one of these 20 variables, change them if needed and write the whole thing back again? If so, how would that be accomplished? Thanks!

J. M.'s missing motivation
  • 124,525
  • 11
  • 401
  • 574
Nothingtoseehere
  • 4,518
  • 2
  • 30
  • 58
  • 1
    Yes. See http://reference.wolfram.com/mathematica/guide/ImportingAndExporting.html You could keep each "table" in a separate Excel file or de-normalize the entire database and store it in a single file. You could also just store everything in CSV. – Jagra Jun 18 '12 at 22:29
  • 6
    Beware of where you are going ... Excel is not transactional, not multiuser, not robust, ... it is NOT a DB – Dr. belisarius Jun 18 '12 at 22:37
  • @belisarius Totally agree. I was trying for a read it all in and write it all out type of solution. No transactions available. – Nothingtoseehere Jun 18 '12 at 22:55
  • @Jagra that is certainly an easy solution, but to be able to do this as one file would be best. On the read I get the whole file, but if a new variable must be written, the previous variables must be held as well. This is the sticking point for me. How to make sure the whole file maintains it's structure when adding another variable. Thanks for your help! – Nothingtoseehere Jun 18 '12 at 22:59
  • Why not mySQL or one of the other free databases + DatabaseLink (http://reference.wolfram.com/mathematica/DatabaseLink/tutorial/Overview.html). This would enable you to manipulate a real database directly from Mathematica. Also, reasonably portable. You could even access one of those online databases – Jagra Jun 18 '12 at 23:22
  • @Jagra this program would normally access a db using DatabaseLink. In this case a connectivity to that database is not possible. Thanks! – Nothingtoseehere Jun 18 '12 at 23:25
  • 1
    There is a way to manipulate Excel like you would with VBA: http://mathematica.stackexchange.com/questions/4594/open-excel-file-with-mathematica – faysou Jun 19 '12 at 06:14
  • 2
    It's probably much better to go with a real database. Having said that, perhaps you could use Leonid's SQL DSL as a starting point and just operate on mathematica lists, which you could export to a file. – Ajasja Jun 19 '12 at 08:06
  • @Ajasja this looks very promising! Thank you! – Nothingtoseehere Jun 19 '12 at 12:25
  • @FaysalAberkane Interesting approach! I know nothing about Com objects! Thanks for the suggestion! – Nothingtoseehere Jun 19 '12 at 12:58

1 Answers1

5

According to the comments you have several options:

  • Use Import and Export to work with Excel files (noted: this is not straightforward with problems mentioned in the comments).
  • Use this answer to manipulate the Excel file like it is done in VB.
  • Using Leonid's SQL DSL as a way to get database like syntax while keeping all the data in Mathematica lists.

I would also like to add that Mathematica comes with a built in, standalone, database called HSQL. Wherever Mathematica exists that database also exists and I wonder if perhaps you missed this when you said that you cannot use databases where this code is going. There is an example of how to set up an HSQL database as well as many example queries here.

Community
  • 1
C. E.
  • 70,533
  • 6
  • 140
  • 264