Open tab-delimited TXT or CSV file in Excel or Open Office Calc

Use these "how to" instructions to open any delimited format text file as a spreadsheet. The instructions below are for tab-delimited files, but can be adapted for comma-separated or pipe-separated files easily by simply choosing the appropriate delimiter/separator.

Some feeds use comma as the 'delimiter' or 'separator'. You can find your feed's settings on the "Manage Feed > Advanced Settings" page.

Open Office Calc 3.3.0

1. Start Open Office Calc
2. Go to File > Open.
3. In the Files of type: drop-down menu, choose Text CSV (*.csv; *.txt), which is in the section that begins with ODF Spreadsheet (*.ods)
4. Select the file and click Open
5. In the Text Import dialog, choose the following settings and click OK
   • Character set: Unicode (UTF-8)
   • Language: Default - English (USA)
   • From row: 1
   • Separator options: Separated by Tab
   • Text delimiter: " (double-quote)
   • Quoted field as text: checked
   • Detect special numbers: unchecked
6. The settings above will be remembered by OpenOffice, so the next time you open the file, you can simply review the settings and click OK.

Excel 2002 SP3 and Excel 2007

Tip: Typical TXT and CSV files can be opened rapidly using this shortcut. Open Excel and close the blank workbook. Drag and drop the file onto the dark gray working area of Excel. Excel will automatically detect the format and load the spreadsheet instantly. If this doesn't work, use the full instructions below.

1. Start Excel
2. Excel 2003:
   1. Go to File > Open
   2. In the Files of type: drop-down menu, choose Text Files (*.prn; *.txt; *.csv)
   3. Select the file and click Open
3. Excel 2007:
   1. Select the Data tab.
   2. In the Get External Data group, select From Text.
   3. Once the Import Text File dialog box appears, choose the file and click Open.
4. In the Text Import Wizard - Step 1 of 3 dialog, choose the following settings and click Next
   • Original data type: Delimited
   • Start import at row: 1
   • File origin: 437 : OEM United States
   Note, in later versions of Excel, you may be able to choose UTF-8, which is the actual 'origin' or 'encoding' of the file.
5. In the Text Import Wizard - Step 2 of 3 dialog, choose the following settings and click Next
   • Delimiters: Tab
   • Treat consecutive delimiters as one: checked
   • Text qualifier: " (double-quote)
6. In the Text Import Wizard - Step 3 of 3 dialog, leave the settings as-is and click "Finish".

Row limits and field length limits

• Starting with version 3.3, the OpenOffice Calc row limit is 1,048,576 rows. Prior versions were limited to 65,536 rows.
• Excel 2002 is limited to 64K rows, but later versions of Excel support 1M rows.
• Excel 2002 through 2010 have a field length limit of 32,767 characters. If you have a caption or other field that exceeds this length, the extra data will flow to the next cells or rows, and the file will appear to be corrupt.

Excel and UTF-8 Encoding

Our system produces feeds with UTF-8 character encoding. If the file contains any high value UTF-8 characters, then Excel may have problems opening the file. You may see fewer lines than expected.

You can check your text file for UTF-8 characters by using the Count Character Occurrences Tool. High value characters are those with a decimal code value of 128 or higher. Some non-printable characters under 32 may also cause issues.

To solve the problem, you can force Excel to open the file with UTF-8 encoding.