Importing a CSV Into Google Sheets With the Right Types

Google Sheets treats a CSV more kindly than Excel does, mostly because there is no double-click: a file has to go through an import dialog, and the dialog shows you what it is about to do. That does not make it safe. The default setting converts anything that looks like a number, a date or a formula, so 00123 becomes 123 and 3/4/2024 becomes a date in whichever locale the spreadsheet has. The right settings take about ten seconds to choose, and they matter most for the columns you cannot afford to lose: identifiers, codes, long numbers and dates. The general damage is listed in opening CSV files in Excel and Sheets without damage, and this article is the Sheets side of it.

The import dialog

Open a spreadsheet and choose File > Import, then Upload and drop the file. The dialog has four controls that deserve a decision each.

Import location chooses between creating a new spreadsheet, inserting a new sheet, replacing the current sheet, replacing the spreadsheet, or appending to the current one. Appending is useful for a monthly feed, with the usual risk that a changed column order puts values under the wrong header. Separator type is Detect automatically, Comma, Tab or Custom. Detect is right most of the time and wrong on a file with few rows or odd quoting, so pick the real one if you know it. Convert text to numbers, dates, and formulas is a checkbox, ticked by default, and the one that does the damage.

Untick the checkbox and Sheets stores every cell as the text it read. A zip code 02134 stays 02134. A 19-digit account number stays as typed, which a numeric cell could not do, since Sheets keeps about 15 significant digits like Excel does (keeping long numbers intact when Excel opens a CSV explains the limit). A value like =SUM(A1:A3) stays as text and is not run, which is also a safety gain. The cost is that your quantities are text too, and a column of totals will not add up until you convert it. Convert only the columns you can name, with a formula such as =VALUE(D2) or by selecting the column and applying Format > Number > Number.

The decision I would make for a file I do not recognize is to import with conversion off and convert deliberately. For a file I know well, such as a clean export of numbers and ISO dates, conversion on is faster and fine.

Set the locale before you import

Sheets reads decimals and dates according to the spreadsheet's locale, under File > Settings > General > Locale. A file with 1.234,50 and 04.03.2024 imports as numbers and a date in a German locale and as text in a United States one. A file with 04/03/2024 is March 4 under one locale and April 3 under another, and Sheets will not tell you it chose.

The setting belongs to the spreadsheet, not to your account, so a sheet created by a colleague in another country carries their locale with it. Set the locale first, then import. Changing it afterwards does not re-read cells that were already converted: those keep the numbers they became, and only newly typed values follow the new rule. The time zone setting in the same panel matters for timestamps and NOW(), and not for text cells. For unambiguous dates in any locale, ask for ISO 8601 (2024-03-04), which Sheets reads the same way everywhere.

Pulling a CSV from a URL

A formula can load a CSV without any file handling:

=IMPORTDATA("https://example.test/exports/orders.csv")

IMPORTDATA fetches the URL, detects the separator and fills a block of cells below the formula. It refreshes periodically, which makes it good for a published report you want to watch. It has three limits to remember. It always converts types, with no checkbox, so identifiers lose their zeros. The URL must be reachable without a login, which rules out most private exports. And a large file is slow or fails, since each refresh refetches the whole thing. Treat it as a quick lookup tool and not as a loader.

Keep every cell as written with Apps Script

When you need the file exactly as it is, in a sheet, and need to repeat it, a script beats the dialog. Apps Script's Utilities.parseCsv parses a string into an array of arrays of strings, and nothing else, which is the behavior you want. Pair it with a plain text number format on the destination so the sheet does not convert on write:

function importCsv() {
  const file = DriveApp.getFilesByName("orders.csv").next();
  const text = file.getBlob().getDataAsString("UTF-8");
  const rows = Utilities.parseCsv(text);

  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet
    .getRange(1, 1, rows.length, rows[0].length)
    .setNumberFormat("@")
    .setValues(rows);
}

The order matters. setNumberFormat("@") marks the range as plain text before the values arrive, and setValues then writes strings that stay strings. If you set values first, Sheets parses them on write and the damage happens before you can format. For a semicolon file, pass the delimiter as a second argument: Utilities.parseCsv(text, ";"). The encoding goes into getDataAsString, and a file in Windows-1252 needs "windows-1252" there or é arrives as garbage (detecting the encoding of a CSV file).

After the load, convert the quantity columns, in the script, with a typed copy: parse rows[i][3] with parseFloat and write that column as numbers into its own range. Doing it in code means the conversion is explicit and repeats. The script runs from a time-driven trigger, so a CSV dropped in a Drive folder can refresh a sheet each morning without anyone opening the file.

Check the result

Two quick checks tell you whether the import behaved. For an identifier column, test the widths: =ARRAYFORMULA(LEN(A2:A)) and a pivot of the lengths should show one width where you expect one. A column of codes with two widths has lost zeros. For a numeric column, =COUNT(D2:D) counts the real numbers and =COUNTA(D2:D) counts all non-empty cells, and a difference means some cells are text. For a date column, =ISDATE(B2) shows whether Sheets parsed it.

Compare the row count of the sheet with the file's: wc -l orders.csv on the file against =COUNTA(A:A) in the sheet, remembering the header line and that embedded newlines inside quoted fields make the line count larger than the record count (checking the row and column counts of a CSV). A large gap means a quote swallowed part of the file, and the first suspect is a stray double quote in a text field.

When Sheets is the wrong place for the file

Sheets caps a workbook at 10 million cells in total, counting every sheet, and cells are rows times columns. A CSV with 500,000 rows and 25 columns is 12.5 million cells and will not load, although Excel would hold the rows. The practical ceiling for comfortable editing is far lower, around tens of thousands of rows, after which the browser slows down.

It is also the wrong place for data that must stay exact and be edited by several people at once, since each editor's locale and paste habits can alter a cell without any record. Keep the source file as the record, load it into a database or DuckDB for work (querying a CSV file with DuckDB instead of loading it), and use Sheets for the part people read.

When a spreadsheet is the destination, a final habit helps: protect the identifier columns and mark them as text with a note in the header row, so the next person pasting into them is warned. Google documents the import dialog in import data in Google Sheets, and the script service is described in the Apps Script Utilities reference.