1 Reply by Eleanor Knott

  • Eleanor Knott
  • Veteran
  • Offline
  • Registered: 21-01-2009
  • Posts: 93

Re: Importing from Office 2007

Has anyone had a problem importing data from a Microsoft Office 2007 spreadsheet (.xlsx)?

We received some data in an Office 2007 spreadsheet, which we converted to Office 2003 so that we could open it.  We then imported the data into Recorder 6.

We found that Recorder changed all of the dates - it knocked off 4 years and 1 day off each date, so 06/06/99 became 05/06/95, 01/07/98 became 30/06/94. 

I am assuming that this has something to do with how the different versions store dates.  If you convert a cell that is in date format to general format it comes up with a string of figures - in an Office 2003 spreadsheet 01/06/00 is 35216, but in a spreadsheet converted from Office 2007 01/06/00 is 36678, and 35216 corresponds to 31/05/00.

Have any of you techie people got a solution for this?  Or should we avoid data in Office 2007 format until Recorder 6 catches up?

Ellie
Eleanor Knott
Technical Co-ordinator
Devon Biodiversity Records Centre
C/o Exeter Central Library
Castle Street
Exeter
EX4 3PQ

Tel. (01392) 274128
http://www.dbrc.org.uk/

Eleanor Knott
Devon Biodiversity Records Centre

2 Reply by TonyP

  • TonyP
  • TonyP
  • Veteran
  • Offline
  • From: Somerset
  • Registered: 15-05-2006
  • Posts: 306

Re: Importing from Office 2007

Hi it sounds like they have changed the serial date start point. All dates up to now have been assuming that dates start at the first of Jan 1980 when the first PC's arrived. So positive numbers are after that date and negative before.

Or perhaps... when putting in a short year ie 10 instead of 2010 it assumes it's post 1920 [I think]. Perhaps they have moved things on for either or both. It would require a search on the MS support/Knowledge base to answer that without a copy of Excel 2007 onwards, which I don't have.

Off the top of my head that's a best guess.

Tony

Data Manger
Somerset Environmental Records Centre

TonyP's Website

3 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Importing from Office 2007

I found that the import wizard wouldn’t import Excel 2007 files when I was testing v6.14 last year. I requested that this be added to the list of future changes. Mary, could you please tell us whether it is on the list.

Re dates being out by about 4 years have you got the right date system selected? You can change the date system in Excel 2007 as follows:
1.    Click the Microsoft Office Button (top left), click Excel Options, and then click the Advanced category.
2.    Under the When calculating this workbook section, select the workbook that you want, and then select or clear the Use 1904 date system check box.

In Excel 2003, you change it using Tools – Options – Calculation and tick or untick 1904 date system.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

4 Reply by TonyP

  • TonyP
  • TonyP
  • Veteran
  • Offline
  • From: Somerset
  • Registered: 15-05-2006
  • Posts: 306

Re: Importing from Office 2007

That looks like the ticket Sally. Excel 2003 help says;

1904 date system Changes the starting date from which all dates are calculated from January 1, 1900, to January 2, 1904.

Which would account for 4 years and a day.

Data Manger
Somerset Environmental Records Centre

TonyP's Website

5 Reply by Eleanor Knott

  • Eleanor Knott
  • Veteran
  • Offline
  • Registered: 21-01-2009
  • Posts: 93

Re: Importing from Office 2007

Sounds sensible.  We don't actually have Excel 2007 - we have to convert the files to Excel 2003. 

I checked the date system in the spreadsheet, and it is using the 1904 date system.  However, when I changed it, all of the dates changed as well :( 

I need to find a way to change the dates to the 1900 system without the dates changing themselves in the process.

Ellie

Eleanor Knott
Devon Biodiversity Records Centre

6 Reply by TonyP

  • TonyP
  • TonyP
  • Veteran
  • Offline
  • From: Somerset
  • Registered: 15-05-2006
  • Posts: 306

Re: Importing from Office 2007

This might just help under correcting shifted dates;
http://support.microsoft.com/kb/214330

Data Manger
Somerset Environmental Records Centre

TonyP's Website

7 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Importing from Office 2007

If I'm having date problems I usually convert the date to a text string, either by exporting to a CSV or importing the data into an Access database and setting the date field to text. The DigDB Excel add-in can also convert a column of dates to text. The Recorder import wizard handles dates-as-text just fine.

Try asking whoever is supplying the 2007 file to Save As into Tab Delimited or CSV to see if that helps. Or they could just save into xls format.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

8 Reply by Mary Campling

  • Mary Campling
  • Administrator
  • Offline
  • Registered: 16-04-2010
  • Posts: 101

Re: Importing from Office 2007

Hi Sally, yes importing from Excel 2007 is on the feature wish list.

9 Reply by LBacon

  • LBacon
  • Veteran
  • Offline
  • Registered: 12-09-2006
  • Posts: 80

Re: Importing from Office 2007

Its also a MAC thing... the 1904 date system was always the MAC default, but most people now use the 1900 date system, except one or two who have not caught on to these odd modern ways!  I had some data last year from two Mac users.  Even if you "change the date system" when displayed in Excel, the actual five-digit string behind it, from which the date is interpreted, is wrong.  I got round it by calculating how many days I had to add on, then converting the date to the five-digit character string, adding the required amount to it, and re-displaying as dates.

I only came across it as I had a date-restricted survey and initially could not work out why the records would not import despite being the "correct " date - if 4 years are removed, it was too early for the survey!


TonyP wrote:

That looks like the ticket Sally. Excel 2003 help says;

1904 date system Changes the starting date from which all dates are calculated from January 1, 1900, to January 2, 1904.

Which would account for 4 years and a day.

Louise
CPBRC (and a mac user in my spare time!)