How do I open a text file containing separate fields in a spreadsheet? What if there are different field separators?
Importing date from a .CSV file can have variable results because there are so many different ways the data can be separated. This is especially so if you ignore the settings in the Text Import dialog, and just hope for the best: that is a recipe for trouble later on, even if it apparently works. To avoid variable results, take care with the settings in the Text Import Dialog. If you do not know how the data is separated in your file, open it in a text editor and analyse it first.
If your ASCII file is not already named with a .csv or .txt extension, rename it (preferably as .csv).
- Go to ▸ .
- In the File Type box, select: Text CSV ( *.csv; *.txt). (Note : This choice is near the bottom of the list).
- Navigate to your ASCII file and select it.
- Click on Open.
- In the Text import dialog that opens, select the correct Separator options for the columns:
- 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
- If you are importing dates:
- 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
- If some or all text fields are surrounded by quotes, select the appropriate Text Delimiter
- 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 ASCII files into a single sheet, follow this procedure:
- Open an empty spreadsheet.
- Go to ▸ .
- Select the File Type that you want (Text CSV .txt, dbase, etc.)
- Search for the file you wish to open.
- Click on the Insert button to close the window.
- Set the parameter in the Text import window as shown above.
- Click OK.
- 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.