OTRS generated CSV and Unicode issue

It’s a while for now that we have problems with OTRS generated CSV files and Unicode. We are conducting an OTRS implementation in a Persian (Farsi) context. OTRS’s Stats (reports) module can generate CSV as output. There is 2 problems here. First, delimiter character used is Semicolon (;) not Comma (,) and second, because of our Unicode environment, CSV file format is UTF8. Following screenshot shows such a CSV file opened directly in MS Excel 2007, all of fields is just located in one column and Persian (Farsi) text are showed incorrectly as some meaningless characters:

A CSV file opened directly in MS Excel 2007


While searching for the solution in OTRS mailing lists, I found a good and very simple solution. Resolving the issue is possible by importing (not opening directly) the CSV with some considerations. All necessary steps are illustrated in the following.

1. Select “Get External Data, From Text” from Data menu and select desired CSV file:

Menu: Data->From Text->Get External Data From Text


2. A wizard opens. In the first step select “65001 : Unicode (UTF-8)” as “File Origin” and click “Next”:



3. In the second step select “Semicolon” instead of “Comma” and click “Next”:



4. In the 3rd step of wizard click “Finish” and in the “Import Data” window click “OK”. After this you will see your correct data in correct cell (each field in a separate column) and correct language (Persian text not garbage one) in MS Excel 2007:

Our CSV showed and interpreted in corrected way


Please consider that all of these instructions are applicable in MS Excel 2003 too.

تجربه ای جدید در جمع آوری اطلاعات

چطور می شه یک سری اطلاعات یکسان را از طریق چند نفر جمع آوری کرد؟

4 تا فایل اکسل به چند نفر داده می شه و اونا هم قراره اطلاعات کاغذی و قدیمی رو وارد این اکسل ها کنند. یک سری اطلاعات بین این اکسل ها مشترک هستند مثل نام دوره ها، نام گروه ها و … حالا مشکل اینه که اونا ممکنه یک سری اطلاعات مشترک رو جداگانه وارد کنند و شماره ردیف های مختلفی بهش نسبت بدن. در واقع ممکنه یک دیتای یکسان رو بیش از یک نفر وارد کنند. مشکل اصلی ترکیب (merge) این دیتا ها با همدیگست. در واقع این خودش ی replication هستش.