How to Open a CSV File in Excel (Office 365) Properly

If you have exported your dataset from Communalytic as a CSV file, and now wish to view and analyze it further in Excel, follow the steps below to properly open it.

Important: Do not simply double click on the CSV file to open it in Excel, it will likely not be able to properly display emojis and other special characters. It may also corrupt some of the fields that store unique identifiers for posts and users (as these fields are usually represented as a long sequence of digits) which Excel will try to interpret as integers and will likely fail and corrupt your data.

Here are the Proper Steps to Import a CSV Into Excel: 

  1. Start by opening Excel and creating a blank spreadsheet  (File->New)
  2. Under the [Data] main menu, click the [From Text/CSV] icon in the toolbar.
  1. After selecting a CSV file you want to open, you will see the following popup window. Choose [Unicode (UTF-8)] in the [File Origin] dropdown menu, and click [Transform Data].
  1. Next, you will see another window called [Power Query Editor]. In this editor, for any column that stores either post ID or user ID, we need to change their data type to [Text]. To do so, right click on the column header of the selected column, and in the context menu, select [Change Type]-> [Text] (as shown below). 

When prompted to either [Replace current] or [Add new step], select [Replace current].

If the process has completed successfully, the column should now properly display unique tweet id as shown below:

Repeat this process for all columns with “id” in their name such as “user_id”, “in_reply_to_userid”, “in_reply_to_tweet_id”. 

  1. The final step is to click [File] in the main menu and then [Close & Load]
  1. To avoid repeating this process when opening the same dataset in the future, we recommend saving it as an Excel file (xlsx).