topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 4:32 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: join operations with excel tables (free?)  (Read 10858 times)

urlwolf

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 1,837
    • View Profile
    • Donate to Member
join operations with excel tables (free?)
« on: March 16, 2007, 08:43 AM »
Hi,

is there any free (or cheap) program that can do join operations with excel tables? I have found digDB, but is pricey and it's only a six-month license...

Thanks

bmm

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 15
    • View Profile
    • Bent Møller Madsen
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #1 on: March 16, 2007, 12:32 PM »
You can take a look at Kettle / Pentaho Data Integration. This is a very nice open source data integration/ETL (Extraction, Transformation and Loading) tool. It is more focused at integrating data from databases and other sources into data warehouse solutions, but it can also be used for "simple" excel transformations.

It is probably somewhat overkill for what you are after and it does have a learning barrier, but if you have some database knowledge it is very powerful.

urlwolf

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 1,837
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #2 on: March 16, 2007, 01:18 PM »
You can take a look at Kettle / Pentaho Data Integration. This is a very nice open source data integration/ETL (Extraction, Transformation and Loading) tool. It is more focused at integrating data from databases and other sources into data warehouse solutions, but it can also be used for "simple" excel transformations.

It is probably somewhat overkill for what you are after and it does have a learning barrier, but if you have some database knowledge it is very powerful.

Thanks bmm, love the avatar.
Looks like pentaho spreadsheet services is not free, and they only give a quote by mail. The entire thing seems much more complicated that I expected. I honestly don't know what kettle does after about a min. in their page. Looks like this would require serious time investment.

digDB seems to do join operations, which is what I want. But I'm sure there must be free alternatives.

I used to do all data mungling in R, but I have seen some hardcore excel people doing cool stuff visually, and I'm trying to 'learn' excel (pivot tables etc) after ignoring it for my entire life.

kfitting

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 593
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #3 on: March 16, 2007, 03:47 PM »
Do you know VBA?  Not sure exactly what you want to do, but VBA would allow you to join spreadsheets from different workbooks, GUI or no GUI.

Kevin 

tinjaw

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 1,927
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #4 on: March 16, 2007, 06:54 PM »
NOOOOOOOOOOOOOOOOO!!! Don't go over to the dark side!!!!!

You are a Jedi of the command line - Wizard of the script - Oracle of code. Do not succumb to the glitz and glitter of the GUI dark side! For all they offer is vain. Once enticed you will only then learn of the shallowness of the dark side. It is a mere facade that will prove weak and easily overtaxed with all but the meagerest of demands upon it.

Kevin, IMNeverSHO, is right. If you can "do" R, you should look to VBA. With VBA you will be able to take from the dark side it's meager offerings of GUI goodness and yet resist the temptation to do GUI gymnastics when CodeFu will get you there via a more elegant path.

We, brothers of the code, are here for you. We have behind us the knowledge of many before us. The knowledge upon which the GUI generation is dependent. GUIs shall always stand upon the ground made solid by our brethren. We, keepers of Ada, custodians of Lisp, clerics of C, protectors of Perl too have our young generation of warriors. We too enter the future with youth and vigor, but such as ours is built on code and shall protect the earth through Ruby and Python. Have faith in your ancestors urlwolf. Our strength runs deep throughout the universe.

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 40,896
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #5 on: March 17, 2007, 06:44 AM »
lol, tinjaw.

i'm not an excel person, but the truth is even as a coder i almost always look first for an existing GUI tool to do manipulation.  however i will admit that i almost always would have been better off learning to script a solution.  so if you don't find a quick gui solution, take tinjaw's advice and embrace the idea of writing a vba script in excel to do what you want -- you'll find some help on this forum to get it to work  :up:

urlwolf

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 1,837
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #6 on: March 17, 2007, 08:35 AM »
hehe, that's a really good post tinjaw.

I'm a code monkey like anyone here, but I agree with mouser. I have written a library in R with wrappers around reshape() and merge() to do something that excel does natively and easily with pivot tables (heh! shame on me!).

Beware: some programmers believe that people relying on a GUI are simpletons that don't know better. That's simply not true. There is a lesson to be learnt here (in my experience at least!): sometimes the mainstream GUI equivalent does the job better and faster. You don't need to code everything. Spending time looking for the right tool pays off, one just has to find a balance (This is why this forum is so good, it helps fidning the right tools).

tinjaw

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 1,927
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #7 on: March 17, 2007, 08:38 AM »
Actually that was just the excuse I give myself when I, once again, cannot find the time to teach myself pivot tables.  ;)

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #8 on: March 17, 2007, 03:02 PM »
for help w/ excel & vba:

http://vbaexpress.com/

and the microsoft.public.excel.programming newsgroup

very helpful people, just like around here

urlwolf

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 1,837
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #9 on: March 17, 2007, 03:37 PM »
for help w/ excel & vba:

http://vbaexpress.com/

This one  shows an empty page with the forum header only. Even after signing up. But thanks, the other one looks good.

I'm not sure I'll need to invest time in VBA just yet, this is all pretty basic. For example, I want to do a simple recode operation.

in a matrix, I want to replace all values with a different code. For example, if I have say a matrix of names, and I want to get a matrix of emails, having a table with the mail-name equivalence.

This is probably built in. I'm ashamed to realize how little I know about excel :(


kfitting

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 593
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #10 on: March 17, 2007, 09:27 PM »
Honestly, this is the perfect time to learn VBA: you have a somewhat simple task to perform!  VBA is useful for everything from performing batch operations on cells, to full-fledge programs.  At work we use excel for storing data (yes, not the best format!) and I've done a fair amount of coding for VBA.  If you have to do the same thing more than 5 times it's worth writing a script for it! 

The power of VBA notwithstanding, I am still amazed at the limitations of Excel and VBA.  Every once in a while I run into something small and it just amazes me. 

If you use excel a lot, I'd seriously recommend learning VBA... it will save LOTS of time and headache in the long run!

Kevin

AndyM

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
Re: join operations with excel tables (free?)
« Reply #11 on: March 19, 2007, 06:59 PM »
This one  shows an empty page with the forum header only. Even after signing up. But thanks, the other one looks good.

try

http://vbaexpress.com/forum/

or

http://vbaexpress.co...orumdisplay.php?f=17