On this page
1. Prepare to import and clean CSV in LibreOffice Calc #
Save this as workshop-orders.csv in a plain-text editor, using UTF-8:
order_id,customer,quantity,unit_price
00041," Amina Patel ",2,12.50
00042,"Luis Romero",1,8.00
00043,"Jo Chen ",3,4.25These are invented sample records. The prices are plain decimal amounts, with no currency conversion or tax calculation. Keep the original file separate from the working copy. That gives you a reliable reference if an import setting changes a value.
Decide what each column means before opening it. An order ID identifies a record, so 00041 should stay exactly that text. Quantity and price are numbers. A spreadsheet can't infer that distinction safely from every CSV.
2. Open the file and inspect Text Import #
Choose File → Open, select the CSV and enable Edit filter settings if that option is shown. Click Open. The CSV documentation confirms this route to the Text Import dialog.
Choose Unicode (UTF-8) as the character set for the example. In the separator options, select Separated by and Comma. Use a double quote as the string delimiter.
The preview should show four columns, not a single long line. The name with spaces should remain inside one field. If the preview is wrong, change the separators here before accepting the import. A semicolon-separated export needs semicolons even if its filename ends in .csv.
3. Set column types before accepting the import #
Select the order_id column in the preview and set Column type → Text. Leave quantity and unit price numeric, using the matching language setting for the file's decimal convention. For this example, English (USA) matches the dot in 12.50.
The Text Import help explains column types and number-language interpretation. You can leave customer names as text. Keep Evaluate formulas off for a data import when you want expressions beginning with = treated as data.
Click OK. Inspect the first three order IDs: 00041, 00042 and 00043 should still have five characters. Check that the quantity and unit-price columns contain numbers you can calculate with. Cell alignment alone isn't a complete check; use a small formula in the next step.
4. Clean the names in a separate column #
Add clean_customer in E1. In E2, enter =TRIM(B2) and fill the formula down to E4. Calc's text-function documentation describes TRIM's handling of ordinary spaces.
The first result should be Amina Patel, with the excess ordinary spaces removed. Keeping the imported column beside the cleaned result lets you compare them. Don't erase the source column before deciding whether the cleanup is appropriate for the data.
TRIM isn't a universal repair for all invisible characters. If a copied name still contains an odd gap, inspect the original source or use a targeted replacement for the character involved. A blanket replacement can also damage spaces that are meant to be there.
5. Calculate a check total #
Add line_total in F1. In F2, enter =C2*D2, then fill down to F4. The expected arithmetic for this original example is:
| Order ID | Quantity | Unit price | Line total |
|---|---|---|---|
| 00041 | 2 | 12.50 | 25.00 |
| 00042 | 1 | 8.00 | 8.00 |
| 00043 | 3 | 4.25 | 12.75 |
Use =SUM(F2:F4) below the values. The expected total is 45.75.
If the total is zero or an error appears, check the imported number types and decimal language. Formatting text to look like currency doesn't necessarily turn it into a number. Reimporting the source with the correct settings is often cleaner than repairing converted values afterward.
6. Save the working sheet and check a CSV export #
Choose File → Save As and save a working copy as an ODS workbook. That preserves the added formulas and spreadsheet structure.
When another application needs cleaned CSV, prepare the current sheet with the columns it expects, choose File → Save As, select Text CSV and enable Edit filter settings. Set the character set and separators for the receiving system. Give this export a new name, such as workshop-orders-clean.csv.
CSV contains the current sheet's plain records; it doesn't preserve an entire workbook with its formulas, multiple sheets and formatting. Open the exported file as plain text to inspect the header and values, then reimport it with the intended types. The IDs and total values should survive in the representation your receiving application expects.
Known CSV problems and their fixes #
Product codes become scientific notation
An April/May 2025 Calc discussion describes values such as 01e17 being interpreted as scientific notation. Replies point to explicit text column types and the Detect scientific notation option. Set identifier columns to Text during import; don't rely only on disabling date detection.
Imported amounts won't sum
A May 2025 bank-CSV thread describes an AutoSum returning zero for imported amounts. The discussion separates text interpretation from displayed number formatting. Check the import language and actual cell type, then reimport or convert the amount column carefully before calculating.
Troubleshooting table #
| Problem | Cause to inspect | Fix |
|---|---|---|
| Everything appears in column A | Incorrect field separator | Reopen Text Import and select the actual comma, semicolon or tab separator |
| IDs lose zeros | Numeric interpretation | Reimport the original with that column set to Text |
| Accented names look wrong | Wrong character set | Choose the encoding used by the export, UTF-8 for this sample |
| Prices won't sum | Text values or a mismatched decimal convention | Use the matching import language and numeric type |
| A code containing E changes | Scientific-notation detection | Set the code column to Text |
| Extra sheets vanish from CSV | CSV represents one current sheet | Keep an ODS working file and export sheets separately as needed |
Variations for another file #
Import a semicolon-separated export
Use the preview to select Semicolon instead of Comma. This matters for files that use decimal commas in numeric fields. The separator and the number language are separate decisions; set both to match the source.
Preserve dates as written
If a receiving system needs the original date string, import that column as Text. If you need date calculations, choose an explicit date type matching the source order, such as YMD. Check an ambiguous date against the original record before using it in a report.
Clean an ongoing export
Keep the original export, the working workbook and the outbound CSV as separate files. Record the separator, character set and identifier columns in the project's instructions. A remembered import setting from another file shouldn't silently decide the next job.
Completion checklist #
- The preview has the expected columns and records.
- Identifiers retain their leading zeros and original spelling.
- Names have the intended cleanup, with the source available for comparison.
- Numeric columns calculate and the sample total is
45.75. - An ODS working copy preserves formulas.
- The CSV export has been reopened and checked against the receiving system's requirements.
Next steps #
Compare free spreadsheet apps for other workflows, or read LibreOffice Calc vs ONLYOFFICE for file exchange and collaboration choices. The LibreOffice listing, ONLYOFFICE listing and LibreOffice alternatives provide the related tools.
Featured illustration: Webindexly.
