Dear forum,



I need to come up with a way to import calender data from excel. My office is trying to coordinate calender data from other companies which they then can have the option to integrate into their own calenders. I have been googling around and the process seems, at first sight, to be way to laborious. Is there a way to (through initial programming or other ways) to create an excel sheet with data where a people can, with a click of a button, import the calender data in the targeted coloumn into their own outlook calender - without downloading any additional software? Non-clouding solutions are prefered.

Thank you in advance

There will be two parts to this solution: preparing the data in Excel and importing it into Outlook. Because of this, I suggest the user flags all required items rather than doing this one appointment at a time.

In Excel, you will need to arrange your data in a table, with individual appointments on each row. If the column headings (field names) are the same as those used by Outlook, they will not have to be mapped later. You can obtain Outlook's field names, and the default data settings for fields you don't use, by exporting a (small!) calendar from Outlook to Excel format.

You may want to write a simple macro in Excel to select the flagged records and massage the data into the correct output structure in a separate file (Excel 97-2003 .xls format).

Once this is done, your user can open Outlook and import the data in this file (the Import & Export command will first need to be added to their Outlook ribbon if using v 2007 or above). The Import process is a little laborious the first time, but after that, Outlook will remember the settings.

I hope this is enough to get you started. If you would like to have some help with the macro in Excel, please post a sample of the input data.

CJ seems to know what you're asking, but I don't get it. If his post works for you then feel free to ignore this question, but what (more exactly) are you trying to do?

The way I picture it, you're in Outlook's calendar, looking at an appointment and editing that appointment's notes, the body I mean. Now you want to <Alt-Tab> to Excel, point to a particular cell that had a date field in it, and click a button ... somewhere ... that causes the date in Excel to be pasted into the appointment notes?

That doesn't really make sense when you can just <Ctrl-Ins> and <Shift-Ins>, so I'm thinking you meant something more complicated. But what?

Hi Bob, thanks for the fast reply. I imported the Excel file to Outlook, which include the e-mail address in the RequiredAttendees field or column. Everything but that data imported correctly.

My assumption is that when that field is populated in the Excel spreadsheet it would also populate the cooresponding field in the Outlook meeting or appt - thus, the recipient would also get the appt notice.

I am not sure how to attached a sample file, but the RequiredAttendees filed does have a valid e-mail address in it.

Sorry for my earlier reply; you did say you were importing it back into Outlook after the mod, not into Excel, but I didn't notice.

But then, if the email address is getting into the CSV correctly, and then not making it into Outlook, it sounds like Excel is acting as you intended and this is a question that should be addressed to the Outlook forum instead.

I haven't seen the CSV yet, but here's a thought: When you export the appointment from Outlook to the CSV, before you change anything, are email addresses appearing in any special format? In quotes, perhaps? If you've triple-checked and you're sure the new email addresses are in the right column(s), then maybe it has to do with something like quote marks.

Hi Bob, I am agreeing that this is likelk an Outlook issue and will take it to the Outlook forum...

FYI, I should have mentioned previously, that I am at work and we are on an Exchange server-that may have something to do with it.

Anyway, I have tried the e-mail address in the traditional, name@domain.com form as well as the lastname, firstname form used here at work. And I have confirmed the use, or in this case the non-use of quotes.

I attached the test file.

Thanks

Dave

Attached Files

Test Import.xls (23.0 KB, 20 views)

Actually, I had something else in mind. You say you've confirmed it; but what I was wondering is how the CSV looks after you've exported an appointment from Outlook, just in case I can see anything unexpected there. It may be a clue to how you must modify the CSV before importing it back into Outlook.

Say, how did you export an appointment from Outlook's calendar, anyway? I just looked for it, and I don't see the way to do it.

Bob, I attached an original export, except for an e-mail address I needed to mask.

As for the how, in 2010, clock on FILE, then OPEN, then IMPORT - silly place to put an export function, but the first 3 option are export... Choose export to a file....

One other thing. this site does not allow for CSV uploads so I had to save the file as an xls.... Not sure if that conversion does anything to the data but thought you should know.

Attached Files

Test Export.xls (22.5 KB, 16 views)

Ok, so now that I know how (thanks for showing me—and I agree, a silly place for doing exports, but I never looked further than File anyway, didn't think of Open much less Import), I tried exporting my own appointments to CSV.

Now, I notice you said you "confirmed the use, or in this case the non-use of quotes". Does that mean that in the CSV that you're trying to import back into Outlook, the email addresses (in column 11) do not have quotes around them? I'm being so specific because when I export my own appointments to CSV, there are email addresses in column 11; besides the header row there are two records, and in both appointments the email values are in double quotes. In the first there are multiple comma-separated addresses and some of them are names only, eg Bob Bridges (so I presume Outlook feels free to look them up in my contacts book), but others are email addresses; and in the other appointment the only entry is ...with the quotes. (That's my work address.) I infer that the quotes are necessary at import time. But you say you confirmed the non-use of quotes, so I'm suspicious.

Crud - though you hit on something, but no luck.

I re-exported an appt with a required attendee. Openeded the CSV andthere are no quotes - single or double.

But, I went to my test import file, added double quotes and imported it. Still no luck. So I tried single quotes and no luck.

Have you attempted to import an appt with a required attendee?

I have asked this question over int he Outlook forum... so I guess I wait and see.

I wonder if there is some sort of policy or enterprize tyep rule preventing this...

Thanks

dave

This is getting more interesting. Send me an invitation (use my gmail address, robhbridges) of the type you're trying to set up, using both required and optional invitees; I'll accept it, then export it and experiment. We can compare notes.

Rob, I sent an invite to you. I exported it and none of the e-mail address have quotes.

I exported as a CSV and and XLS, both the same results....

Let me know what you see.

Dave

Ha! I think we have it!

I accepted the invitation, then exported it twice: once to CSV (Windows) and once to CSV (DOS). They seem identical in both CSV (viewing in Notepad) and when loaded into Excel. I wondered whether you were using a different export format than I, but they're the same, so that's one theory down.

In Excel, the required attendee (my email address) is in col 11, and the optional (your address) is in col 12. Neither one has quote marks, of course. But when I open them with Notepad, both addresses are in double quotes.

You haven't yet made the distinction between how they look in the CSV and how they look once they're read into Excel, so I suspect you haven't noticed it yet. If you already know this then don't take offense, but just in case you didn't: A CSV file is a plain-text "unload" (so to speak) of the contents of a workbook. When you double-click on a CSV document, Windows knows it's a kind of Excel workbook and by default (unless you change the association for the .csv extension) uses Excel to display it. But you can right click on a CSV, Select "Open with" and point it to Notepad; or you can open a Notepad window, then drag-and-drop the CSV icon to that window. This lets you look at the native text of the file. There you'll see something like this:That's the appointment you sent me, you see, except that I sanitized your email address in case you didn't want it seen here.

When I first brought up the issue of quotes, I was wondering whether the email addresses were quoted in your CSV. (They wouldn't show the quotes in Excel, of course; they're not needed there.) The fact that you never said "Notepad" while telling me what you were looking at in the CSV makes me suspicious that you didn't know what I just explained above. But I think we have the scoundrel now, so don't just read about it: I want you to go try it. Do it now: Use Notepad to look at the invitation that you exported from Outlook. (For future reference, I'll pretend this file is named "Outlook.csv". In Notepad, the email addresses have double quotes around them; confirm that this is true for you as well.

Next, double-click on the CSV normally and look at the invitation in Excel. The quotes around the text fields don't show up in Excel; that's normal.

Now: In Excel, use Save As to save that file in the CSV format. Make it a different file name than the one you imported, say "Excel.csv".

Last step: Look at Excel.csv in Notepad. Notice: The email addresses don't have quotes around them.

Voilà: We now see the problem. Well, I think we see the problem. When Outlook exports appointments, it puts the email addresses in quotes; my hypothesis is that it expects them to be in quotes during an import, too. But Excel sees no need to put quotes around character values that have no spaces or other special characters in them, so when you export your data from Excel to a CSV, the addresses aren't in quotes, and—I expect—that's causing Outlook not to understand what it sees.

You can test this by using Notepad to put double quotes around those addresses and then importing Excel.csv into Outlook. If it works correctly this time, you're on your way; you just gotta figure out how to get Excel to use quotes in those columns. I'm not sure Excel offers an easy way to do that, but it may; and if it doesn't, there are certainly ways to get around it.

Io, Io, Io, Pæan!

Bob, I am/was aware of the difference with Excell and Notepad and did open the test export file in notepad and saw the double quotes. I guess I just neve verbalized it... anyway, you are right they are there.

I'll go along with your theory and test, but just for discussion sake, I would agree totally, that the qoutes are needed, except for the fact that every other field is imported correctly.

I will also test, exporting in an Excel format and then re-importing from that file. I'm going to use the appt I sent you as the test, so if it work, you may see anotgher appt from me.

Hey one other thing... Export can also be found under FILE/OPTIONS/ADVANCED/EXPORT....

Thanks.

dave