Excel Export: Save Spreadsheets as CSV or Text Files Step‑by‑Step

Exporting Excel to CSV and TXT: A Quick Guide for Beginners

Converting Excel workbooks into CSV or TXT files is a common task for sharing data, importing into other tools, or preparing datasets for analysis. This guide walks through the basic methods in Excel, common settings to watch, and quick tips to avoid common pitfalls.

1. When to use CSV vs. TXT

• CSV (Comma-Separated Values): Best for tabular data where each cell maps to a column. Widely supported by databases, programming languages, and data tools.
• TXT (Plain text, often tab-delimited): Useful when you need a specific delimiter (tab, pipe |, semicolon) or want to preserve formatting that CSV rules might break.

2. Quick steps: Save a worksheet as CSV

1. Open the workbook and select the worksheet you want exported (Excel exports the active sheet only).
2. File → Save As.
3. Choose a folder, then from Save as type select CSV (Comma delimited) (.csv).
4. Click Save. If prompted about multiple sheets or features not supported, click OK or Yes to keep using the active sheet only.
5. Close the file in Excel (Excel may warn that the CSV format doesn’t support workbooks—save if needed).

1. Select the worksheet to export.
2. File → Save As.
3. From Save as type choose Text (Tab delimited) (.txt).
4. Click Save and confirm prompts about unsupported features.

4. Alternative delimiters (semicolon, pipe) — use Export or Save As with locale settings or VBA

• If you need a different delimiter (e.g., ; common in locales using comma as decimal separator), either:
  • Change your system list separator (Windows Control Panel → Region → Additional settings → List separator) temporarily and save as CSV; or
  • Use Excel’s Get & Transform (Power Query) to export with custom delimiters; or
  • Use a short VBA macro to write rows with a custom delimiter.

Example VBA (writes active sheet to pipe-delimited file):

vb
Copy CodeCopied
Sub ExportPipeDelimited() Dim fso As Object, ts As Object   Dim row As Range, cell As Range   Dim outLine As String   Set fso = CreateObject(“Scripting.FileSystemObject”)   Set ts = fso.CreateTextFile(ThisWorkbook.Path & “xport_pipe.txt”, True, False)   For Each row In ActiveSheet.UsedRange.Rows

 
outLine = "" For Each cell In row.Cells   outLine = outLine & cell.Text & "|" Next If Len(outLine) > 0 Then outLine = Left(outLine, Len(outLine) - 1) ts.WriteLine outLine 
Next   ts.Close   MsgBox “Export complete” End Sub 

5. Common issues and how to fix them

Dates and numbers change format: Set the column formatting before export or convert values to text (use TEXT function) to preserve display format.
Commas inside cells break CSV columns: Enclose fields in quotes or use a different delimiter.
Leading zeros dropped (e.g., ZIP codes): Format as Text before saving or prefix values with an apostrophe.
Unicode/character encoding problems: Use CSV UTF-8 (Comma delimited) (*.csv) in Save As for non-ASCII characters, or export via Power Query with specific encoding.
Multiple sheets: CSV/TXT save only the active sheet. For multiple sheets, save each separately or use a macro to loop through sheets.

6. Quick automation options

Record a macro while doing Save As to repeat the steps.
Power Query can transform and export data with more control and custom delimiters.
VBA scripts can batch-export all sheets, set encodings, and apply custom delimiters.

7. Checklist before exporting

 Active worksheet selected
 Numeric/date formats verified
 Delimiter chosen and consistent
 Encoding set (use UTF-8 if needed)
 Leading zeros preserved if required
 Multiple sheets handled if necessary

8. Example: Simple workflow

Verify formats and clean data (remove formulas if you want raw values).
Save a backup of the workbook.
Save As → choose CSV UTF-8 or Text (Tab delimited).
Open resulting file in a text editor to confirm delimiter and encoding.

This quick guide covers the essential methods and safeguards for exporting Excel data to CSV or TXT. For specific needs (batch exports, special delimiters, or encoding quirks), a short VBA script or Power Query step usually solves the problem.