Help Center

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

Sarah Bacchus
Sarah Bacchus
  • Updated

Use these steps to open any delimited format text file as a spreadsheet. In this process, you will tell your spreadsheet program to format your UPCs or GTINs as text instead of as numbers. This prevents the program from converting the number into scientific notation.

 

Excel 2002 SP3 and Excel 2007

  1. Start Excel
    • Excel 2003:
      • Go to File > Open
      • In the Files of type: drop-down menu, choose Text Files (*.prn; *.txt; *.csv)
      • Select the file and click Open
    • Excel 2007:
      • Select the Data tab.
      • In the “Get External Data” group, select From Text or From Text/CSV.
      • Once the Import Text File dialog box appears, choose the file and click Open.
  2. 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.
  3. 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)
  4. In the Text Import Wizard - Step 3 of 3 dialog, leave the settings as-is and click "Finish".

 

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 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.

Was this article helpful?

14 out of 46 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.