What Is CSV
CSV (Comma-Separated Values) is a comma-separated values file. It is the simplest tabular data format—each line is a record, and fields are separated by commas.
Name,Age,City
Tom,25,Beijing
Jerry,30,Shanghai
CSV is a universal format for data exchange. Excel, databases, and programming languages can all read and write CSV.
CSV Parsing Rules
Basic Structure
- One record per line
- Fields separated by a delimiter (usually a comma)
- The first line is usually the header
- Newlines separate records
Fields Containing Commas
When a field contains a comma, wrap it in double quotes:
Name,Description
Tom,"Hello, world"
Without wrapping, it is treated as two fields.
Fields Containing Double Quotes
When a field contains double quotes, escape them with two double quotes:
Name,Description
Tom,"He said ""Hello"""
Fields Containing Newlines
When a field contains a newline, wrap it in double quotes:
Name,Description
Tom,"Line one
Line two"
Delimiter Variants
- Comma: Standard CSV
- Semicolon: Common in Europe (Excel European edition defaults to this)
- Tab: TSV (Tab-Separated Values)
- Pipe: Some systems use |
Confirm the delimiter before converting.
Data Cleaning
Trim Whitespace
- Leading/trailing spaces in fields: trim
- Empty lines: remove
- BOM header: remove
Unify Formats
- Dates: unify to YYYY-MM-DD
- Numbers: unify decimal point (no thousands separators)
- Booleans: unify to true/false or 0/1
- Encoding: unify to UTF-8
Handle Missing Values
- Empty fields: fill with a default value or mark as null
- Entire row missing: delete or fill in
- Key field missing: flag as error
Deduplication
- Fully duplicate rows: delete
- Duplicate primary keys: keep the latest or merge
Common Problems
Chinese Character Garbling
CSV encoding issues are the most common. Excel defaults to GBK (Chinese edition); other tools use UTF-8.
Solutions:
- When exporting from Excel, choose "CSV UTF-8"
- Save as UTF-8 using a text editor
- Use a conversion tool that supports automatic encoding detection
Delimiter Misidentified
CSV uses commas, but Excel European edition defaults to semicolons. Opening a CSV file may cram everything into one column.
Solutions:
- Open with a text editor to confirm the delimiter
- Specify the delimiter when importing in Excel (Data → Text to Columns)
BOM Header
UTF-8 CSV exported from Excel may contain a BOM (\uFEFF). There is an invisible character before the first field name.
Solution: Remove the BOM with a text editor, or let the conversion tool handle it automatically.
Numbers Becoming Scientific Notation
Long numbers (ID card numbers, order numbers) become scientific notation in Excel (1.23E+18).
Solutions:
- In Excel, set the column to "Text" format before pasting
- Add a single quote before the number in CSV ('123456)
- Specify the column as text when importing
Messy Date Formats
Date formats differ by region:
- China: 2024/1/1
- US: 1/1/2024
- Europe: 1.1.2024
Solution: Unify to ISO 8601 (2024-01-01).
Inconsistent Newlines
Windows uses \r\n, Mac/Linux uses \n. Mixing them causes parsing errors.
Solution: Unify newlines.
CSV Conversion
CSV to JSON
Programs handle JSON more easily than CSV. Use the CSV to JSON tool for one-click conversion.
CSV to Excel
Opening a CSV file with Excel gives you a table. But watch out for encoding and delimiter issues.
Excel to CSV
Save as CSV from Excel. Note:
- Only the active worksheet is saved
- Formulas become values
- Formatting is lost
- Multiple worksheets must be saved separately
Batch Processing
Merge Multiple CSVs
Combine multiple CSV files into one:
- Confirm headers are consistent
- Keep the header in the first file
- Remove headers from subsequent files
- Concatenate the content
Split a Large CSV
Split a large CSV by row count:
- Keep the header in each file
- Split by the specified number of rows
- Generate multiple smaller files
Filter Data
Filter CSV data by conditions:
- A field equals a specific value
- Numeric range filter
- Date range filter
Process CSV with DocsAll
- Upload a CSV file or paste content
- Automatic parsing
- Convert to JSON output
- Copy or download
Processed in the browser—data is not uploaded.
Processing Tips
Look at the Data First
Before processing, examine the CSV content:
- Open with a text editor (not Excel)
- Confirm the delimiter
- Confirm the encoding
- Confirm the header
- Check data integrity
Back Up the Original File
Back up the original CSV before processing. If cleaning goes wrong, you can roll back.
Test with a Small Batch
Process a few rows first to confirm the logic is correct, then batch process.
Validate Results
After processing, validate:
- Is the row count correct (should not be fewer than expected after cleaning)
- Is the field count consistent
- Spot-check key data
Summary
The core of CSV processing: understand parsing rules (commas, quotes, newlines), solve encoding issues (UTF-8, BOM), clean data (whitespace, formats, missing values), and convert formats (JSON, Excel). Look at the data before processing; validate results after. The DocsAll CSV to JSON tool runs in the browser—data never leaves your device.