How to Open CSV Files in Excel on Mac

Comma separated files (CSV) can sometime be difficult to open in MS excel on MacOs. The reason CSV files behave unexpectedly is because they sometimes contain carriage return (line breaks). And in such case if you open the CSV file directly on excel on Mac, you will encounter unexpected data formatting. If you face such an issue where the data is not being displayed as it should be, this article is going to explain the possible options to make it work. 

There are 2 different ways to handle such situation. 

1. Use Other CSV View App
2. Edit the CSV file in a text editor

1. Use Other CSV View App:

This is the easiest way to deal such issue. You can install CSView  from App store. This app is made for CSV files specifically and can display such files without any formatting issue. Just open the app store, search for CSView and install.

You can also download it here: https://apps.apple.com/us/app/csview/id1167193104?mt=12

After you finish installing CSView app, open it and click on the file option in the top left of your screen. Open the CSV file yo want and you'll find everything just fine.

2. Edit the CSV File in a Text Editor: 

As the main reason behind this issue is the line breaks (carriage return), you can always edit the CSV files and remove those line breaks. To do this, you have to right click on the file and open with any text editor (e.g. TextEdit). You will find a lot of data separated with commas, You will also see some data that is not separated with commas but the line breaks.

In the above screenshot most of the data is on same line separated with commas, but the domain whois records contain line breaks (Every record field on new line). Such line breaks make trouble viewing data properly in the excel.   All you have to do it, is to remove the line breaks and bring that data on one line. (Do not add any comma just bring them on same line).

Once you are done removing all the line breaks from the CSV file, save it. Now you can simply open it with MS excel without going through all the steps in method 1. Your data will be in correct form. 

If you are using MacOs TextEdit tool to edit this, you would need to do it manually line by line. But if you have some other text editor, you can just use find and replace option. In find field put '\n' and replace it with nothing. It will remove all the line breaks. Save the file and open with excel.