Importing Excel 2010 data into Outlook Calendar 2010

[Trapper]

What am I doing wrong?

I did not realise that I could import an Excel database into Outlook Calendar (Which I can then export to my phone, I think).

Please see attached Snip showing where I am stuck.

My Excel datasheet looks like this:
Date Venue Time Opponents Dress Rinks
21-Apr H 2.30 President v Captain Greys

26-Apr A 2.30 Batchwood Whites 4MR
27-Apr H 2.30 North Mymms Whites 4MR

The date shown are actually written as 21/04/2014 and not 21-Apr as above.

I think I am getting it wrong on the 'Fields' as when I copy left to right I am not sure where to link my headings of Date, Venue, Time, Opponents, Dress and Rinks against those listed as no matter how I try the 'OK' button remains greyed out.

Thanking you in advance to any suggestions!!

Just realized month shown in snip is March but nothing shows in April or any of the other 75 odd games from April to September.

Alan.

 

[MiniMe]

Drag Date to the mapped from column for start date, Time to start time, Venue = location, opponent = Subject.

Dress and rinks are the problem - one can go into the message (or body) field.

The other option is to change the CSV so the column header names match outlook - Start Date, Start Time, etc.

The date format shouldn't be a problem, but you may need to use the American format of mm/dd/yyyy. Oh, and make sure they are dates and not formatted as text.

 

[Trapper]

Thanks for the suggestions, played around a bit but am still not able to import all the games. I can import to the Outlook 2010 calender games that do not have a start time or rink info, so it will import this:
01/06/2014 H Pairs Handicap Competition Greys
02/06/2014 H Pairs Handicap Competition Greys

But will not import this:
16/06/2014 H 14.30 Club Afternoon Greys
17/06/2014 A 14.15 Oxhey Red (Vets) Greys 2T

The above is a CSV Excel file but of course the times shown are 2.3 for 2.30pm so I changed the dates to the 24hr format - 14.30 as above in case that was causing a problem, but it still did not import.

I have also tried to import the information when saved as an Excel 97~2003 xls file but came across an error as I needed to enter a named range but after looking at the help information I was even more lost! In layman's terms what is and how should I name the range? Do I have to enter Date into the box top the left of the Formula note in Excel for every cell from A1:A75 individually? And Location into B1:B75 and so forth, seems rather a lot of work!!

Is there a way to enter Custom fields or must you stick with the default field map?

Regards
Alan.

 

[Diane Poremsky]

Can you upload the file and I'll take a look at it? I think the date and time fields might be the problem, but will know after I look at the file. My reason is this: 2.3 for 2.30pm It should have the formatted time, as in 2:30 PM.

Named ranges are created by selecting a group of cells then typing a name in the field to the left of the address field in excel - it contains the current cell if there is no named range - but CSV or XLS shouldn't make a difference, unless importing from XLS affects the time format. AFAIK, it uses the same format you see in the file (just like a CSV would), not the underlying serial time.

 

[Trapper]

Hi Diane.

They say you're never too old to learn, how true! Sometimes it's fun learning as well!!

With any luck there should be an uploaded file attached.

Good luck
Regards
Alan.

 

[Diane Poremsky]

Ok, it imports for me but all events are all day events, because of the times. You need to use 2:30 PM format.

Also, without an end time, the default length of the appointment is based on your time scale, so you may want want to add an End Time column. The end time can be calculated in Excel for you - =C2+0.083333333 is 2 hours after the start time (which is in the C column).

My version of the file is attached. With the updated field names, it should map perfectly to the Outlook fields.

 

[Trapper]

Hi Diane.
Thank you for the new file, I have not tried it yet but am sure there will be no problems. If I entered 2.30PM in the start time would this work just the same as 14.30.00?

Games are usually around 3 hours, can I enter 5.30PM as an end time or is there another =C2+0.0??????? I should use?

One thought, does it matter if the cells are General or Text or ? when they are imported, or do the cells need to be any specific type?

Many thanks

Alan.

 

[Diane Poremsky]

3 hours = .125 in the formula. You'll want to use time format for the time field, date for date, and it doesn't matter for the other fields. Excel uses the formats, and what you see on screen is what Outlook uses - if you save as CSV, what you see onscreen is what gets saved.

14.30.00 won't work - it needs to be in a standard time format that outlook understands.

 

[Trapper]

Hi Diane.
Ohps, still a problem when I try to import into Calendar as error below:

All cells in row 1 are field names except D1, maybe this is causing the problem? I tried to name the cell End Time but that failed and reverted to only showing D1 again.

I do not know if it is possible but I would like to remove the seconds from the timings 2:30 PM instead of 2:30:00 PM

Thanks for your help

Alan.

 

[Diane Poremsky]

no, named ranges is an excel feature that is required if importing xls files. In Excel 2013, select the range you want to import (which is all of the rows and columns for the games) then Formula tab > Create from Selection in the Defined Named group. Or type a name in the box that says A1, next to the address bar and press enter. Then, when you are in say H2, you can select the name from the dropdown in the box and the entire table is selected once again.

Because you need to remember to change the range used by the name or enter a new name when you add rows or columns to the spreadsheet, I recommend using CSV, where all data is imported. But this is great if you have additional rows you don't want to import.

BTW, the steps should be the same in Excel 2010 and probably 2007. It's also available in 2003 and older, but I'm not sure offhand where the toolbar command is. Typing in the field to the right of the address bar works in all versions.

 

[Trapper]

Hi Diane.
I think I will have to re-think this idea, I have now imported the file into Outlook using CSV format. I had thought that all the information would be visible under the date in question but this is not the case.

The start time and end times plus subject are shown but category and description are not, to see these I need to open up the entry in Calendar where Category is shown separately above subject and the description is shown in the body of the file which looks like an Outlook Email.

I really need to see the whole information in one go, especially if I am going to sync the Calendar with my phone.

I did try the 'Create from Selection' option you mentioned, that brought up a new box when I tried to import the file with more import options, seemingly asking to import each field and asking where to place these 'custom' fields. At that point I backed out and went for the CSV option.

My thanks for doing a very good job trying to help me on this, perhaps as my wife often says, I am, beyond help!

If you have any other ideas please let me know

Alan.

 

[Diane Poremsky]

Because the category and description are fairly short, I'd merge them with the subject field.

You can use a formula in Excel to merge them (=D2 & " " & E2 & " " & F2) , then copy and paste special as values, to replace the existing subject in the excel sheet. Then Import once more.

 

[Trapper]

Hi Diane.
Brilliant, I wondered if I could merge the cells, it took me a while to realise that the (= above should be =( easy mistake to make but after a little playing with the entries it worked great and I have imported the new file. I tried to import a couple into my phone and they also worked fine so I will now import the rest.

I used the CSV format for the file.

Once again you have come to my rescue and once again all I can say is 'Thank you'

Regards
Alan.