10 Common CSV Import Errors (And How to Fix Them Fast)

When Your CSV Import Breaks (Again)

You've exported a CSV from your CRM. Or downloaded analytics data. Or received a file from a vendor. You try to import it into Google Sheets or Excel, and... something breaks.

Maybe everything ends up in one column. Maybe your phone numbers turned into scientific notation. Maybe you see weird characters like Renée instead of Renée.

Welcome to CSV hell.

This guide covers the 10 most common CSV import errors, what causes them, and exactly how to fix them. Bookmark this—you'll need it.

Error #1: "All My Data Is Crammed Into Column A"

What you see: Your CSV opens, but instead of neat columns, everything is jammed into the first column as one long text string.

Example:

Column A contains: "John,Doe,john@email.com,555-1234"
(Should be spread across 4 columns)

Why This Happens

Your software is using the wrong delimiter. Your file uses semicolons (;), tabs, or some other character to separate columns. But Excel or Google Sheets assumed it was commas.

This is especially common with European CSV files, which use semicolons because commas are decimal separators in Europe.

How to Fix It

In Google Sheets:

1. Don't double-click the file
2. Go to File → Import
3. Upload your CSV
4. Change "Separator type" to the correct delimiter (try semicolon, then tab)
5. Check the preview—if columns look correct, click Import

In Excel:

1. Open Excel first (don't double-click CSV)
2. Go to Data → Get Data → From Text/CSV
3. Select your file
4. Click "Transform Data"
5. In Power Query, specify the correct delimiter
6. Close & Load

How to identify the delimiter:

• Open the file in TextEdit (Mac) or Notepad (Windows)
• Look at the first few lines
• The character separating your values is your delimiter

---

Error #2: "My Leading Zeros Disappeared"

What you see: ZIP codes like "02134" become "2134". Product codes like "00789" become "789".

Example:

Before: 02134, 00789, 00001
After:  2134, 789, 1

Why This Happens

Spreadsheet software sees a string of digits and thinks, "That's a number!" It then helpfully removes "unnecessary" leading zeros. But those zeros weren't unnecessary—they were part of identifiers.

This happens automatically when you double-click a CSV file. The software makes formatting assumptions before you can tell it otherwise.

How to Fix It

Prevention (if you haven't opened the file yet):

In Google Sheets:

1. File → Import
2. Upload your CSV
3. CRITICAL: Uncheck "Convert text to numbers, dates, and formulas"
4. Import
5. All columns stay as text, preserving leading zeros

In Excel:

1. Data → Get Data → From Text/CSV
2. In the preview, click "Transform Data"
3. Select columns with leading zeros
4. Right-click → Change Type → Text
5. Close & Load

Recovery (if zeros are already gone):

If you still have the original file, re-import it correctly (above).

If not, and you know the format (like ZIP codes should be 5 digits):

=TEXT(A1,"00000")

This converts 2134 back to 02134. The number of zeros determines total length.

---

Error #3: "Special Characters Are Garbled (Encoding Errors)"

What you see: Names like "René" become "René". Cities like "São Paulo" become "São Paulo".

Example:

Should be: José, Björk, Zürich
Displays as: José, Björk, Zürich

Why This Happens

Your CSV file is encoded in UTF-8, but your software is reading it as Latin-1 (or vice versa). This is a character encoding mismatch.

Character encoding is how computers store letters as numbers. Different systems use different standards. When they don't match, special characters break.

How to Fix It

In Google Sheets:

Google Sheets usually auto-detects encoding correctly. If it doesn't:

1. File → Import
2. Look for "Character encoding" option (appears for some file types)
3. Try UTF-8 first, then ISO-8859-1 if that fails

In Excel (Mac):

1. Data → Get Data → From Text/CSV
2. In the preview window, look for "File Origin"
3. Select "65001: Unicode (UTF-8)"
4. Transform and load

In TextEdit (for diagnosis):

1. Open the file in TextEdit
2. Format → Make Plain Text
3. File → Save
4. Choose "UTF-8" encoding
5. Re-import

Prevention:

Always export CSV files as UTF-8 when you have the choice. It's the universal standard that handles every language and special character.

---

Error #4: "My Phone Numbers Became Scientific Notation"

What you see: Phone number "8005551234" becomes "8.01E+09". Completely unreadable.

Example:

Should be: 8005551234, 15551234567
Displays as: 8.01E+09, 1.56E+10

Why This Happens

Spreadsheets see long strings of digits and assume they're large numbers. Numbers over 10 digits often get converted to scientific notation for readability. But phone numbers aren't math—they're identifiers.

How to Fix It

Prevention:

Same solution as leading zeros:

1. Use the import wizard
2. Set phone number columns as Text type during import
3. Or uncheck "Convert text to numbers"

Recovery:

If the numbers are already converted, you're in trouble. Scientific notation loses precision after 15 digits, so recovery isn't always possible.

If you still have the original file, re-import correctly.

If not, and you have a backup or source system, re-export.

Future prevention:

When exporting phone numbers from a database or CRM, wrap them in quotes in the CSV:

"8005551234"

Quotes force text interpretation.

---

Error #5: "File Is Too Large to Import"

What you see: "File exceeds size limit" or "Too many cells" error.

Why This Happens

Different platforms have different limits:

Platform	Limit
Google Sheets	10 million cells total
Excel (Mac)	1,048,576 rows × 16,384 columns
Numbers (Mac)	~1 million cells (varies)

If your CSV has 500,000 rows and 50 columns, that's 25 million cells—exceeding Google Sheets' limit.

How to Fix It

Option 1: Split the file

Use command-line tools to split:

# On Mac, split CSV into files of 100,000 rows each
split -l 100000 largefile.csv split_part_

Then import each part separately.

Option 2: Import only needed columns

If you don't need all 50 columns, filter during import:

1. Open CSV in a text editor
2. Delete unnecessary columns
3. Save and re-import

Option 3: Use a database

For truly large datasets, CSV and spreadsheets aren't the right tool. Import into:

• SQLite (free, local database)
• PostgreSQL (more powerful)
• BigQuery (Google's cloud database)

Then query for the subset you need and export that.

Option 4: Use a programming language

Python with pandas:

import pandas as pd
# Read only first 100,000 rows
df = pd.read_csv('large.csv', nrows=100000)

---

Error #6: "Dates Imported as Weird Numbers"

What you see: Date "12/15/2024" becomes "45642" or similar.

Example:

Should be: 12/15/2024
Displays as: 45642

Why This Happens

Excel stores dates as numbers (days since January 1, 1900). When you import a CSV, it sometimes converts dates to this numeric format, then fails to display them as dates.

How to Fix It

In Excel:

1. Select the column with weird date numbers
2. Format → Cells → Date
3. Choose your preferred date format

Excel will re-interpret the numbers as dates.

In Google Sheets:

1. Select the column
2. Format → Number → Date

Prevention:

Store dates in ISO format in your CSV files: YYYY-MM-DD

2024-12-15

This format is unambiguous and imports correctly everywhere.

---

Error #7: "Commas in My Data Broke Column Alignment"

What you see: Address "123 Main St, Suite 4B" gets split into two columns. Everything shifts.

Example:

| Name | Address |
| John | 123 Main St |

(Should be:)
| Name | Address |
| John | 123 Main St, Suite 4B |

Why This Happens

Your data contains the delimiter character (comma). Without text qualifiers (quotes), the import process treats that comma as a column separator.

How to Fix It

Proper CSV files wrap such fields in quotes:

"John","123 Main St, Suite 4B"

Most export tools do this automatically. But if yours doesn't:

Option 1: Fix the export

• Re-export from the source with "text qualifiers" enabled
• Most export dialogs have this option

Option 2: Manual fix

• Open in a text editor
• Find fields containing commas
• Wrap them in double quotes: "123 Main St, Suite 4B"

Option 3: Use a different delimiter

• Export as tab-separated (TSV) instead
• Tabs rarely appear in normal text data

---

Error #8: "File Won't Import At All (Format Not Recognized)"

What you see: "Unable to parse file" or "Format not supported" error.

Why This Happens

The file might not actually be a CSV, or it's corrupted, or it has weird formatting.

How to Fix It

Step 1: Verify it's actually CSV

• Open in TextEdit/Notepad
• Does it look like comma-separated text?
• Or is it binary garbage?

Step 2: Check the file extension

• Should be .csv or .txt
• If it's .xls or .xlsx, use Excel to open it directly (not as CSV)

Step 3: Try re-downloading

• File might have been corrupted during download
• Clear browser cache and download again

Step 4: Check for BOM (Byte Order Mark)

Some programs add an invisible BOM character at the start of UTF-8 files. This breaks imports.

Fix in TextEdit:

1. Open file
2. Format → Make Plain Text
3. File → Save
4. Choose "UTF-8 without BOM" if available

---

Error #9: "Empty Rows or Columns Appeared"

What you see: Blank rows between your data. Or extra empty columns at the end.

Example:

| Name | Email | | |
| John | john@email.com | | |
| | | | |
| Jane | jane@email.com | | |

Why This Happens

Your original spreadsheet had empty rows or columns. When exported to CSV, these become commas with no data between them:

John,john@email.com,,,
,,,
Jane,jane@email.com,,,

How to Fix It

Option 1: Clean before export

1. In your source spreadsheet, delete empty rows/columns
2. Re-export to CSV

Option 2: Clean after import

1. Import the CSV
2. Select empty rows: Edit → Delete rows
3. Select empty columns: Edit → Delete columns

Option 3: Filter programmatically

Python example:

df = pd.read_csv('messy.csv')
df = df.dropna(how='all')  # Remove rows that are completely empty

---

Error #10: "Import Worked But Data Looks Wrong"

What you see: File imported without errors, but:

• Numbers are left-aligned (means they're text)
• Dates don't sort correctly
• Formulas don't calculate

Why This Happens

Everything imported as text. It looks fine, but the data types are wrong, so spreadsheet functions don't work.

How to Fix It

Fix data types post-import:

For numbers:

1. Select the column
2. Format → Number → Number
3. If values don't change, they might have leading spaces
4. Use Data → Text to Columns (Excel) or TRIM() function

For dates:

1. Select the column
2. Format → Number → Date
3. Choose format (MM/DD/YYYY or DD/MM/YYYY depending on your source)

For formulas (if you need them to calculate):

CSV files can't store formulas. If you want formulas, you need to:

1. Keep the original Excel/Sheets file with formulas
2. Export only when you need a data snapshot

---

How to Prevent CSV Import Problems

1. Never Double-Click CSV Files

This is the #1 cause of import problems. Double-clicking lets software make assumptions. Those assumptions are wrong 40% of the time.

Always use: File → Import (Google Sheets) or Data → Get Data (Excel)

2. Export with Proper Settings

When you're creating the CSV:

• Use UTF-8 encoding (prevents character issues)
• Enable text qualifiers (wraps problem fields in quotes)
• Choose a safe delimiter (comma or tab)

3. Standardize Your Workflow

Create a checklist for imports:

• Use import wizard (not double-click)
• Check delimiter (comma, semicolon, tab?)
• Turn off auto-formatting ("Convert text to numbers")
• Preview before importing
• Verify first few rows look correct

4. Use Automation Tools

If you're importing CSV files more than once a week, manual processes get tedious.

For Mac users working with Google Sheets, tools like CSVtoSheets automate the entire import process:

• Handles delimiter detection automatically
• Preserves leading zeros and special formatting
• Opens files correctly with one double-click
• No manual wizard every time

For occasional use, manual is fine. For daily use, automation saves hours.

---

Quick Reference: CSV Import Troubleshooting

Problem	Most Likely Cause	Quick Fix
All in one column	Wrong delimiter	Use import wizard, try semicolon or tab
Leading zeros gone	Auto-formatted as number	Re-import with "Convert to numbers" OFF
Garbled characters	Encoding mismatch	Import as UTF-8
Scientific notation	Large numbers auto-formatted	Set column to Text during import
File too large	Exceeds cell limit	Split file or use database
Dates as numbers	Format not applied	Apply Date format to column
Broken column alignment	Comma in data	Re-export with text qualifiers
Won't import at all	Corrupted or wrong format	Re-download, check file type
Empty rows/columns	Present in source	Clean before export, or delete after import
Functions don't work	Wrong data type	Convert to Number or Date format

---

When to Give Up on CSV

Sometimes, CSV just isn't the right format for your data.

Use Excel (.xlsx) instead when:

• You need to preserve formulas
• Formatting conveys meaning (colors, bold)
• You have multiple related sheets
• Data validation is important

Use a database when:

• Files exceed 10 million cells
• You need complex queries
• Multiple users need concurrent access
• Data changes frequently

Use JSON or XML when:

• Data has nested structures
• You're working with APIs
• Programming languages will consume it

CSV is great for flat, tabular data exchange. It's terrible for everything else.

---

Related Resources

More CSV troubleshooting guides:

• How to Fix CSV Delimiter Problems
• How to Preserve Leading Zeros in CSV Files
• CSV vs Excel: Which Format Should You Use?
• The Brutally Honest Guide to Converting CSV to Sheets

---

Still having import problems? Check our full CSV guides or try CSVtoSheets for automated, error-free imports on Mac.