When opening your data file in Excel and all the data is in the first cell, Excel did not open it using the "," as the delimiter. To have Excel open the file using the "," as the delimiter try the following.
- Open Excel and open an blank spreadsheet
- Go to 'Data' tab
- Select 'From Text' (third option from left) and select the .CSV file you want to import.
- Select "Delimited" for the Data type and check "My data has headers" and click next
- Note: If you have extended characters that do not display correctly change the File Origin to Unicode (UTF-8)
- Select Comma for the Delimiter and ensure that data is now showing in columns and click next.
- Column data format should be set as General
- Click Finish
- Put the data in either an existing worksheet or a new worksheet and click OK
- Highlight all the data in the first cell of Excel and copy it
- Open a plain text editor - Notepad on Windows or TextEdit for OSX
- Paste the data into the editor and save as a plain text file (.txt)
- In Excel open the .txt file you just saved.
- Select Delimited for the file type and select My data has headers and click Next
- Set the Delimiter to Comma and text qualifer to " and click Next
- View your data in the columns and ensure it is correct and click Finish
You file should open in Excel with all the data in the correct column
Change the default delimiter when opening a .csv in Excel on Windows
To change the separator in all .csv text files do the following:
- In Microsoft Windows, click the Start button, and then click Control Panel.
- Open the Regional and Language Options dialog box.
- Do one of the following:
- Click Change date, time, or number formats in the Regional Options, and then click Additional Settings.
- Type a new separator in the List separator box or select the , as the default separator.
- Click OK twice.
Content provided by Microsoft https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba