CSV files are text files with data separated by commas, tabs, semicolons or other characters. Some services and program will provide CSV files directly, others will provde JSON files that may be converted to CSV (or in some cases imported directly to a spreadsheet. Please refer to the companion page JSON Extraction to CSV or Excel if a JSON file is the starting point.
By importing CSV files to Excel it becomes easier to clear, sort, reorganise, or analyse a dataset.
If or when doing this it is advised to remember: Always keep a backup of your original data file.
In order to import a CSV file into Excel follow these steps. This general recipe should ideally work, but due to different handling of data betwwen operative systems, some versions of Excel on MacOS may present difficulties in importing data to Excel. Options for handling such problems are addressed after the general recipe.
1) Create an Excel Spreadsheet with an appropriate name.
2) In the empty spreadsheet go to the "Data" tab, and select “From Text”.
3) In the path finder window find and select the CSV file you want to import.
4) The Text Import Wizard now opens. “Delimited” (by commas, etc.) should be marked by default. If not, then mark it. A preview of the data that will be imported will indicate whether experimentation with the settings should be tried, e.g. adding or changing to tabs as separators. This also applies to selection of character encoding ("File Origin") mentioned in point 6)
5) In some versions of Excel it may be necessary to tick a line in the import wizard saying “My data has headers.”
6) In “File Origin” scroll down and select “65001: Unicode (UTF-8)" if this is not already stated. This is to avoid unnecessary character mismatches in the import. Unicode (UTF-8) is the most widely used standard and should yield the best results in most cases, but in some cases selecting another character encoding may provide a better result.
7) Press “Load” when the data preview looks good. It will now be imported til Excel.
8) Save the resulting Excel spreadsheet.
Special directions for Mac users:
If a CSV file cannot be imported correctly by following the steps above, one may consider the following options:
a) If the final format must be Excel, e.g. for collaborative purposes, and provided that the CSV file does not have structural flaws which prevent if from being imported correctly to Excel at all, the simplest solution is if one can get a Windows user to help with converting the data.
b) Otherwise, try importing the CSV file to the built-in spreadsheet program in MacOS, Numbers. Please notice the advice on separators and character encoding in points 4) and 6) in the main recipe above.
c) It is possible to work with data in Numbers instead of Excel, but if needed the data can also be exported from Numbers to the Excel spreadsheet format. Select "File", "Export To", and "Excel". In the next window, select "One per sheet". A warning may appear that "Multiple hyperlinks in table cells aren't supported. Only the first links was retained". In the experiments conducted at CDMM we found no significant data loss, but if the message occurs then the Numbers and the Excel versions should be compared to determine if the needed and relevant data columns are intact. Since the data loss that may occur will affect hyperlinks such as links to images, the main data such as text in posts, and metadata such as number of shares, likes, or comments, should remain intact if the data was retrieved from social media. In such a case, the data loss that may occur during the export is only significant if the hyperlinks for specific elements are important.
Please notice: If you encounter problems related to the content not being placed in the right columns when opening the CSV file in Excel, an option is to try to open it in Google sheets. Usually this creates a correct file which can then be downloaded as an Excel file. Warning: Using a cloud based solution like Google Sheets may not be compliant with GDPR.
CDMM Tutorial video: Data Treatment in Excel
