What Is a CSV File?
A .csv file stands for comma-separated values file. This text file format contains tabular data that can be exported from and imported to spreadsheet programs, such as Microsoft Excel, Apple Numbers, OpenOffice Calc, or Google Sheets. Unlike other spreadsheet files, CSVs only carry a single sheet, with data fields most often separated by commas. They can store strings of numbers and words but not formulas and formatting styles.
Basic Structure
CSV files are universally readable—various software recognize the values in between the commas as distinct from each other. The comma acts as a delimiter to separate values within each row, but other file creators also use the tab, semi-colon, pipe, and carat.
Since a CSV file represents a table of data, every line is a row, with the first row serving as the table header. The values within that row—separated by commas—correspond to the labels or the title of each column. The values in the succeeding rows represent the column contents. Some CSV files may not have headers, and some files show each piece of data enclosed in double quotes.
Types of Data that CSV Files Can Handle
You can use CSV files to upload these types of data for a variety of purposes:
-
Customer contacts
You can convert your client database to a CSV file, so you can import their names and details to your email marketing, referral tracking, and customer relationship management software.
-
Stock-keeping units
SKUs are the scannable bar codes you find on the printed labels of products. Online stores can use CSV files to upload them to an inventory management or order fulfillment system.
Benefits of CSV Files
Here are some reasons why CSV files are convenient and versatile:
- They’re compact
As a plain text file, a CSV file takes up little memory and makes it ideal for transferring large basic data between systems or via email exchanges.
-
Their content is editable
You can alter the content of CSV files unless the original creator locked the columns to prevent them from being edited.
-
Their content is parsable
Due to the simple format of CSV files, their contents are easy to parse or organize using features in Excel or parsing programs.
-
They’re web-friendly
They can work on HTML, so they're for web developers to manipulate and upload on your website.
Table Size Limits of Spreadsheet Programs
You can use these programs to import CSV files, but they have their respective limits:
-
MS Excel
This Windows program can open up to 1,048,576 rows and 16,384 columns. OpenOffice and LibreOffice have the same limits.
When the file is too large, you can go to File Preview after clicking the Import button and look for the Load button with a drop-down arrow beside it. Click the arrow, and a new box will pop up saying "Select how you want to view this data in your workbook." Select "Only Create Connection" from the four options, and below it, click the square that says "Add this data to the Data Model." Then you'll find a new dialogue box on the right side of your screen showing the file name and number of rows loaded. Double-clicking this box will open the Power Query Editor that allows you to view more rows as you scroll down.
-
Apple Numbers
On Mac and Apple mobile devices, you can import, view, and edit tables with up to 1 million rows and 1,000 columns.
-
Google Sheets
You can open up to 40,000 rows and 18,278 columns at a time with Google's spreadsheet app.
These programs may not open a CSV file if it has too many rows, too many columns, or too many fields or bits of data. You can break up the file into separate sheets to enable uploading.
You may also use spreadsheet programs or data importers, including CSV Explorer, Apache Open Office, Python, and Flatfile. These software suppliers have their respective guidelines for safely importing and viewing your files on their platforms.
Common Import Issues
Besides having a large file size, some CSVs may not open due to:
-
Mis-matching and missing data
This can happen when there are missing column names, missing field values, or data that don't match the columns they're supposed to be under, and missing data.
-
Non-standard characters
Invalid symbols include asterisks, hyphens, parentheses, periods, and colons.
-
Non-digestible format
This can refer to inconsistent formats for dates and numerals-only data, such as social security numbers and phone numbers, that some systems may not read.
