Importing Data to Excel Help

Long story short, I am wanting to make a master list of my photos in excel. I have a program that will export the exif data I need to html or txt files but exports it as one html or txt file per photo. I am wanting to import these into one master list but am having trouble getting them to import into the right rows and such. Any tips?

Also is it possible to import only selected data from each html or txt file? I saw where I can select only certain columns to import but I haven't found where I can knock out rows.

Also, any batch processing feature anyone know of so once I successfully do this once I can have the computer do the rest for me?

PS: I know I am running Excel 2008 on Mac but I figure this forum has the biggest group of excel users in it and I have used both windows and mac based excel and the features are pretty much the same.

Thanks alot everyone!

 

That very much depends...and the mac v. pc may make a difference. I don't know Excel for Mac, but with Excel for PC you can use VBA routines to do some pretty complicated things.

Just about anything can be done if you can account for the variables, but your variables are pretty complicated.

Given your vague description, you would be doing something like:

For example, lets say you exported all the exif data to files incrementally named exif1.txt, exif2.txt, etc. You could create an excel VBA routine that will open the file, import the data from rows a,b,c,f,k, close the file, then using a counter open the next file and repeat (loop) the import data code.

But, this assumes your exif data files are incrementally named and they are identical so excel will know where to extract the data.

 

This is simple, but may not work for you.. if they're in text files you can just do..

type *.txt > mergefile.txt

mergefile.txt will then have the entire contents of every .txt file in that directory.

Then using a macro might be a bit easier in excel (since you have a single file to work with) to get rid of the data you don't want to keep.

 

Both are true. The files are named by the picture names so yes they are DSC_0001.txt DSC_002.txt and so forth. Some photos are deleted of course but as long as I can start the string over once it stops at the next one that seems to be okay. And the program that makes the files are consistant enough for this I think. I will look more into this VBA bit...

I am pretty sure the Mac and Windows versions are close enough but if needed, I can use the excel on my sister's dell or something, not a big deal there.

Thank you so much for heading me in a direction, any tips for VBA or a website or something you know that can help get me aquainted with it would be helpful.

Edite: Also a good idea Brandon, I will play with that too...

 

You can add a condition to the loop, if the file exists, do routine, if not, skip and increment to next file.

It can take some time to learn VBA to do what you want, but not as much time as you might fear. Here is a website by John Walkenback, who is THE Excel vba master

http://spreadsheetpage.com/

He has written several books that are easily accessible; not sure what your level of knowledge is or your willingness to learn.

I picked up an earlier version of his book many years ago, Excel 2000 Power Programming with VBA, but he has updated it through the current Excel.

http://www.amazon.com/Excel-Power-P...s-Bookshelf/dp/0470044012/ref=ntt_at_ep_dpi_2

Within a week I was creating Excel Programs that were pretty darned impressive using some of the basic building blocks...I'm am pretty sure you could do everything you need with just this book provided you can put disparate pieces together.

This is more of a "this is how you do "very specific thing" and then you need to come up with ways to link all these things together. I am not a programmer at all, but a hardware guy, but it was suffficient to get me going.

 

Okay I was just poking around google and came across an on-line VBA tutorial and it looks alot like Matlab which I use alot in my engineering classes so I get how to do loops and stuff like that... this might be a christmas break project...

This may be a redundant, but where do I find Visual Basics? Does it come with windows? Is it free ware?

Also, although I think I could do this, if anyone has any other ideas I would appreciate it.

 

It comes inside Windows Office programs--access by hitting ALT-F11

 

Wow, nice!

 

I should note here that an excel macro is just vba--usually pretty sloppy vba

A lot of times, I will record a macro to write the vba if I have a long sequence, then go in and edit the vba code created by the macro and add things like loops and conditional (if this, do this) statements.

Saves time and gives you a nice introduction to the code