CSV Files Explained: How to Open, Edit, and Convert Them Without Excel
CSV files are everywhere, from bank exports to CRM downloads to API responses. But opening them in the wrong tool corrupts your data before you even start. Here is how to handle CSVs properly, avoid the most common pitfalls, and convert them when you need a different format.
You downloaded a report from your bank. Or exported contacts from your CRM. Or received a data dump from a client. The file is a .csv, and now you need to do something with it. Maybe view the data in a table. Maybe convert it to a different format for import into another system. Maybe just figure out why the accented characters in the product names all turned into question marks.
CSV stands for Comma-Separated Values, and it is the lowest common denominator of data exchange. Every database can export it. Every spreadsheet can import it. Every programming language can parse it. This universality is both its greatest strength and the source of most of its problems.
What a CSV File Actually Is
At its core, a CSV file is just a text file where each line represents a row of data, and commas separate the values within each row. The first line usually contains column headers. Open any CSV in a plain text editor and you see something like this:
name,email,department,start_date
Sarah Chen,[email protected],Engineering,2023-03-15
Mike Rodriguez,[email protected],Design,2024-01-08
"Johnson, Patricia",[email protected],Finance,2022-11-30Notice the last row. Patricia Johnson's last name comes before her first name, and the whole value is wrapped in double quotes. This is because her name contains a comma, and without quotes the parser would split "Johnson" and "Patricia" into separate columns. Quote handling is where most CSV problems start.
The Three Problems That Break Every CSV Workflow
Problem one: encoding. A CSV file is text, and text comes in encodings. UTF-8 is the modern standard and handles every language on earth. But plenty of systems still export in Latin-1, Windows-1252, or ISO-8859-1. When you open a UTF-8 file expecting Latin-1 encoding (or vice versa), accented characters turn into garbage. The German name Muller with an umlaut becomes M followed by two random characters. Japanese text becomes a wall of question marks.
The frustrating part is that most applications do not tell you what encoding they expect or what encoding the file uses. You have to figure it out through trial and error, or by using a tool like Notepad++ that can detect encoding automatically. If you create CSV files, always save them as UTF-8 with BOM (Byte Order Mark). The BOM is a tiny invisible marker at the start of the file that tells applications the encoding. Without it, many programs guess wrong.
Problem two: delimiter confusion. CSV stands for Comma-Separated Values, but many CSV files do not actually use commas. German and French systems commonly use semicolons as the delimiter because those locales use commas as decimal separators (3,14 instead of 3.14). Tab-separated files often have a .csv extension. Some systems use pipes (|) as delimiters. When you open a semicolon-delimited file in a tool expecting commas, all your data ends up in a single column.
Problem three: Excel's aggressive type conversion. Excel silently changes your data when you open a CSV. Long numbers become scientific notation. Values that look like dates get reformatted. Leading zeros disappear. The gene name MARCH1 becomes March 1st. This problem is so well-documented in genomics research that scientific journals have published papers about it. The workaround is to import CSV files through Excel's Data > From Text/CSV wizard where you can force columns to be treated as text.
Opening CSV Files Without Excel
You do not need Excel or any paid software to work with CSV files. Here are the alternatives, ranked by what you are trying to do.
Just need to view the data in a table: Upload the file to Google Sheets (free). Or use an online CSV viewer that renders the data in a browser table without modifying anything. Online viewers are the safest option when you just need to check what is in a file because they do not auto-convert data types.
Need to edit values and save: LibreOffice Calc is free, runs on Windows, Mac, and Linux, and its CSV import dialog lets you specify encoding, delimiter, and column types before loading. It does not aggressively convert data types like Excel does. For quick edits on smaller files, a plain text editor with column highlighting (VS Code, Sublime Text) works well.
Need to restructure or clean the data: For anything beyond simple edits, Python with the csv module or pandas library gives you full control. You can rename columns, filter rows, merge files, change encodings, and handle edge cases that visual tools struggle with. The learning curve is an afternoon. The time savings compound with every file you process.
Converting CSV to Other Formats
The most common conversions are CSV to JSON (for APIs and web applications), CSV to XML (for legacy systems and data interchange), CSV to SQL (for database imports), and CSV to Excel (for people who want the data in a spreadsheet with formatting).
You can convert CSV files to other formats by uploading your file and selecting the target format. The converter handles delimiter detection, encoding, and type mapping automatically.
| Target Format | Best For | Watch Out For |
|---|---|---|
| JSON | APIs, web apps, NoSQL databases | All values become strings unless type detection is enabled |
| XML | Legacy systems, SOAP APIs, enterprise integrations | Column names must be valid XML element names (no spaces, no starting with numbers) |
| SQL INSERT | Database imports, data migration | Need to define correct data types for each column; SQL injection risk if values are not escaped |
| Excel (.xlsx) | Reports, sharing with non-technical users | Large files (100K+ rows) may hit Excel's performance limits |
| TSV | Data with commas in values, academic datasets | Tabs in data values (rare but possible) cause the same problems commas cause in CSV |
Data Cleaning Before Conversion
Raw CSV exports are messy. Before converting to another format, check for these issues:
Trailing whitespace. Spaces at the end of values are invisible in spreadsheet view but cause exact-match lookups to fail. "New York" and "New York " are different strings in every database and programming language. Trim all values before converting.
Inconsistent date formats. One column might have 03/29/2026, 29-03-2026, and March 29, 2026 mixed together. Standardize to ISO 8601 format (2026-03-29) before conversion. This format sorts correctly, is unambiguous, and is recognized by virtually every system.
Empty rows and headers. Many CSV exports include blank rows, section headers that span multiple columns, or summary rows at the bottom. These need to be removed before conversion because they break the regular structure that converters expect.
Duplicate headers. If the CSV was generated from a multi-page report, the header row might repeat throughout the file. Filter these out before converting, or the duplicate headers will appear as data rows in your output.
When CSV Is the Wrong Format
CSV works well for flat, tabular data with consistent column counts. It falls apart when your data is hierarchical (a customer with multiple addresses), relational (orders linked to customers linked to products), or contains large text blocks with commas and newlines. For those cases, JSON, XML, or a database export format is a better starting point.
The other situation where CSV fails is when you need data types. CSV is all text. A number, a date, a boolean, and a string all look the same in a CSV file. If the receiving system needs to know whether "42" is a number or a string, CSV cannot tell it. JSON and XML both support explicit type information, making them better choices for type-sensitive data exchange.
CSV has survived for over 50 years because it is simple enough to create with any tool and readable enough to debug with a text editor. Respect its limitations, clean your data before converting, and choose the right tool for viewing and editing. Most of the frustration people have with CSV files comes from using the wrong tool to open them, not from the format itself.
