Frequently asked questions - Calc

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Documentation Home
• Official Publications
• Other Resources
• FAQ
• HowTo
• Macros
• Docs Development

• FAQ Home
• General
• Writer
• Calc
• Impress
• Draw
• Math
• Base

How do I open a text file containing separate fields in a spreadsheet? What if there are different field separators?

Importing data from a text file can have variable results depending on the parameters selected in the Text Import dialog. If you do not know how the data is encoded or separated in your file, double check the preview of columns shown at the botton of the dialog.

If your text file does not have .csv or .txt extension, rename it (preferably as .csv).

1. Go to File ▸ Open.
2. In the File Type box, select: Text CSV ( *.csv; *.txt). (Note: This choice is near the bottom of the list).
3. Navigate to your text file and select it.
4. Click on Open.
5. In the Text import dialog select the correct parameters:
   • Character Set (legacy ASCII is nowadays rather to be UTF-8)
   • if there is a separator: choose Separated by and then enter the appropriate separator(s); and deselect any other separators (LibreOffice tends to select several by default)
   • if the columns are fixed width: select Fixed Width and position the column dividers in the preview
6. If you are importing dates or timestamps:
   • select Detect Special Numbers
   • at the top of the relevant columns click on standard and select Date instead
   • Select D/M/Y OR M/D/Y as appropriate
7. If some or all text fields are surrounded by quotes, select the appropriate Text Delimiter
8. If you are importing a data file from a stock control system, it is highly probable that the decimal separator will be a dot. In this case set the language to English (US).

If you want to combine several text files into a single sheet, follow this procedure:

1. Open an empty spreadsheet.
2. Go to Sheet ▸ Insert Sheet from file.
3. Select the File Type that you want (Text CSV .txt, dbase, etc.)
4. Search for the file you wish to open.
5. Click on the Insert button to close the window.
6. Set the parameter in the Text import window as shown above.
7. Click OK.
8. Repeat the procedure from 2) for the other files.

Note: If you check the Link box, the sheet will be linked when you open your spreadsheet, and you will be asked if you wish to update the link. Just click OK.