Learning Center What is a mineral?The most common minerals on earth Information for Educators Mindat Articles The Elements The Rock H. Currier Digital Library Geologic Time

Minerals by Properties Minerals by Chemistry Advanced Locality Search Random Mineral Random Locality Search by minID Localities Near Me Search Articles Search Glossary More Search Options

The Mindat Manual Add a New Photo Rate Photos Locality Edit Report Coordinate Completion Report Add Glossary Item

Mining Companies Statistics Users Mineral Museums Clubs & Organizations Mineral Shows & Events The Mindat Directory Device Settings The Mineral Quiz

Photo Search Photo Galleries Search by Color New Photos Today New Photos Yesterday Members' Photo Galleries Past Photo of the Day Gallery Photography

Discussions

💬 Home 🔎 Search 📅 Latest

╳Discussions

💬 Home 🔎 Search 📅 Latest

Groups

Recent Images in Discussions

Improving Mindat.orgPersonal Collection Catalog

28th Feb 2014 20:33 UTCEugene & Sharon Cisneros Expert

I believe that I have found a quirk in the export feature of the personal catalog. Because on this side of the pond, we are a bit behind the rest of the world, Sharon insists on using inches and fractions of inches in the size fields. There may be a formatting issue with format for those fields for export, because some of the dimensions show up in the exported file as dates! For example, 3/4 translates to 4-mar in the exported file. See the example below. Is there anything that can be done to remedy this issue? If necessary, Sharon will switch to metric, as I have already tried to persuade her that it is more universal anyway.

There is one more thing that I would like to suggest. Some people will have more than one collection that they would like to catalog separately. As far as I can tell, you can only have one catalog per account. Is that correct, or am I missing something?

If we can find solutions to the issues above, I think that the catalog feature will be perfect for us. It is very easy to link to Excel sheets that only collect the data that you wish to print out or archive.

Thanks,

Gene

28th Feb 2014 22:43 UTCDan Fountain

Gene,

It's a peculiarity of Excel. Right click on the column header, left click on "Format cells...", left click on the "Number" tab, and select either "Number" (if you want decimal units) or "Fraction" if you want fractions. If you want to keep the cm figures as decimals and the inches as fractions, you'll have to format individual cells.

Hope this helps.

Dan

28th Feb 2014 23:12 UTCEugene & Sharon Cisneros Expert

Dan,

I tried that before I posted. If I try to reformat the dates, or fractions, the results are strange. Here are the results.

Numbers format, 0 decimal places

Fractions format, xx/xx

I believe that the problem is that you cannot reformat a date to a number or fraction. The export file has to be set to number at the source before export.

In a fresh Excel sheet, I just tried entering 3/8 into a cell and it displays as 8-Mar. When I reformat it to number, I get 41706.

Gene

28th Feb 2014 23:35 UTCDan Fountain

Just when I thought I knew something about Excel...

It looks like Excel interprets fractions as Julian ~~Date~~ Day Numbers - only trouble is, one chart I found online says that 41706 is the JDN for March 6, 2014, not March 8.

I guess saying it's a peculiarity of Excel is more accurate than I realized.:-S

Dan

1st Mar 2014 00:11 UTCEugene & Sharon Cisneros Expert

First problem solved, though a pain in the neck. Yeah, I thought I knew a lot about Excel too, having used it for many years. However, there are dark little nooks and crannies that I have never explored before.

When you input numbers with a - or / into Excel, it wants to make that a date by default. So, you have to change the format before it is written into the cells. That means that you can't open the .csv file directly. You have to import it with the Text Import Wizard and change the format before it is written into the cells of your new sheet. I just learned that and it works. Here is the result.

Unfortunately, this is a lot of hoops to jump through and takes time. Unless Jolyon can change the formatting at his end, this will be the only solution that I can think of...right now. A macro or visual basic solution can most likely speed up the process and I will attack that as time allows. At least I know that I can successfully import and reformat as desired now.

That leaves the problem of more than one collection catalog per account.... Jolyon?

Gene

1st Mar 2014 00:17 UTCEugene & Sharon Cisneros Expert

Looking at the sample above, I think that Sharon should switch to metric and save me a whole lot of trouble and also make the spread sheet look cleaner. Just look at that mess of fractions compared to the first entry in cm. Yikes, it makes my eyes go funny!

Gene

1st Mar 2014 01:02 UTCKeith Compton 🌟 Manager

Dan

Maybe therein lies a problem - perhaps its not that Excel interprets fractions as Julian Date Numbers - perhaps its that in Mindat we only use Jolyon numbers !!!!

Sorry J

Cheers

Keith

1st Mar 2014 07:21 UTCDon Windeler

Keith -- nice call! :-D

D.

1st Mar 2014 15:39 UTCDavid Von Bargen Manager

"That leaves the problem of more than one collection catalog per account"

There are a couple of possible workarounds for that. If you are not using the storage location, that could also be used as a subcollection (or collection identifier). You could also prefix your catalog number to indicate the collection.

1st Mar 2014 15:57 UTCAlfredo Petrov Manager

It would also be easy enough to just register a second account to handle a separate collection.

1st Mar 2014 16:49 UTCEugene & Sharon Cisneros Expert

David,

That's certainly a possibility. An issue with that is that you can't export the individual collection lists separately. You would have to export the file with all collections and then sort them out. Still, workable...

Alfredo,

This would currently be the simplest and most direct way to keep the data separate. However, a simple provision for more than one collection per account would perhaps be the best solution.

Jolyon,

Is his something that could be easily be implemented? Perhaps it could go on the to do list...

Gene

1st Mar 2014 17:05 UTCDavid Von Bargen Manager

My feeling was that the export function was there for either making a backup of the data that you could be sure was safely being stored in case of a disaster or if you wanted to get it out of mindat and into another program that you wanted to use instead.

If you want to view certain specific groups of specimens in your collection, improvements in the program search capabilities would likely be more important,

1st Mar 2014 18:24 UTCEugene & Sharon Cisneros Expert

David,

The export function may have been implemented for backup of data, but since the database has no prevision for making labels or printing sorted data, the export file can be used to good advantage. For example, I can sort on any field in the reformatted export file to make reports that can actually be printed out. Can you imagine anyone wanting printed data these days, except for dinosaurs like me? From the exported data, I can also print labels of my own design and with the information that I want, not what the collection database allows me to print (if it had a print function). I think that being able to import the collection database into one's Excel or Access solves all the problems of making the database perform to each individual user's needs. To that end, it is important that the export feature works properly and I believe that it does, but for one small glitch, which may be easy to fix. If not, there is a simple workaround that I mentioned earlier.

I realize that all users may not have the ability to manipulate the data in Excel, but everyone who does could make their templates available to others.

Just my thoughts, based upon my own needs....

Cheers,

Gene

31st Mar 2014 23:38 UTCJolyon Ralph Founder

Sorry, I stumbled onto this thread a bit late.

Storing dimensions in fractions of inches is allowed by the system but not really the smartest thing to do because it does prevent future searching or sorting by size (show me all cuprites less than 1 inch in my collection).

Looking into the problem briefly (I don't have Microsoft Excel on any of my machines so I can't test it directly) - it does seem that Excel automatically formats the cell as a date and you can't override it. There is no way I can change the export format to prevent this that wouldn't have other unwanted side effects.

There may be a work around and that is to use the Data > Import External Data > Import Data option to import the csv into the existing spreadsheet. Assuming your columns are set up with the right types of values it might work better at importing correctly.

Alternatively you may want to look at using a different program such as OpenOffice (although that may well have similar issues).

1st Apr 2014 01:52 UTCEugene & Sharon Cisneros Expert

Jolyon,

I understand that you can't do anything at your end to fix the date formatting issue. You may have missed it, but I mentioned how to import and correctly format the data a couple of posts earlier.

What hasn't been answered is if the capability to have more than one collection database is possible. This would be a welcome addition for us and perhaps for others as well.

Gene

3rd Apr 2014 16:44 UTCJolyon Ralph Founder

Gene

Sorry, we're not going to be able to support more than one collection per account - for two distinct collection catalogues you'll need to register a second mindat.org user account.

Jolyon

Mindat.org is an outreach project of the Hudson Institute of Mineralogy, a 501(c)(3) not-for-profit organization.
Copyright © mindat.org and the Hudson Institute of Mineralogy 1993-2024, except where stated. Most political location boundaries are © OpenStreetMap contributors. Mindat.org relies on the contributions of thousands of members and supporters. Founded in 2000 by Jolyon Ralph.
Privacy Policy - Terms & Conditions - Contact Us / DMCA issues - Report a bug/vulnerability Current server date and time: April 25, 2024 16:55:14

Go to top of page