Import a CSV to Google Sheets Without Data Loss

Importing a CSV to Google Sheets takes one menu: File then Import, then a choice of import location and separator. Unlike a double-click into a desktop spreadsheet, the dialog lets you decide how the values are read before they land — which is exactly where leading zeros, long IDs, and product codes are won or lost.

What Google Sheets does to a CSV on import

A CSV holds no types. It is plain text — one line per row, values separated by a delimiter — and every character in it is a character until something decides otherwise. Google Sheets is that something. On import it reads each column, guesses what the values are meant to be, and rewrites them to match the guess.

Most of the time the guess is right and invisible. The damage shows up in four places:

• Leading zeros vanish. A UK postcode column, a German customer number, a US ZIP starting 0 — read as a number, 01234 has no reason to keep its zero, so it becomes 1234.
• Codes become dates. 3-5 turns into 3 May. 1/2 turns into 1 February. Anything shaped like a date is treated as one, interpreted through the spreadsheet's locale, so the same file read in a US locale and a UK locale can produce two different days.
• Long numbers lose their tail. Order IDs and barcodes past 15 digits are stored as floating-point numbers and the final digits are replaced with zeros.
• Leading and trailing spaces disappear or persist unevenly, which matters later when you try to match two records that look identical.

None of this is a bug — a spreadsheet exists to do arithmetic, and arithmetic needs numbers. It is a problem only because the conversion happens silently and cannot be reversed once the sheet is saved. A custom format restores the look of 01234; it does not bring the digit back.

One of those four is worth a setting of its own. Date interpretation follows the spreadsheet's locale, not the file's, so a column of 03/04/2026 values reads as 3 April in a UK sheet and 4 March in a US one — with no warning either way, because both readings are valid. If your exports come from a system in a different region, set the locale to match before you import, under File > Settings > Locale. Changing it afterwards reformats how the dates display but does not re-parse what was already read, so the order has to be right the first time.

The useful difference from Excel is that Sheets asks first. There is a checkbox in the import dialog that turns the whole guessing pass off, and it is the single most important control on this page.

The import route that keeps every value intact

Skip dragging the file into Drive. Use the import dialog, which is the only route that exposes the separator and conversion settings.

1. Open a spreadsheet. A blank one, or the file you want the data to land in.
2. Go to File > Import. The Upload tab takes a file from your computer by drag-and-drop or file picker; the other tabs read files already in Drive or shared with you.
3. Choose an import location. Google documents six: create a new spreadsheet, insert new sheets, replace the spreadsheet, replace the current sheet, append rows to the current sheet, or replace data starting at the selected cell. Appending is the one people miss — it is how you stack this month's export onto last month's without a copy-paste.
4. Set the separator type. The options are Detect automatically, Tab, Comma, or Custom. Detect automatically is right most of the time and wrong in one specific case: exports from systems configured for European locales, which separate with semicolons. If the preview looks like one long column, this is why.
5. Untick "Convert text to numbers, dates, and formulas" when the file holds identifiers. Then click Import data.

That last step has a cost worth stating plainly. With the box unticked, everything arrives as text — including the columns you do want to add up. Two ways round it: wrap the numeric columns in =VALUE(A2) in a helper column, or import as text, fix the identifier columns, and use Data > Split text to columns on the numeric ones to convert them deliberately rather than automatically.

If the file is going the other way — out of Sheets and into a system that wants a workbook — you can turn a CSV into an XLSX file in the browser instead of round-tripping it through a Google account.

Pull a CSV from a URL with IMPORTDATA

When the CSV lives at a stable web address rather than on your laptop, Sheets can fetch it with a formula. IMPORTDATA takes a single argument — the URL of a CSV or TSV file, including the protocol — and spills the contents into the sheet:

=IMPORTDATA("https://example.com/exports/leads.csv")

Three things to know before you rely on it. The fetch happens from Google's servers, not from your browser, so an internal URL that works on your network will fail. The result is a live formula rather than a set of values, so the sheet re-reads the file rather than holding a snapshot — copy and Paste special > Values only when you need it frozen. And the conversion controls from the import dialog do not apply here, so the type guessing described above runs unchecked.

That makes IMPORTDATA a good fit for public reference data and a poor fit for a customer export.

When the CSV is too big for Google Sheets

Sheets has a hard ceiling, and it is measured in cells rather than rows: 10 million cells or 18,278 columns (column ZZZ) for any spreadsheet created in or converted to Sheets, with the same limits applied to CSV and Excel imports. A separate rule drops any cell holding more than 50,000 characters during conversion.

Ten million sounds generous until you divide it. A 40-column export runs out at roughly 250,000 rows. A 200-column CRM dump — and CRM exports are wide — runs out around 50,000. The columns you are not using still consume the budget.

There is no warning worth relying on when you cross the line. An import that exceeds the ceiling loads what fits and leaves the remainder behind, which produces a sheet that looks complete and is not. If the source file is large enough that this is a live question, count its rows before the import and compare that number against the last populated row in the sheet afterwards. A total, a pivot, or a deduplication run on a truncated file returns an answer that is confidently wrong.

So when an import stalls or truncates, the first move is to narrow the file rather than to find a bigger tool. Delete the columns nobody reads, drop the duplicate rows, and the same data often clears the ceiling with room to spare. Doing that work before the import also means the sheet opens faster, because every cell you removed is a cell Sheets never has to render.

Clean the CSV before Sheets ever sees it

There is a second reason to do the row-level work first, and it is about where the file goes. Importing to Sheets copies the file to Google's servers and stores it in your Drive. For a public dataset that is unremarkable. For a customer list, an employee export, or a candidate pipeline, it is a decision with a GDPR answer attached, and it is worth making deliberately rather than by reflex.

Sigmera's in-browser cleaning tools take the other route: the file is read into the page with client-side JavaScript and handed back as a download. Nothing is uploaded, so there is no copy on a server to account for. In practice that means you can remove duplicate rows, standardise an email column, format phone numbers to E.164, and split a Full Name column in two before anything reaches Drive — and import a file that is smaller, deduplicated, and free of the columns that were going to break on type inference anyway.

If the data is already in a sheet, the same job can be done there: removing duplicates in Google Sheets covers the Data cleanup menu and the UNIQUE formula. Previewing a cleaned file in Sigmera costs nothing and is unlimited — the download counter only moves when you export the finished result.