I have downloaded some Facebook data from DiscoverText to an Excel CSV formatted file (.csv) but the data is unreadable or unintelligible. How can I make the spreadsheet data look correct?
In some cases the data in Microsoft Excel CSV formatted files look good, however, the problem lies with the UTF-8 encoding. In other cases the spreadsheet may appear to be empty or the data may appear to be missing.
Excel does not to open up CSV files in UTF-8 encoding by default. For Excel 2013 and later, the default encoding for opening CSV files has changed (especially if you just double-click the file to open it). There can be an issue if the data is encoded in a language such as Arabic, Spanish or Farsi. Therefore, this might cause the issue that you are seeing.
The problem is not in the export itself, but rather how Excel opens up the file.
Use the following instructions when opening the .csv files to ensure that the correct encoding gets into Excel:
- Open an empty (new) spreadsheet in Excel.
- Click Data -> From Text ("Get External Data" area).
- Browse to your CSV file and click Import. It should launch the wizard.
- (Wizard step 1): On the first page of the wizard, choose the data type Delimited, and (most importantly!) set the File Origin dropdown to 65001 : Unicode (UTF-8).
- Check the My data has headers checkbox.
- Click Next >.
- (Wizard step 2): On the second page of the wizard, select Comma as the delimiter type and uncheck Tab. Keep the Text qualifier as " (a double quote character).
- Click Next >.
- (Wizard step 3): On the third page, if you want, you can set the columns to general vs text vs date, but this is usually not necessary. Click Finish.