Mass Conversion of Images in Externally Stored Containers; also, why is my FileMaker database still so big with externally stored containers?

[oilcan]

I maintain a FM database which has accumulated a ton of container data over the past 15 years. Currently our folder that contains all of our externally stored container data is clocking in at 7+ GB.

 

Lots of this container data are images saved in swollen formats, such as .bmp or really high resolution .jpg. Looking through the file sizes, I'm seeing tons of things that are 3+MB, and even more that are 1-2MB.

 

The images stored are very simple and do not need to be this big. What I'd like to do is mass convert all these images to a .png format. I'm pretty convinced We could make our data pile dramatically smaller if I could do this.

 

The question is, how to do it? If I were to run some batch processing program (suggestions welcome if you know of one!) on the folder containing all of our database images, would this work? Or would the file name change break the association? Do I need to run some sort of scripted export/convert/import process?

 

Any suggestions on how to approach this issue would be appreciated!

 

And a followup question: In our solution, our main database file is also 7+GB, and I'm pretty sure this shouldn't be the case with externally stored containers. I have saved a compacted copy of the database in the past and only shaved off like 500MB or so. It's a serious pain to move this 15GB of data around when the need arises, so if anybody has any ideas why our FM file is sized as if the containers are embedded, when they are most definitely not embedded, I'd love to hear your ideas, and if there's a way to fix it!

 

Thanks!

 

 

[comment]

Not to answer your main question, but:

 

1. If on OS X, look at GraphicConverter for doing the batch conversion.

 

2. What are your images images of? If they are photographs of real-life objects, you're very likely to see a dramatic increase in size by converting them from high-resolution JPEG to PNG.

[oilcan]

Not to answer your main question, but:

 

1. If on OS X, look at GraphicConverter for doing the batch conversion.

 

2. What are your images images of? If they are photographs of real-life objects, you're very likely to see a dramatic increase in size by converting them from high-resolution JPEG to PNG.

 

Thanks on the GraphicConverter suggestion! I'll look into it!

 

The images are of trace chromatograms and SDS-PAGE gels. Some are photographs, but they are all really simple images. I was under the impression that .png was ideal for images of this type. Please advise if I have been misled!

[comment]

Here's a good rule-of thumb:

 

JPEG uses a lossy encoding method specifically designed for photographic image data, which is typically dominated by soft, low-contrast transitions, and an amount of noise or similar irregular structures. Using PNG instead of a high-quality JPEG for such images would result in a large increase in filesize with negligible gain in quality. In contrast, when storing images that contain text, line art, or graphics – images with sharp transitions and large areas of solid color – the PNG format can compress image data more than JPEG can.

http://en.wikipedia.org/wiki/Portable_Network_Graphics#Comparison_with_other_file_formats

 

Ultimately, it would be best to run a few tests, using a sample of your own images.

[oilcan]

Here's a good rule-of thumb:

 

http://en.wikipedia.org/wiki/Portable_Network_Graphics#Comparison_with_other_file_formats

 

Ultimately, it would be best to run a few tests, using a sample of your own images.

Thanks for the advice. It sounds like .png would probably be beneficial, but I'll do some comparisons to see. 

 

My current plan is to develop two scripts, one to export all container data into a folder matching the record ID on the local machine, and another to re-import the data after I run a batch process on the images. I'll see how that goes.

 

In the mean time, I don't suppose you have any ideas about why my FM file is still as swollen as if the containers were embedded? I read that external container storage should drastically reduce file size, but that has never been the case for us. Could this be a file corruption issue? Anything I can try to remedy it?

[doughemi]

Did you Save a Copy as -> compacted copy(smaller)?

[oilcan]

Did you Save a Copy as -> compacted copy(smaller)?

I have tried that method. At the time I did it which was a couple years ago running FM12, it shaved about 500MB off the file size. The file was still nearly as big as the folder containing all container data. Perhaps I should try it again? /shrug

[comment]

I don't suppose you have any ideas about why my FM file is still as swollen

 

I don't think you gave us any information that would enable us to judge that. In general, your file size is mostly made up of [a] data, indexes, and [c] layout graphics. I would suggest you try to isolate the data first (e.g. by exporting it as Filemaker file/s).

[oilcan]

I don't think you gave us any information that would enable us to judge that. In general, your file size is mostly made up of [a] data, indexes, and [c] layout graphics. I would suggest you try to isolate the data first (e.g. by exporting it as Filemaker file/s).

Thanks comment. This sent me in a direction toward solving the problem I think.

 

I've begun suspecting that there's problems with some of my container data. So I've taken a particular table that has 3 containers, and tried exporting the id and all the containers into a FM file. I find that FM crashes after a certain point. So I then go into the exported file, omit all records that did not import an id, and go to the last record and get the id. I then do a find on that id in the live database, show all, and flip to the next record. This should show me the record that caused the import to crash. So, the container on this record appears to show an image, but when I right click to export the data I find 'Export Field Contents' is greyed out. This appears to be at least part of the problem?

 

I've found that I can fix the record by taking a screenshot of the image, and then loading the screenshot into the container instead of whatever thing was there. Export Field Content is no longer greyed, and that record exports fine on the next attempt. The problem is, there are a significant number of records that have this problem within one particular stretch of records, but I have no idea how many. Is there any container calculation I might use to isolate these records?

[comment]

when I right click to export the data I find 'Export Field Contents' is greyed out.

 

That is strange. My guess would be that the field contains a pasted image, but you still should be able to export it. Is this container field set up to store data externally? What do you see in a calculation field (result is Text) =

 

GetAsText ( Containerfield )

(make this field tall enough to see all possible values of a return-separated list).

 

 

 

 

I've found that I can fix the record by taking a screenshot of the image, and then loading the screenshot into the container instead of whatever thing was there.

 

I wouldn't hurry to adopt any particular cure before being sure of the diagnosis. Especially when the cure reduces the quality of the image as this one does.

[oilcan]

 

 

That is strange. My guess would be that the field contains a pasted image, but you still should be able to export it. Is this container field set up to store data externally? What do you see in a calculation field (result is Text) =

 

GetAsText ( Containerfield )

(make this field tall enough to see all possible values of a return-separated list).

 

 

 

 

 

I wouldn't hurry to adopt any particular cure before being sure of the diagnosis. Especially when the cure reduces the quality of the image as this one does.

 

 

Here is an example of what I get from one of these containers:

 

remote:TB15-283 72310.bmp

size:1350,519

JPEG:Purifications/file_one/TB15-283 72310.jpg

 

It looks completely normal in this respect it seems. But it just...won't export. And yes, this field is set to be stored externally.

 

I have some more clues I might share. This table is currently roughly 10500 records. A couple of years ago there was a schema redesign wherein two different tables were merged into this same table.

 

I've found there is a stretch of about 1200 or so records where the vast majority of these containers are like this, starting at around record 800. The first 2000 records in this table were imported from one specific table, so it appears that something may have gone wrong with the import process at around 800 records through the rest of this table, which did not return an error of any kind.

 

There's other interesting things happening here. Before I read your advice about adopting a particular cure before being sure of the diagnosis, I replaced a couple hundred of these items with their screenshot versions (honestly they're just trace chromatograms, you can hardly tell the difference in quality). Looking at the file size for the FM file, it appears to have gone down by about 600MB! From 6.9GB to 6.3GB. So it definitely seems this is at least part of the problem of my swollen FM file.

 

But it's going to suck to brute force all remaining 1000 of these records in this way. And there could be random ones here and there that I don't know about (I don't know how to find them yet, outside of this block of known problem records). I am preparing to try a few other things, such as doing a Replace Field Contents to another container with a different path, see if that does anything worthwhile to these (I already know exports fail). Or perhaps redoing the container path, to see if that does anything of note.

 

Finally, I've found some containers that appear to have 2 files in the GetAsText info?

 

remote:Picture 2.png

size:372,189

JPEG:Purifications/file_one/Picture 2_1.jpg

PNGf:Purifications/file_one/Picture 2_1.png

 

It's worth noting that these are *not* items that I've replaced with a screenshot. Doing that replaces the original entirely it seems. So where/why/how did that happen, and are there actually two files residing there somehow?

 

This just gets weirder and weirder!

 

In any case, I am happy to entertain any other suggestions as to what I might try with these, outside of trying to re-define the container path, doing a replace field contents to another container with a different path, or just brute force screenshotting all I can find (ultimately may still not work since there may be other records like this that I can't find). This is pretty uncharted territory for me, so please share if you have some ideas!

[oilcan]

other notes:

 

for the containers that won't export, it appears that the folder containing the files does actually contain the file, even though I can't export it.

 

Cutting the image and pasting it back does not change the problem; still can't export.

 

Dragging and dropping from the problem container to a test container with a different external path appears to duplicate the problem. The option to export contents remains grayed out in the new container.

 

Replace field contents does not work (in fact it appears to cause an eventual crash of FM much like exporting the containers does if you try to do it for the whole table).

 

Changing the container storage path does not make the images able to be exported either.

 

So far, the only thing that seems to do anything at all is to completely replace the image with a new one.

[oilcan]

In case anyone cares, after trying a huge number of things, I finally found something that worked. Not only did it work, it cut my FM file size down from 7ish GB to around 700MB!

With all the problems with container fields in this particular table, it seemed unlikely that I'd be able to track down and fix every issue manually. However, I did note that even these files where Export Field Contents were greyed out, they still had a file in the external folder. I found that if I copied the file from the external folder, then dropped it back into the container, it appeared to fix the problem. The item could be exported again. So I basically just copied the entire external folder, then wrote a script to replace all containers with the correct referenced file. I don't know why this works, but it works. Presumably something went wrong with a bunch of these containers when this table was being built I guess.

In any case, you can't argue with the results. An order of magnitude decrease in file size of my FM file surely indicates I was barking up the right tree on this one. I can now export without crashing, and all that good stuff as well.

[comment]

Good for you. To tell the truth, I don't understand the problem or the solution. Apparently, you had some kind of a glitch where the field had the image embedded - but still pointed to external storage?

[oilcan]

Good for you. To tell the truth, I don't understand the problem or the solution. Apparently, you had some kind of a glitch where the field had the image embedded - but still pointed to external storage?

​Yeah, that's the only thing I could figure. This table makes up the vast majority of our file size, so I had basically double the data consumption that I should have for the solution and its data set. I don't understand how or why our system got like that, and I don't fully understand why the solution worked, but I'm delighted I found a solution to the problem of our swollen FM file. Particularly one I could run as a script. I had nightmares about going through every single container, three per record, across 10,500 records, as a manual process to fix the problem.

[siroos12]

On 4/12/2015 at 0:05 PM, oilcan said:

In case anyone cares, after trying a huge number of things, I finally found something that worked. Not only did it work, it cut my FM file size down from 7ish GB to around 700MB!

With all the problems with container fields in this particular table, it seemed unlikely that I'd be able to track down and fix every issue manually. However, I did note that even these files where Export Field Contents were greyed out, they still had a file in the external folder. I found that if I copied the file from the external folder, then dropped it back into the container, it appeared to fix the problem. The item could be exported again. So I basically just copied the entire external folder, then wrote a script to replace all containers with the correct referenced file. I don't know why this works, but it works. Presumably something went wrong with a bunch of these containers when this table was being built I guess.

In any case, you can't argue with the results. An order of magnitude decrease in file size of my FM file surely indicates I was barking up the right tree on this one. I can now export without crashing, and all that good stuff as well.

Oilcan,

 

I am experiencing the same problem here, I read your posts and I just did not figured out how you could solve the file size problem. I am sitting with a 19 GB database file while all containers are set to store data externally. the external files folder is about 45 GB.

 

What I understood from your post is that you made sure that containers are set to store externally, then you wrote a script to loop through all the records and re insert the photos in containers? Did that solve the problem and made your file smaller?

 

Cheers,