Edit CSV File in Google Sheets

Introduction

This is a crash course to edit a Comma Separated Values (.csv) file with Google Sheets. Editing a CSV file isn't straightforward, because Windows or MacOS do not provide a visual editor for the CSV file type. Editing in a text editor is error-prone. It requires a third-party CSV file editor, such as Google Sheets or Microsoft Excel.

A CSV file stores tabular data, such as numbers, text, date, phone number, in plain text, with each data row representing a data record. The simplicity, lightweight and universal compatibility made the file type stand out for data sharing purposes among many data file types. In xFanatical Foresight automation platform, CSV files are widely used as data input for bulk workflow automations.

EmployeeID,Name,Department,Age,Salary
101,Jane Smith,Marketing,29,58000
102,John Doe,Engineering,34,76000
103,Lisa Wong,Human Resources,41,69000
104,Mark Davis,Sales,27,54000
105,Amy Johnson,Engineering,30,72000
106,Robert King,Finance,38,81000
107,Emily Green,Marketing,25,50000
108,Daniel Lee,Sales,45,63000
109,Nina Patel,Engineering,32,70000
110,Chris Adams,Finance,40,85000

Sample CSV file in a text editor's view

Google Sheets, as an office spreadsheet cloud software, visualizes the CSV file for user-friendly editing experience. Follow the instructions below to import, edit and export a CSV file.

Instructions

Import and edit CSV

1. Create a blank Google Sheet.
   
2. Go to File > Import.
   
   
3. Click Upload, and then Browser.
   
   
4. Select the CSV file in your system.
   
5. In the Import file dialog, select the preferences to import and edit the CSV file.
   
   1. In Import location, select where the CSV file would be imported.
   2. In Separator type, select the separator of column values. Leave the default Detect automatically.
   3. In Convert text to numbers, dates, and formulas, leave it checked by default. Learn more in Notes > About Converting text to numbers, dates, and formulas.
      
6. Edit the CSV file in the Google Sheets.
   

Export Google Sheets into CSV

1. Open the Google Sheet.
   
2. Click File > Download > Comma Separated Values (.csv).
   
   
3. The browser downloads the CSV file into your system in the name format of {spreadsheet name} - {sheet name}.csv.

Notes

About Separator types

• Comma. Comma Separated Values (.CSV) files separate column data in a visible comma , character.
  
  
• Tab. Tab Separated Values (.TSV) files separate column values in an invisible Tab character.
  
  
• Custom Separator. Other separators like #, (space), | are user defined separators and less common and typically unsupported in data editing applications.
  

About Converting text to numbers, dates, and formulas

The Converting text to numbers, dates, and formulas checkbox in Google Sheets during the import process is a tricky option to choose. Here are the factors to consider.

Check Converting text to numbers, dates, and formulas when you want to

• Convert text like 1000, 3.14, -42 to be numbers.
• Convert date / time like text to real date/time values, e.g. 04/03/2025, 3-Apr, 12:30PM.
• Calculate formula text =SUM(A1:A100) after importing and display the formula value 136 instead of raw text =SUM(A1:A100).
• Boolean type text, true or false. These 2 words will be standardized to TRUE and FALSE in Google Sheets.

Uncheck Converting text to numbers, dates, and formulas when you want everything exactly as it appears in the CSV.

• Number-like data with leading zeros, like zip codes 09013, or IDs 0001007. The leading zeros will be lost if converted to numbers;
• Date-like data, 03-04, or 03/04. Google Sheets may misread it as March 4th or April 3rd of current year, mistakenly treat it as a Date cell and display a date picker;
• Time-like data, 9:10. Google Sheets may interpret it as Morning time 09:10, when it's actually a match score.
• Formula-like data, =F1. Google Sheets may mistakenly treat it as a formula, calculate the formula and displays the formula value instead of the raw text.
• Boolean type text, true or false. Keeping them in letter case.

Format loss

A frequently raised concern involves the loss of formatting when exporting a Google Sheet as a CSV file. CSV files only carry the data. The text formatting, such as header color, column width, are features in Google Sheets. When downloading a Google Sheet to a CSV file, only the data is exported and text formatting is lost.

About formulas

Formulas are an powerful feature in Google Sheets. CSV files do not understand, interpret or calculate formulas.

• When importing a CSV file to a Google Sheets, the Google Sheets option of Converting text to numbers, dates, and formulas recognizes the formulas and converts them into data.
• When exporting the same Google Sheet to a CSV file, the same formula is lost and the cell data is replaced by the formula value.

Due to the incompatibility, it's not recommend to store platform-specific (Google Sheets or Microsoft Sheets) formulas into CSV files.

Conclusion

Editing a CSV file in Google Sheets offers a convenient and user-friendly way to manage, organize, and update data, compared to a text editor. With the correct editor, you can ensure the data is properly edited, and more importantly, allow xFanatical Foresight to process the edited data, automate your workflows, and improve productivity.