How do I open a comma separated text File in Excel?

Keep a copy of your data files in a plain text format, with comma or tab delimiters. I generally use comma-delimited (CSV) files.

So a spreadsheet like this:

Would become a plain text file like this:

id,sex,glucose,insulin,triglyc
101,Male,134.1,0.60,273.4
102,Female,120.0,1.18,243.6
103,Male,124.8,1.23,297.6
104,Male,83.1,1.16,142.4
105,Male,105.2,0.73,215.7

It’s not pretty to look at, but you can open the file in Excel and it will look pretty again. More importantly, this sort of non-proprietary file format does not and never will require any sort of special software.

(And most of the time, the first thing I do with an Excel file is convert it into a set of CSV files, one per worksheet, because they’re easier to handle in code.)

If any of the cells in your data include commas, Excel will put double-quotes around the contents of each cell when it’s saved in CSV format. That requires slightly more finesse to deal with, but it’s generally not a concern.

To save an Excel file as a comma-delimited file:

• From the menu bar, File → Save As
• Next to “Format:”, click the drop-down menu and select “Comma Separated Values (CSV)”
• Click “Save”
• Excel will say something like, “This workbook contains features that will not work…”. Ignore that and click “Continue”.
• Quit Excel. It will ask you, “Do you want to save the changes you made?” Click “Don’t Save”, because you just saved them. (Excel really doesn’t want you to use a format other than its own.)

Note that there’s also an option to save as “Tab Delimited Text”. Many people prefer that. (I don’t like tab delimiters so much, myself, as multiple adjacent tab characters are hard to discern by eye.)

Also note that, if your Excel file did contain critical features that will not work when saved as a plain text file, that’s a problem. For your primary data file, keep things simple.

Personally, I prefer to use a vertical bar (|) as a delimiter between fields, because the data itself will seldom contain a vertical bar. But you can’t get that from Excel.

Next up: Other things to avoid.

(Previous: Use data validation to avoid data entry mistakes.)

Optimizely Campaign creates CSVStands for "comma-separated values"; tabular data in a plain text file separated by the comma character. files in UTF-8 without BOM. Microsoft Excel incorrectly interprets the format as ISO-88591. To correctly open CSV files in Excel, perform the following steps:

1. Open a blank Excel workbook.
2. In the Data tab, click Get Data > From File > From Text/CSV.
3. Select the file to open and click Import.
4. In the File origin area, select 65001: Unicode (UTF-8) and Semicolon in the Delimiters area.
5. Click Load.

Evaluating data with Microsoft Excel

The Excel format allows rich formatting, calculations, and output in pivot tables and in a chart.

To use your own Excel template, download the default template, customize it then upload it. This Excel template contains the report data in the first worksheet grouped by the selected groupings. The second worksheet contains a copy of the data without sum fields. Use this worksheet to reference data you want to display in other tables or charts (for example, pivot tables).

In general, the following standards apply when formatting the Excel templates:

• Supported file formats. Optimizely Campaign supports the Excel '97 format (file suffix .xls) and Excel 2007 (file suffix .xlsx). However, Optimizely does not guarantee support of functions and formatting of these two formats.
• Data export and data format. Exported data is exported into the first table with sum fields and second worksheet table without sum fields. Do not change these worksheets; otherwise, the correct export of the data fails. The format of the header page is completely adopted, but not the content. The data formats of the first data row (row 2 in the Excel worksheet) are adopted. Thus, values can be formatted and the formatting can be changed.
• Evaluations. You can perform evaluations on the second and subsequent worksheets. Because an unlimited number of rows may be added when executing the report, chart data, pivot tables and formulas must be designed for a very large number of data rows. This especially applies to reports without time restriction.
• Pivot tables. Automatic updates must be turned on startup. You find this option under PivotTable Options > Data > Refresh data when opening the file.

Upon request, Optimizely creates Excel templates with pivot tables, charts and using the layout of your choice. Contact customer support.

How do I open a Delimited text file in Excel?

How do you open a comma separated file?

How do I convert a TXT file to Excel?

How do I always open a CSV file in Excel with columns?