Importing Excel 2010 data into Outlook Calendar 2010

Status
Not open for further replies.

Trapper

Senior Member
Outlook version
Outlook 2010 64 bit
Email Account
POP3
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.
 

Attachments

  • Calendar import problem 10 03 14.JPG
    Calendar import problem 10 03 14.JPG
    92.1 KB · Views: 859
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.
 
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.
 
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.
 
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.
 

Attachments

  • RBC Diary 2014 2 .zip
    7.1 KB · Views: 484
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.
 

Attachments

  • RBC Diary 2014-dp.zip
    7.7 KB · Views: 520
:)
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.
 
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.
 
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.
 

Attachments

  • RBC Error Snip.JPG
    RBC Error Snip.JPG
    54.1 KB · Views: 698
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.

named-range.png


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.
 
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.
 
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.
 
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.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
K Importing appointment body from excel in outlook 2010 Using Outlook 1
J Importing to the correct calendar from Excel 2010 to Outlook 2010 Outlook VBA and Custom Forms 2
Diane Poremsky Importing Lists from Excel to Outlook Using Outlook 0
B Importing Text from Excel to Outlook 2013 Calender Using Outlook 0
M importing email address list from excel Using Outlook 6
G Importing Excel contact data base into Outlook Using Outlook 1
T Importing data from Excel - address fields BCM (Business Contact Manager) 1
M having trouble importing excel data into outlook contacts BCM (Business Contact Manager) 1
J importing contacts from excel BCM (Business Contact Manager) 3
R importing excel spread sheet to bcm BCM (Business Contact Manager) 6
K Importing from Excel with HTML Formatting Outlook VBA and Custom Forms 1
HarvMan Importing PST into IMAP account Using Outlook 12
CWM550 Importing " Old Skool" Data Using Outlook 0
P Importing other e-mail accounts into Outlook Using Outlook 1
M How to setup outlook after importing old account information - Entering email account info creates with "(1)" after the account! Using Outlook 1
J Importing N2K from a different Exchange Server Using Outlook 1
J Outlook 2010 Changing events in Outlook calendar via opening file, importing CSV Using Outlook 0
O Importing Mbox - anyone out there having experience with this tool... Using Outlook 2
D Importing Outlook Categories from another domain (Exchange 2016/Outlook 2016) Using Outlook 4
B Importing business card into Outlook for SIgnature Using Outlook 1
S Importing Ribbons Not Saved Using Outlook 7
V importing appointments to non-default calendar? Using Outlook 1
CWM550 Importing from Eudora Update Using Outlook 5
CWM550 Importing from Eudora Using Outlook 7
N Importing Google contacts from CSV file removes recipient names in autocomplete list Using Outlook 0
S Importing emails to contacts Using Outlook 3
Z Importing PST files from Outlook 07 to Outlook mac Preview Using Outlook 1
Z how to make OL (2007) to use custom contact form when importing / opening .VCF? Using Outlook 1
P Importing Thunderbird email to outlook Using Outlook 2
A Importing too many emails from Gmail Using Outlook 2
S Possible to link to ics file without importing? Using Outlook 4
L Importing Opportunities in to BCM 2013 BCM (Business Contact Manager) 0
K New to BCM - trouble importing contacts & accounts BCM (Business Contact Manager) 3
LarryH Importing csv file to custom form/fields? Using Outlook 3
W Importing .pst from Outlook 2010 (Win7) to Outlook 2013 (Win8.1) Using Outlook 2
V Importing Rules in Outlook 2013 Using Outlook 2
D Importing custom fields into custom form in BCM BCM (Business Contact Manager) 1
V Problem not having Contacts folder after importing emails from Yahoo. Using Outlook 0
O Importing contacts from CSV, comma delimited Using Outlook 7
B importing Outlook from a backup on an external hard drive Using Outlook 5
M Trouble Importing folders from Outlook 2007 to Outlook 2013 Using Outlook 12
wallisellener BCM 2013 importing contacts from CSV file BCM (Business Contact Manager) 8
T BCM not importing address BCM (Business Contact Manager) 1
T Importing OE6 e-mail to Outlook via Store Folder Using Outlook 9
A Trouble importing data to radio buttons Business Contact Manager 2010 (BCM) Using Outlook 2
H Importing Windows Live Mail Contact into Outlook 2010 Using Outlook 0
T Limit on importing Inbox mail from OE6 Using Outlook 16
Commodore Importing RSS on another computer (and feed locations) Using Outlook 8
O Importing pst, archive pst and address not working Using Outlook 17
N lose conditional formatting when importing to Outlook 2010 Using Outlook 2

Similar threads

Back
Top