{"id":109,"date":"2026-11-15T09:20:00","date_gmt":"2026-11-15T09:20:00","guid":{"rendered":"https:\/\/www.masters-tool.com\/blog\/?p=109"},"modified":"2026-11-11T03:39:06","modified_gmt":"2026-11-11T03:39:06","slug":"csv-escaping-before-excel-import","status":"publish","type":"post","link":"https:\/\/www.masters-tool.com\/blog\/csv-escaping-before-excel-import\/","title":{"rendered":"CSV Escaping Before an Excel Import"},"content":{"rendered":"<p><strong>TL;DR \/ Summary:<\/strong> A CSV export that opens with one column containing everything is a delimiter problem, and a CSV that turns phone numbers into scientific notation is a formatting problem. Fix both at the source rather than repairing the sheet by hand.<\/p>\n<h2>The one-column problem<\/h2>\n<p>Opening a CSV and seeing every field crammed into column A means the spreadsheet guessed the delimiter wrong. This is common with European exports that use semicolons instead of commas.<\/p>\n<p>Excel and similar tools default to the system list separator. A machine configured for a comma sees a semicolon-delimited file as one column.<\/p>\n<p>The fix is to import rather than open. Use the data import wizard and specify the delimiter explicitly, rather than double-clicking the file.<\/p>\n<ul>\n<li><strong>Comma<\/strong> \u2014 the standard, but ambiguous when fields contain commas<\/li>\n<li><strong>Semicolon<\/strong> \u2014 common in European exports<\/li>\n<li><strong>Tab<\/strong> \u2014 often used for pasted data<\/li>\n<li><strong>Pipe<\/strong> \u2014 used where commas appear in content<\/li>\n<\/ul>\n<h2>Quoting and embedded delimiters<\/h2>\n<p>The real reason CSV is fragile is that fields contain the delimiter. A product name like &#8220;Smith, Jones and Co&#8221; breaks a naive comma split.<\/p>\n<p>The CSV specification handles this with quoting: a field containing a comma is wrapped in double quotes. A field containing a quote has the quote doubled.<\/p>\n<p>When writing a CSV from a script, apply quoting properly rather than joining values with commas. A one-line change to use a proper CSV library prevents an entire class of downstream bugs.<\/p>\n<h3>Newlines inside fields<\/h3>\n<p>Multi-line text in a field is legal CSV and catastrophic for line-based tools. A single record can span several physical lines.<\/p>\n<p>Either strip newlines from field content before export, or use a parser that understands quoting. A line-based script that splits on newline will produce a corrupted record and no error message.<\/p>\n<h2>The formatting problem<\/h2>\n<p>Spreadsheets auto-convert values that look like numbers or dates. A product code of 00123 becomes 123. A phone number with a leading zero loses it. A date like 03\/04\/2026 becomes a date object resolved by locale.<\/p>\n<p>These conversions are lossy, and they happen silently on open. The original file is fine; the spreadsheet&#8217;s interpretation is the problem.<\/p>\n<ul>\n<li><strong>Leading zeros<\/strong> \u2014 set the column to text before opening, or prefix with an apostrophe<\/li>\n<li><strong>Long numbers<\/strong> \u2014 identifiers above fifteen digits lose precision<\/li>\n<li><strong>Dates<\/strong> \u2014 use ISO format in the export to avoid locale ambiguity<\/li>\n<li><strong>Scientific notation<\/strong> \u2014 a symptom of a column being treated as numeric<\/li>\n<\/ul>\n<blockquote><p>Never open a raw CSV by double-clicking. Import it, with the types specified.<\/p><\/blockquote>\n<h2>Exporting for other people<\/h2>\n<p>If the CSV is going to someone else, assume they will double-click it. That means the file should survive the default behaviour of the most common spreadsheet.<\/p>\n<p>Use ISO 8601 dates. Avoid leading-zero identifiers or quote them. Keep the header row simple and free of punctuation that could be misread.<\/p>\n<p>Consider an accompanying note describing the delimiter and the encoding. It takes one line and prevents the most common support question.<\/p>\n<h2>Encoding<\/h2>\n<p>Accented characters and non-Latin scripts turn into mojibake when the encoding is guessed wrong. UTF-8 is the correct default; some spreadsheet versions assume a local codepage.<\/p>\n<p>A byte order mark can help a spreadsheet detect UTF-8, and it can also break naive parsers that read the first field literally. Choose deliberately rather than by accident.<\/p>\n<p>Test with a file containing at least one accented name before sending it anywhere important.<\/p>\n<h2>Sanitising for spreadsheets<\/h2>\n<p>Spreadsheet applications interpret cells beginning with certain characters as formulas. A field starting with an equals sign can execute a formula when opened, which is a security problem as well as a data problem.<\/p>\n<p>Prefix such values with an apostrophe, or quote them, or strip the leading character. This is worth doing for any CSV generated from user input.<\/p>\n<p>Check fields starting with equals, plus, minus, and at signs. Those are the common formula prefixes.<\/p>\n<h2>A practical checklist<\/h2>\n<p>Before exporting: choose a delimiter, quote fields containing it, strip newlines, use ISO dates, and protect identifiers.<\/p>\n<p>Before importing: specify the delimiter and the encoding, and set identifier columns to text.<\/p>\n<p>For converting small datasets between formats without writing a script, <a href=\"https:\/\/www.masters-tool.com\/\">master tool<\/a> covers the common transformations, but the export rules above matter more than the tool.<\/p>\n<p>CSV is a simple format with subtle rules. Respecting the rules costs a few minutes and saves an afternoon of manual repair in a spreadsheet that should never have needed it.<\/p>\n<h2>Large files and the import limit<\/h2>\n<p>Spreadsheets have row limits and memory limits, and a CSV that exceeds them fails in ways that look like corruption. Check the size before sending a file to someone who will open it in a spreadsheet.<\/p>\n<p>If the file is large, offer a split export or a database file instead. Sending a file that cannot be opened is worse than sending none, because the failure looks like the recipient&#8217;s error.<\/p>\n<p>Compression helps with transfer and not with import. A zipped CSV must be extracted before it can be read, which is a step people forget.<\/p>\n<h3>Validating before delivery<\/h3>\n<p>Open the file in a text editor before opening it in a spreadsheet. Check the header, the first record, and the last record, and confirm the field count matches everywhere.<\/p>\n<p>A mismatch in field count is the clearest sign of a quoting bug, and it is visible in seconds with a proper parser or a quick script.<\/p>\n<p>Send a small sample first when the file matters. A ten-row sample confirms the format, and it costs nothing compared with reissuing a broken export to a hundred recipients.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>TL;DR \/ Summary: A CSV export that opens with one column containing everything is a delimiter problem, and a CSV that turns phone numbers into scientific notation is a formatting problem. Fix both at the source rather than repairing the sheet by hand. The one-column problem Opening a CSV and seeing every field crammed into [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":38,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-109","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-converters-codecs","entry","has-media"],"_links":{"self":[{"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/posts\/109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/comments?post=109"}],"version-history":[{"count":1,"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/posts\/109\/revisions"}],"predecessor-version":[{"id":110,"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/posts\/109\/revisions\/110"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/media\/38"}],"wp:attachment":[{"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/media?parent=109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/categories?post=109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.masters-tool.com\/blog\/wp-json\/wp\/v2\/tags?post=109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}