Vague date formats when importing data

Have you read this FAQ: Adding records with date ranges or 'vague dates' ?

I don't usually have vague date mode enabled, but I switched it on and entered a casual record in training mode with a 1906 date, and it worked okay for me.

Yes Barry, I checked settings whilst in training mode and vague date mode was enabled.  It works ok if i just enter a record manually, but not when importing records from csv file. Im obviously doing summut wrong ;-/ 

In the importing guide (appendix 2) it states that DateType Y would require e.g. StartDate 01/01/1906 EndDate 31/12/1906, and DateType YY would require e.g. StartDate 01/01/1880 EndDate 31/12/1884. Which would seem to imply you cannot use vague dates in ranges - they are only allowed in a single Date column (i.e. with no DateType). The example spreadsheet in appendix 3 would appear to confirm this - although the instructions never make any of this explicit, so I could be wrong. I suppose a little bit of experimentation might help clarify things...

This is getting complicated,  Might be easier to just send the data to the NBN.   What I have found after trying again is that the two records with just a single year ie 1875 and 1906 when I format these cells to a custom date format rather than number 1875 becomes 17/2/1905, and 1906 becomes 20/03/1905 and these are the dates coming through onto the irecord record list.   Guess Ill try using Srart Date 01/01/1875 and End Date 31/12/1875 and see if that then brings through 1875 when uploaded.  For now though, its time to get something to eat.  Thanks Barry, Ill keep trying.

I give in.  Table shows extract from my sample data with headings as recommended with the date as I require it in the first column, then the start/end dates and dat type.  Last column is what I get after uploading.  

 

Your row values look ambiguous because they include entires in the Date columns and the Date Start/End/Type columns.

I did a little experimentation myself, and found it relatively easy to get coherent results. It's possible to use both date entry methods in the same table, just so long there's never values in both sets of columns within each row. Here's my example values and results:

PS: after trying the above example table again, I found it did not produce consistent results. Specifically, the first row with a date of 1906 produced 20/03/1905 on the second run. So it looks like there may be an issue with interpreting vague values in the Date column. This seems to happen even if the other date columns are removed.

I would therefore recommend not entering vague values in the Date column - or perhaps even removing that column altogether. The Date type/start/end method seems much more reliable, as it provides an explicit format for the dates.

Hmmm, thats a pity as I thought you'd cracked it then Barry.   The trouble is, of the 200 ish records, only a handful are a year range (eg 1880-1884)  or specific date (eg 21/6/18887).  The vast majoiry are just the year only as they are based on the date of the journal/book that the record was gleened from.  I have added comments for each record stating the source journal/book and that the date is approx and maybe either the date of the publication OR where given the specific date of the field observation,   The 21/6/1887 date is specific as the record relates to the species being seen on the night of Queen Victoria's silver jubliee when the author saw the species whilst returning form lighting a jubilee beacon. 

I'll have a play with the method you tried above and see if i can get it to work.

I think others have had date issues when importing data too.  If you go to explore all records and then sort by date in reverse order..... there are lots of records from the 11th Century which I have a feeling is down to an uploading error.

Perhaps you should just bite the bullet and re-format your csv file. If you have some basic coding skills this would be quite easy to do. But if not, it could also be done using a text-editor with an advanced search and replace facility (such as NotePad++).

For example, to replace single years (19th-20th century), you could search for e.g. ,(1[89]\d\d), and replace with ,Y,01/01/\1,31/12/\1,; and to replace a year-range you could search for e.g. ,(1[89]\d\d)-(1[89]\d\d), and replace with ,YY,01/01/\1,31/12/\2,. Obviously, all the other date-formats used in your csv file would need to be handled in a similar way as well, so that you ended up with three date columns in every row. Then you would finally need to change the column headers by replacing Date with "Date type","Date start","Date end".

Needless to say, before you attempt anything like this, make sure you create a backup of the original csv file first!

Or someone from Irecord could just make the system work. I used to like iRecord but these days Im finding it more and more frustrating what with the loss of OS Maps and lack of grid lines or scael on the open street maps.  

Ive tried a few things including saving file as xls format instead of csv.  Nothing I do works.  Shame i hadnt tried this before the recent feedback form that verifiers were sent.

Thanks for trying though Barry, its appreciated.

Well, we shouldn't forget that iRecord is provided to users for free, and it's also perhaps a little unreasonable to expect that there will always be enough resources available to fix every bug as quickly as we might like. I daresay iRecord struggles to find sufficient funding at the best of times, so it must be quite challenging to keep the site running in the current economic climate.

Personally, I would be happy that there's a fairly simple work-around available for the current problem and just make do with that. It's only a one-off task, so I wouldn't begrudge spending twenty minutes or so to reformat a CSV file. However, I have some experience with computer programming, so I suppose using a little short-term hackery to get what I want is meat and drink to me ...