Importing Data into Excel Help

I have been working on this problem a while and to make a long story short, I am trying to make an excel sheet on all of my photos. I have found a program to exports exif information in html or txt files and it is the only program I have found that displays the fields I need (Windows XP title, comments, keywords, etc).

Anyway, the program will only export all of the data (I can't just select the fields I want) and it also makes a new files for each photos (and names them whatever the photo is named so yes sequential). I have been playing with importing html and txt files into Excel 2008 and need help. I am looking for a way to only import the rows I need and to automate it so I don't have to do 10,000 photos by hand.

On another forum, it was suggested to use VBA (visual basics) in excel on windows which looks promising but I know little about it and will have to borrow my sister's dell to do. Another suggestion was to combine the text files and use a Macro in excel in which I also have little idea on how to use.

So what do yall think? Can Apple Script or Automator or any thing else help me here? I know Excel 2008 for Mac doesn't have VB but what about macro on it?

Thanks,
Brian

 

Yes, I can tonight. I am on campus right now and don't have my Mac on me. Thank you so much.

 

Okay here is what the txt files look like...

DSC_1048.jpg - EXIF Information
- Exif
Make NIKON CORPORATION
Model NIKON D70
Orientation Top, left side (Horizontal / normal)
X Resolution 300 dots per inch
Y Resolution 300 dots per inch
Resolution Unit Inch
Software Ver.2.00
Date/Time 2007:05:21 09:23:43
YCbCr Positioning Datum point
Exposure Time 1/500 sec
F-Number F6.3
Exposure Program Aperture priority
Exif Version 2.21
Date/Time Original 2007:05:21 09:23:43
Date/Time Digitized 2007:05:21 09:23:43
----------
The part I need besides the file name and date is a bit further down...

Windows XP Title BNSF 3036
Windows XP Comment The 3036 still sports in nice blue and yellow as it sits in Temple on Monday, May 21, 2007.
Windows XP Author Brian Jansky
Windows XP Keywords BNSF, GP40X, SF
Windows XP Subject BNSF 3036

Importing recognizes the tab between and I can successfully get the titles off of the information.

Any thoughts?

 

How would you go about doing this? I know I can probably hack my way through VBA with Windows but would have to either load excel on to my XP in Fusion or use my sister's laptop so if you think I can pull this off by prasing it all into a comma delimted file and import that into excel on OS X then I would like to try that.

Thanks,
Brian

 

Thank you so much. It is finals time here at Texas A&M but I will try this once I get some free time next week and let you know how it goes. Thank you again!!!!

 

I am wondering if someone can help me. I PM'd rowsdower but I think he is out on vacation or something so maybe someone else can point me in the right direction.

Okay, I got the python to run by just double clicking on it. I hope that was okay. But the csv it created was blank... I was looking at the code and I am thinking it is the top line... what do you think?

Here is the code I am using:
------------
#!/usr/bin/env python

import sys

f = open("processed.csv", "w")
for fn in sys.argv:
g = open(fn)
data = {}
data['Picture Filename'] = g.readline().split()[0]
for line in g.readlines():
try:
data[line.split("\t")[0]] = line.split("\t")[1].splitlines()[0]
except IndexError:
pass
try:
f.write("%(Picture Filename)s, %(Date/Time Original)s, %(Windows XP Comment)s\n" % data)
except KeyError:
passx
-----------------------

The python file is called fix_pictures.py and it located in the file with all of the txt files. The folder is located at Macintosh HD/Users/brianj/Desktop/exif

Thanks alot for helping with this!
Brian

 

Finally got it to work! I put the txt files into a folder on my desktop and put the python script in with it. I have some problems with how the data is divided into cells but nothing that can't be fixed. This is the final code:

Code:

#!/usr/bin/env python

import sys

f = open("processed.xls", "w")
for fn in sys.argv:
	g = open(fn)
	data = {}
	data['Picture Filename'] = g.readline().split()[0]
	for line in g.readlines():
		try:
			data[line.split("\t")[0]] = line.split("\t")[1].splitlines()[0]
		except IndexError:
			pass
	try:
		f.write("%(Picture Filename)s, %(Date/Time Original)s, %(Windows XP Title)s, %(Windows XP Keywords)s, %(Windows XP Comment)s\n" % data)
	except KeyError:
		pass