301750 - the outlook csv importer should handle more languages

Status: RESOLVED WONTFIX

(Calendar :: Import and Export, defect)

Description

[Michiel van Leeuwen (email: mvl+moz@)]

The outlook csv importer and exporter can handle 2 languages at the moment.
Other languages should be added.
This used to be handled by the normal l10n system, using dtd and properties
files. The big problem is that your sunbird localization doesn't say anything
about the language outlook uses. So in my english sunbird, i couldn't import csv
files from my dutch outlook. The new system fixes that, but moves the localized
headers etc to a uncommon place, and atm only handles english and dutch.

Comment 1

[Robin Edrenius]

I'm not sure, but this should be a duplicate of bug 294242.
This bug has better information and stuff, so I'm not sure which one should be
marked as duplicate.

Comment 2

[Joey Minta]

(In reply to comment #1)
> I'm not sure, but this should be a duplicate of bug 294242.
> This bug has better information and stuff, so I'm not sure which one should be
> marked as duplicate.

Bug 292242 is about a problem with the way outlook exports data (giving two date
fields for each event).  This is about the way we try to import data.

Comment 3

[Stefan Sitter [:ssitter]]

Some things I noticed when playing around with the *.csv file exported from Outlook 2003 today and I just want to mention here:

When the outlook language setting is english I get the following headers:

  "Subject","Start Date","Start Time","End Date","End Time","All day event",
  "Reminder on/off","Reminder Date","Reminder Time","Meeting Organizer",
  "Required Attendees","Optional Attendees","Meeting Resources",
  "Billing Information","Categories","Description","Location","Mileage",
  "Priority","Private","Sensitivity","Show time as"

When the outlook language setting is german I get the following headers:

  "Betreff","Beginnt am","Beginnt um","Endet am","Endet um","Ganztägiges 
  Ereignis","Erinnerung Ein/Aus","Erinnerung am","Erinnerung um",
  "Besprechungsplanung","Erforderliche Teilnehmer","Optionale Teilnehmer",
  "Besprechungsressourcen","Abrechnungsinformationen","Beschreibung",
  "Kategorien","Ort","Priorität","Privat","Reisekilometer","Vertraulichkeit",
  "Zeitspanne zeigen als"

--> The headers are not only translated, they also appear in a different order.
    For example "Categories" and "Description" are swapped in the german
    headers.

--> The date and time format seems not linked to the language outlook uses. 
    (as currently expected in calOutlookCSVImportExport.js) 
    Instead of that it seems to be linked to the local systems settings for
    date and time format. For example I always had the date like "14.1.2006"
    in english and german csv file.

I'm sure this won't make it easier to write a proper outlook csv importer ...

Comment 4

[gekacheka]

See Bug 173562 comment 34 for how this was implemented in sunbird 0.2 and earlier.  In that case, the UILocale was also the locale used to match csv headers; if some did not match, you could match them manually through a dialog that contained a drop-down field selector for each column.

Comment 5

[Mike Shaver (:shaver -- probably not reading bugmail closely)]

Unfortunately, I do not have cycles to work on Calendar stuff these days (just as it's getting to the good part!), so I am a bad owner for these bugs.  To delete the tragically-large chunk of bugspam, search for gregorianabdication.

Comment 6

[Matthew (lilmatt) Willis]

Not going to make the 0.3 train.

Comment 7

[Martin Schröder [:mschroeder]]

*** Bug 294242 has been marked as a duplicate of this bug. ***

Comment 9

[:Hb]

Attachment: Patch v1

Adding de and fr locale, splitting en into enUS and enGB

The dateRe and timeRe patterns are univeral but still left in the locales

If the language of the headers is English the first Start Date is checked for an am/pm indicator. If this indicator is not found the locale is switch to enGB.

The function parseDateTime() handles now two digit years and doesn't break if no time was found.

The XUL service getApplicationLocale() is used to detect the output locale. enGB is used as default.

Comment 10

[Michiel van Leeuwen (email: mvl+moz@)]

Can outlook import csv files from foreign locales as easy as it can import native csv files? If so, I don't think we need to export in different locales. The less different file formats out there the better.

Comment 11

[:Hb]

Attachment: Patch Adding DE + FR and selecting output language from OS locale

Adding de and fr locale. This is a part (preparation) of the big solution, see bug 359083.

(In reply to comment #10)
> Can outlook import csv files from foreign locales as easy as it can import
> native csv files? If so, I don't think we need to export in different locales.
> The less different file formats out there the better.

We have to export in the locales which Outlook and Calendar can import. Currently Outlook can import localized (i.e. mm-dd-yyyy + 12 hrs AM/PM for en-US) and ISO 8601 data. But Calendar can only import data fitting to headers language.

Comment 12

[Daniel Boelzle [:dbo]]

mvl, you have time to review Hb's patch?

Comment 13

[Michiel van Leeuwen (email: mvl+moz@)]

Let me rephrase my question from comment 10:
If I export an English csv file with sb/ltn, can a non-English outlook import the file? Does is depend on the system locale?
If so, do we need to export in a language other then English? (maybe using iso date format instead of english)

Comment 14

[:Hb]

Short answer: It depends on the day-month-year order in the exported file. 

If we export in ISO format (YYYY-MM-DD) all known Outlook versions can import the files correct. I tested this under half a dozen OS locale settings  including en-US, en-GB, en-SA, de-DE, fr-FR. Also OpenOffice Calc and XL import these files.

Outlook imports as followed: 

1. Open the file and try to match the headers in the file against Outlooks build-in headers. The build-in headers depend on the language of the Outlook program. They do not change with the locale setting of the OS. 

2. Show a dialog to check the header mapping. The user can change the header vs. Outlook column mapping. 

3. Try to import the date values as YYYY-MM-DD if the first tuples has four numeric chars.

4. If the first tuple has less than 4 characters the OS locale is used for determining the day-month-year order.

Simulating this behaviour in SB/LG will get the best compatibility. Please see 
> https://bugzilla.mozilla.org/attachment.cgi?id=306957&action=diff#calOutlookCSVImportExport_1.19.js_sec2

We don't have "an English csv file", we have at least three variants. Please see bug 359083 comment 40. We must distinguish between headers and data. The headers are needed to get the column order. But they are no help to decide about the date format.

Outlook exports the dates always in a format defined by the OS locale.

It exports the headers in the language of the Outlook program if no other setting was made by the user. This setting is stored. It is the same setting from step 2 of the import.

We have four cases with the date format:

#  Export   Import   Format exp.  Imported as
-- -------  -------  -----------  -----------
1. Sunbird  Outlook  ISO          First choice OS independent

2. Sunbird  Sunbird  ISO          First choice OS independent

3. Outlook  Sunbird  OS locale    If 4-digit year is first in dates
                                  take first choice. If not take OS

4. Outlook  Outlook  OS locale    Same behaviour as case 2  


Time format (AM/PM or 24-hour) is no problem. Decide on the presence of a PM indicator for each single time value.

The patch for this bug is only a minor and intermediate part of the patch for bug 359083. Please review that patch (attachment 306957 [details] [diff] [review]) because it is completely free of locale based format settings. 

Comment 15

[:Hb]

Comment on attachment 307516 [details] [diff] [review]
Patch Adding DE + FR  and selecting output language from OS locale

This was small an intermediate solution which didn't make it into 0.8. 

For 0.9 we should take the big one in bug 359083.

Comment 16

[Michiel van Leeuwen (email: mvl+moz@)]

Why should we go for a huge patch when there is a small, reviewable(!) sub-set of that patch already split of?

Comment 17

[:Hb]

Comment on attachment 307516 [details] [diff] [review]
Patch Adding DE + FR  and selecting output language from OS locale

(In reply to comment #16)

I thought 0.8 was already completed. But this is not the case I saw.

Comment 19

[Michiel van Leeuwen (email: mvl+moz@)]

Comment on attachment 307516 [details] [diff] [review]
Patch Adding DE + FR  and selecting output language from OS locale

It's very unlikely that I will ever get around to reviewing this. Sorry.

Comment 21

[James Andrewartha]

From Bug 455845 "Currently the CSV parser supports only English (US) and Dutch outlook files.

You might have more luck by using a tool like FreeMiCal to export from Outlook
to the better supported iCalendar format."

Can this be added to the list of known issues in the release notes?

Comment 22

[Daniel Boelzle [:dbo]]

Hb, are you still still working on this? Is the patch ready for review?

Comment 23

[:Hb]

Yes. As Michiel recommended this patch is to be reviewed after the patch for bug 359083 is in the code.

Comment 24

[Martin Schröder [:mschroeder]]

The import/export code for Outlook CSV format has been removed in bug 1226851. Therefore resolving this bug report as WONTFIX.