Management Systems

3 Sep 2010

How to convert CSV file to Excel spreadsheet format?

User reported that when the data exported with chinese character from SMS and save directly into csv file, the chinese character will display as unreadable character. This is because csv  file must be saved with UTF-8 encoding in order to display chinese character correctly.

To resolve this issue, user is required to convert the exported data into excel spreadsheet format where the UTF-8 encoding can be set for displaying chinese character correctly

To do it, please follow the steps as guided below on how to convert csv file to spreadsheet format.

Step 1  – Save the exported data directly to CSV file

To begin with, user is required to export data from SMS via Exports SMS Profile to Excel Format option. A pop up window will be displayed as shown in sample screen below and please click on Save button and proceed to next screen.

Save

The following screen is displayed for user to save the file to its desired location.  The defaulted file name is “sms”. Click on save button to save the file successfully and proceed to next step.

file name

Step 2:  Convert CSV to Excel

To begin with, please open Microsoft Office Excel (version 2003 & above) first and go to menu bar Data-> Import External Data-> Import Data. Import

Select the directory of the file and click “Open” button once the file (eg “sms”) is located and proceed to next screen.

Select data Source

Please make sure that the “File origin” must be in 65001 : Unicode (UTF-8) only.  Click on the next button to continue to Step 2 Text Import Wizard.

Step 1

Please click on the checkbox “comma” in order to tabulate the fields and click “Next ” button to proceed to Step 3 Text Import Wizard.

Step 2

Please make sure to change “Column Data Format” as “Text” on “ICNO“, “PHONE NO” and “HP PHONE” fields in order to display correct information. Click ”Finish” button and proceed to next screen.

step 3

Click ok for data import to Excel
Step 4

Example Screen after cobvert CSV to XML Format.

Excel

User may save the Excel file.
2010-09-03_110553

RSS feed for comments on this post