How to make Access run ExifTool to retrieve metadata/tiff tags? (1 Viewer)

JamesGreg

New member
Local time
Tomorrow, 06:23
Joined
Feb 17, 2021
Messages
15
I have a some what strange request. I've done some digging on the forum already to find anything related to my issue but to no avail.

I'm running a technical metadata (TIFF Tags) check of files using ExifTool.

I use the command prompt to run me the list and export it as a txt or csv.

An example of this command line is:

exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv

So this example will give me the EXIF data of all TIF's found in the folder location and export it to the destination as a CSV.

Now I've been using Access in recent weeks to import this data into tables and compare them at various stages of capture and creation. For example, when the file is made the ExifTool is run. When the file(s) have undergone editing again the tool is run. And before it is ready to be completed it is run again. These metadata's are imported in Access for me to compare the data and see if there are any variances that are unusual or sign of errors. But doing this constantly has become very tedious and I've been wondering for some time now if I could simply run the Exiftool from Access itself and then import the list using VB into a table for comparison. But I have no idea where to start and there doesn't seem to be much info online regarding this issue.

The only ideas I've found so far is perhaps using Shell to run this? But that's pretty vague.

Anyone have any experience with something related to ExifTool or simply communicating with the command prompt in Access?
 

Isaac

Lifelong Learner
Local time
Today, 12:23
Joined
Mar 14, 2017
Messages
8,738
Wait .. All you're needing to do is automate the creation and execution of a bat file, using VBA, right? There's tons of community research and support for this. Has nothing to do with Exif, it's just "how to create and run a bat file using vba" - try that search and you'll likely come up with tons of answers.

May I suggest to get you started:

- use Scripting.Filesystemobject (fso)
- use Textstream object (set ts = fso.createtextfile, end the file name with .bat)
- write text to the textstream, then close it
- shell bat
- write code to import resulting CSV into Access.

Definitely a lot of precedent for this.
 

bastanu

AWF VIP
Local time
Today, 12:23
Joined
Apr 13, 2010
Messages
1,401
You would use Shell to run ExifTool to create your .csv file then use Docmd.TransferText to import the csv into your Access table for analysis.

Cheers,
Vlad
 

JamesGreg

New member
Local time
Tomorrow, 06:23
Joined
Feb 17, 2021
Messages
15
Thankyou for all the replies. I've done some research into all the responses. As @bastanu says, I want to run the exiftool to create the .csv and then import into table.

But Vlad is there a way to use the command prompt to run my command line like in OP?

Code:
exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv

Sorry I'm a newbie at Access.

Code:
Public Sub ExifRunAndImport()

Dim RunExif

RunExif = Shell("exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv", vbNormalFocus)

DoCmd.TransferText acImportDelim, , tblData

End Sub

This doesn't seem to do what I wanted..
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:23
Joined
Oct 29, 2018
Messages
21,358
Thankyou for all the replies. I've done some research into all the responses. As @bastanu says, I want to run the exiftool to create the .csv and then import into table.

But Vlad is there a way to use the command prompt to run my command line like in OP?

Code:
exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv

Sorry I'm a newbie at Access.

Code:
Public Sub ExifRunAndImport()

Dim RunExif

RunExif = Shell("exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv", vbNormalFocus)

DoCmd.TransferText acImportDelim, , tblData

End Sub

This doesn't seem to do what I wanted..
Perhaps it's a timing issue. You didn't elaborate on what happens with that code, but it may be possible the ExifTool isn't finished working on the files yet by the time Access tries to import the results. Try adding some sort of a time delay between the two processes.
 

JamesGreg

New member
Local time
Tomorrow, 06:23
Joined
Feb 17, 2021
Messages
15
Perhaps it's a timing issue. You didn't elaborate on what happens with that code, but it may be possible the ExifTool isn't finished working on the files yet by the time Access tries to import the results. Try adding some sort of a time delay between the two processes.
Hi. So when I run the sub, the ExifTool opens. It starts processing some command lines. After running it closes. And then that's it. It doesn't create a CSV or import into the table.

The command prompt is supposed to invoke ExifTool with the command I input, which is:

Code:
exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv

Then save a CSV. Then import into the Access table. But isn't doing these two things.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:23
Joined
Oct 29, 2018
Messages
21,358
Hi. So when I run the sub, the ExifTool opens. It starts processing some command lines. After running it closes. And then that's it. It doesn't create a CSV or import into the table.

The command prompt is supposed to invoke ExifTool with the command I input, which is:

Code:
exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv

Then save a CSV. Then import into the Access table. But isn't doing these two things.
Okay, just a guess, but maybe try using ShellAndWait.

 

JamesGreg

New member
Local time
Tomorrow, 06:23
Joined
Feb 17, 2021
Messages
15
Just to update you @theDBguy

I've got the command line working and creating a CSV.

Code:
RunExif = Shell("cmd /k exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv

Forgot to add the "cmd /k". Now that I've got that step going, just not getting it to import into my table in Access. So you're suggesting ShellAndWait for this?

I'm currently getting this error message when it tries to find the file and path:

1613612033888.png
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 12:23
Joined
Oct 29, 2018
Messages
21,358
Just to update you @theDBguy

I've got the command line working and creating a CSV.

Code:
RunExif = Shell("cmd /k exiftool -G4 -ext TIF -r -csv C:\Users\jamesg\Desktop\TIF > C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv

Forgot to add the "cmd /k". Now that I've got that step going, just not getting it to import into my table in Access. So you're suggesting ShellAndWait for this?

I'm currently getting this error message when it tries to find the file and path:

View attachment 89304
You don't have to use ShellAndWait, I was just saying maybe you need to wait a little. For example, try this:

Run your Shell command separately. Then, after it's done, double check to make sure the CSV file exists. Now, run your TransferText command. This should tell you if the problem is the code or just a timing issue.
 

JamesGreg

New member
Local time
Tomorrow, 06:23
Joined
Feb 17, 2021
Messages
15
You don't have to use ShellAndWait, I was just saying maybe you need to wait a little. For example, try this:

Run your Shell command separately. Then, after it's done, double check to make sure the CSV file exists. Now, run your TransferText command. This should tell you if the problem is the code or just a timing issue.
Yep so the command worked. The CSV file was created. I checked in the specified folder location and it was successful. But then when it tries to do the TransferText part it gives me the error I mentioned above in my previous post. I did it like this:

Code:
DoCmd.TransferText , , "tblData", "C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv", True
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:23
Joined
Oct 29, 2018
Messages
21,358
Try using the wizard to see if you get the same error.
 

bastanu

AWF VIP
Local time
Today, 12:23
Joined
Apr 13, 2010
Messages
1,401
Hi James,
Glad to hear you got the csv creation part going for you. Any reason you don't supply all the arguments to the TransferText method?
TransferText (TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
TransferText , , "tblData", "C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv", True

Cheers,
Vlad
 

JamesGreg

New member
Local time
Tomorrow, 06:23
Joined
Feb 17, 2021
Messages
15
Hi James,
Glad to hear you got the csv creation part going for you. Any reason you don't supply all the arguments to the TransferText method?
TransferText (TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
TransferText , , "tblData", "C:\Users\jamesg\Desktop\TIF\allmetadata-V01.csv", True

Cheers,
Vlad
Hi Vlad,

To be honest I wasn't sure how to fill the rest of the arguments to the TransferText method 😂 I know that I can skip HTMLTableName because I was doing an acImportDelim but with SpecificationName and CodePage Identifiers I am proper lost!

Will leaving this out effect the import?
 

bastanu

AWF VIP
Local time
Today, 12:23
Joined
Apr 13, 2010
Messages
1,401
Of course they would, Specification Name is the most import. Usually you import manually for the first time (your csv file), check if data gets in OK, then you save the "tweaked" import specification and use that in the TransferText calls.
Cheers,
 

JamesGreg

New member
Local time
Tomorrow, 06:23
Joined
Feb 17, 2021
Messages
15
The import wizard: External Data > Import & Link > New Data Source > From File > Text File
That was good catch @theDBguy because when I ran the wizard it said some records (from the CSV) weren't able to append into the table. Now that's a bit of a dilemma. When I open my new CSV manually and save it as .xlsx, then try importing that new excel, then everything imports fine. So now I'm trying to figure out how to make the CSV run a 'Save As .xlsx' and import the new .xlsx file. If you guys got ideas I'm all ears.
 

bastanu

AWF VIP
Local time
Today, 12:23
Joined
Apr 13, 2010
Messages
1,401
You shoudn't have to! Look at the error message, inspect the "..ImportError" table(s) and see what the issues\constraints are that are preventing all csv records to be imported (maybe the target table has some text field required property set to yes or allow zero lenght set to no, etc...
 

Users who are viewing this thread

Top Bottom