Importing Data into Excel Help

[BrianJ844]

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

 

[miles01110]

What format is the text file in? What I mean by format is, how are the data entries arranged? Could you paste a sample line from the generated text file?

 

[BrianJ844]

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

 

[BrianJ844]

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?

 

[rowsdower]

I think it would be possible to do this with VBA, but I can't give you much advice on that. I think you would want to write a program to import the data, search for the field you want, and put it into a different sheet. If I had to do it myself, I would write a preprocessing script to parse all of the EXIF files and put the data into a comma delimited file with one line per picture. Excel could import that directly.

 

[BrianJ844]

If I had to do it myself, I would write a preprocessing script to parse all of the EXIF files and put the data into a comma delimited file with one line per picture. Excel could import that directly.

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

 

[rowsdower]

I haven't done a lot of testing, but something like this Python script should work:

#!/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:
		pass

Put all of your EXIF info files in a directory (e.g. /Users/yourusername/Desktop/EXIF). Save the code as a file (e.g. "fix_pictures.py") and run it in the Terminal like this:

python fix_pictures.py /Users/yourusername/Desktop/EXIF/*

It will create a file called processed.csv that has all of the data in it. You can customize what goes in the processed file by changing the line:

f.write("%(Picture Filename)s, %(Date/Time Original)s, %(Windows XP Comment)s\n" % data)

Hope that helps.

EDIT: I forgot to mention that this is very sensitive to the files being exactly in the same format. The first line must be in the form "filename.jpg - EXIF Information". The data must have tabs between the labels and the values.

 

[BrianJ844]

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!!!!

 

[rowsdower]

No problem. It's finals time here too. I will try to keep an eye on this thread, but send me a PM if I don't respond.

 

[BrianJ844]

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

 

[BrianJ844]

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:

#!/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