Importing Data into Excel Help

Discussion in 'Mac Apps and Mac App Store' started by BrianJ844, Dec 8, 2009.

  1. BrianJ844 macrumors member

    Joined:
    Dec 8, 2009
    Location:
    Houston, Tx
    #1
    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
     
  2. miles01110 macrumors Core

    miles01110

    Joined:
    Jul 24, 2006
    Location:
    The Ivory Tower (I'm not coming down)
    #2
    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?
     
  3. BrianJ844 thread starter macrumors member

    Joined:
    Dec 8, 2009
    Location:
    Houston, Tx
    #3
    Yes, I can tonight. I am on campus right now and don't have my Mac on me. Thank you so much.
     
  4. BrianJ844 thread starter macrumors member

    Joined:
    Dec 8, 2009
    Location:
    Houston, Tx
    #4
    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?
     
  5. rowsdower macrumors 6502

    Joined:
    Jun 2, 2009
    #5
    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.
     
  6. BrianJ844 thread starter macrumors member

    Joined:
    Dec 8, 2009
    Location:
    Houston, Tx
    #6
    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
     
  7. rowsdower macrumors 6502

    Joined:
    Jun 2, 2009
    #7
    I haven't done a lot of testing, but something like this Python script should work:

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

    Code:
    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:

    Code:
    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.
     
  8. BrianJ844 thread starter macrumors member

    Joined:
    Dec 8, 2009
    Location:
    Houston, Tx
    #8
    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!!!!
     
  9. rowsdower macrumors 6502

    Joined:
    Jun 2, 2009
    #9
    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.
     
  10. BrianJ844 thread starter macrumors member

    Joined:
    Dec 8, 2009
    Location:
    Houston, Tx
    #10
    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
     
  11. BrianJ844 thread starter macrumors member

    Joined:
    Dec 8, 2009
    Location:
    Houston, Tx
    #11
    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
     

Share This Page