Quick answer: If a CSV imports into one column in Google Sheets, use File → Import and choose the separator that the file actually uses. It may be a comma, semicolon or tab. Import into a new tab first, then check the headers and several records before replacing existing data.
Practical example & checks
A CSV sample that exposes separator and quoting mistakes
company;email;note
Alder;alder@example.com;"Needs review; keep source"
Birch;birch@example.com;ApprovedSave these three lines as a UTF-8 text file with a .csv extension. In Google Sheets, use File → Import → Upload and select semicolon as the separator. Import into a new sheet.
Expected: three columns and two data rows. The first note remains one cell containing Needs review; keep source. The semicolon inside quotes belongs to that note; replacing every semicolon with a comma would damage the file’s structure.
If the entire record lands in one cell, re-import with the correct delimiter. If a quoted note splits across columns, return to the source and check its quoting rather than shifting cells manually. Verify row count and the final record before replacing any working table.
Original ThinkQuarry teaching material added 19 October 2026. Hypothetical examples are not product-test results.
A file named .csv is not guaranteed to use commas in the way you expect. Export settings, regional conventions and data inside quoted fields all affect the result. The first task is to identify the file’s structure, not to manually drag individual cells into place.
Inspect one header and one complete record
Open a copy of the file in a plain-text editor. This is an inspection step: avoid saving changes unless you intentionally want to change the file. Look at the character separating the headers.
Company,Email,Notes
Example Studio,hello@example.com,"Design, research and delivery"This sample is comma-separated. The comma inside the quoted Notes field belongs to the text, not to a new column. A simple split at every comma can turn three columns into four and misalign the record.
A different export might start with Company;Email;Notes. Choose semicolon for that file. Tabs can be hard to see in a text editor; enabling visible whitespace can help distinguish a tab from several spaces.
Re-import into a clean tab
- Keep the original file as a reference.
- Open the destination spreadsheet and choose File → Import.
- Upload the CSV or select the file from Drive.
- Choose an import location that creates a separate sheet rather than replacing your working table.
- Set Separator type to the identified delimiter, or test automatic detection and inspect the result.
- If the dialog offers conversion of text to numbers, dates and formulas, consider disabling it when importing identifiers.
- Import and check several rows before copying anything to the main tab.
Google documents separator selection as part of its import workflow. Starting with a new tab gives you a clean comparison point if automatic detection or conversion does not match the export.
When Split text to columns is appropriate
Data → Split text to columns is useful for simple text that is already in a sheet and has a predictable separator. Use it for a field such as London|UK when the vertical bar consistently separates two values.
For a real CSV containing quoted commas, embedded line breaks or complex notes, prefer the import workflow. A quick split can appear successful on the first row while silently damaging later rows. Check the most complicated record, not only the simplest one.
| Symptom | Check first | Safer next step |
|---|---|---|
| Everything in A | Wrong separator | Re-import with the actual delimiter |
| Notes spill into extra columns | Quoted commas or inconsistent quoting | Inspect the export and import it as CSV |
| IDs lose zeros | Automatic numeric conversion | Import the field as text |
| Accented names look damaged | Encoding mismatch in source export | Ask for a UTF-8 export and compare the source |
Run a five-record acceptance check
Choose an ordinary row, a row with a comma in its notes, a row with an empty field, a row with an international name, and a row with a leading-zero ID. Confirm each record still has the right email, company and note. A correct header does not establish that every row parsed correctly.
Also compare record counts. A multiline quoted field can make the raw file’s physical line count differ from its logical record count, so count actual records rather than assuming every text line is a separate row.
Common questions
Should I replace every semicolon with a comma?
No. A semicolon may be part of a field’s content. Use the correct separator during import instead of rewriting characters globally.
Can a CSV preserve colored cells and formulas?
CSV is a text data exchange format, not a full spreadsheet workbook. Keep a native Sheets or XLSX copy when formatting, multiple tabs or workbook logic matters.
What if the file is malformed?
Return to the source export if possible. Manually correcting scattered cells without a repeatable rule makes it difficult to know whether the remaining records are accurate.
After import, use safe row sorting. If you also use Excel, read how to preserve leading zeros in a CSV.
Sources and further reading
Prepared by ThinkQuarry. Documentation checked 3 October 2026. Menu labels may vary by version. Featured image: original AI-generated photographic concept created for ThinkQuarry. This editorial visual is not a product screenshot.
