What makes a “good” CSV file as opposed to an “evil” one? Okay, I suppose evilness isn’t really a property of CSV files, but if you’ve ever wrestled with a bad one, I think you know what I mean.
Since you're now aware that CSV (comma separated value) files are superior to delimited files for data importing, let’s talk about how to create a CSV file.
Here are the rules for creating CSV files:
Separate data fields with a delimiter, usually a comma. This should be a single character. If you do not want to use a comma, you should choose something like a tab or a pipe (|) character.
Keep each record on a separate line. Each record must start on its own line, but a single record can span multiple lines.
Do not follow the last record in a file with a carriage return.
In the first line of the file, include a header with a list of the column names in the file. This is optional, but strongly recommended; it allows the file to be self-documenting.
Make sure the header list is delimited in the same way as the rest of the file. This helps guard against the data fields being transposed in the data when it is loaded, which can lead to getting wrong answers when you query the data.
Remember that the enclosing character (typically double quotes) must be used when required, such as when the delimiter appears in a field.
For more detail on these rules, you can look at Wikipedia and RFC 4180 (the Request for Comments document in the CSV specification).
Put your flat file mastery to the test
Now that you have a thorough understanding of the different types of flat files, try putting it into practice with some data imports.
If you’re using ThoughtSpot, it’s simple—just upload a CSV file (or even multiple related CSVs) directly from your web browser. ThoughtSpot will automatically detect relationships, structure your data, and make it searchable in minutes. No complex setup, no heavy lifting—just fast, intuitive analytics from your own files.
Ready to explore on your own? Try ThoughtSpot today.
Start Getting Better Insights
Frequently asked questions on creating valid CSV files
1. What makes a CSV file valid?
A CSV file is valid when each record sits on its own line, fields are separated by a single consistent delimiter, and any field containing that delimiter is wrapped in an enclosing character like double quotes. Following these conventions, along with a header row and no trailing carriage return, keeps your data readable by any tool that imports it.
2. Do you have to use a comma as the delimiter in a CSV file?
No, you can use another single character such as a tab or a pipe if commas already appear inside your data. The one rule that matters: pick a single delimiter and apply it consistently, including in the header row, so your columns never get transposed on import.
3. Should a CSV file include a header row?
A header row is optional, but you should include one because it makes the file self-documenting and guards against columns being misread. List your column names in the first line, delimited exactly the same way as the rest of the file, so anyone (or any tool) can tell what each field means.
4. When do you need quotation marks around a field in a CSV file?
You need an enclosing character, typically double quotes, whenever the delimiter appears inside a field's value. Without those quotes, the import reads the embedded comma as a new column, which shifts your data and produces wrong answers when you query it later.
