How to keep commas, quotes and line breaks inside CSV fields
A comma inside a description belongs to the description. In a conventional comma-separated CSV, enclose that field in double quotation marks. A literal quotation mark inside the field is represented by two quotation marks. A quoted field can also contain a line break. Use a CSV-aware reader to verify the resulting records. Counting commas or counting physical lines will give the wrong answer for perfectly valid files containing these characters.
In this guide
Identify which boundary has failed
Start with the original page and an example that imported incorrectly. Suppose a product named Cable, braided should appear between its stock code and its unit price. If the workbook shows Cable in the description column, braided in the price column and an extra numeric field to its right, it probably treated the internal comma as a separator. If the entire record appears in a single cell, the first issue is more likely the chosen field delimiter.
Compare one problematic row with its neighbor
Find an ordinary product immediately above the problem. Confirm the number and order of expected columns against the header. Then inspect both records in a plain text editor. A simple description such as Adapter does not need quotes around it in a comma-delimited file, while Cable, braided does. Inconsistent visual use of quotes can therefore be correct. What matters is whether the parser recovers the intended boundaries for both rows.
Trace the record from the PDF through the editable PDF to CSV preview before assuming an export problem. If the preview already puts braided in a separate editable cell, CSV quoting cannot know that the cells belong together. Correct the table structure or the source extraction before export, or repair the structured records in a spreadsheet. If the preview contains the complete description in one cell and the file quotes it correctly, investigate the destination importer.
Understand a literal quote inside a description
Imagine a catalog entry with this displayed description: Sample labeled "North". The two quotation marks around North are part of the product name. A CSV writer surrounds the entire field with double quotes and doubles each literal quote inside it. A reader honoring those rules restores a single pair of inner quotes. Do not remove all successive quotation marks with a global replace. Some of them encode the very punctuation the destination needs.
Distinguish a quote seen in a cell from syntax seen in text
Copying a cell from a spreadsheet can introduce clipboard formatting. To inspect the file's syntax, use a plain text editor to view the saved CSV itself. To inspect the meaning of the parsed field, use the import preview or a parser. At the file level, a sequence of two quotes may represent one character of content. At the cell level, the reader should display the character itself without CSV serialization syntax.
Empty text, blank fields and placeholders
A delimiter immediately followed by another delimiter represents an empty field between them. Some writers represent an empty string with quoted empty text instead. How the receiving system maps those to blank, empty string or missing value depends on its import rules. A field containing a visible dash is different again. Preserve the distinction the source actually makes unless the destination documentation explicitly requests a conversion and you understand its effect.
Interpret multiline records correctly
Consider a shipping note containing two lines, Dock entrance followed by Ask for receiving. A proper quoted field can preserve that internal break as part of one record. A text editor shows the record occupying two physical lines. A parser should return one shipping note and one row. Splitting the file into strings at every line ending creates an extra incomplete row and usually misaligns the columns that follow.
Work out whether the PDF really contains a multiline field
A PDF may wrap a long description merely because its display box is narrow. That visual wrap may represent one logical phrase. In another file, a separate address line or installation instruction may deliberately carry meaning. Compare the same layout across several records. Ask whether replacing the break with a space changes downstream use. A product name wrapped for print may work well on one line. A formatted shipping label may require its original divisions.
If the destination rejects multiline fields, agree on a transformation before removing breaks. You could replace internal line breaks in the notes column with a space for a simple import. Preserve a copy with the original note and explain the change to anyone responsible for fulfillment. Avoid deleting all line endings from the file because the separators between whole records are line endings too. A parser can distinguish a break inside a field from the boundary after a record.
Test with a small difficult sample
Build a test using one plain item, one item containing a comma, one containing a literal quote, one containing both, and one with a deliberate line break. Give each record a unique fictional identifier and fill the last column with a recognizable amount. Include a record with a genuinely blank middle field so a shifted value cannot go unnoticed. Choose test descriptions with the punctuation the receiving system actually encounters.
Define expected results before opening the file
Write down the expected number of logical records, expected number of fields in each record, and exact decoded text for the two hardest cases. A successful test means the import produces all identifiers once, the last column still contains the correct amounts, and the descriptions match their intended characters. An accepted upload or a clean-looking preview is weaker evidence. Some importers silently discard an extra field or omit a malformed row.
The RFC 4180 description of common CSV formatting is a reference for the quoting convention used in this example. Actual import tools can support different dialects or apply additional rules to fields after parsing them. Use the recipient's requirements for the delivery contract. The RFC helps explain a field boundary. It does not promise that every business system accepts multiline notes or optional headers.
Repair malformed data without guessing missing content
Suppose the CSV contains A7,Cable, braided,4.25. If the known schema is code, description, and price, a person familiar with the catalogue might infer the middle two fragments belong together. But a wider schema could include a color or category column. Inspect the original invoice or stock list before joining fragments. Repair the source record, then regenerate CSV using a writer that understands field quoting. This is more reliable than adding quotation marks by hand to many rows.
Locate the first bad record
An unclosed quote near the start of a file may cause subsequent lines to be interpreted as part of one long field. An error shown at the bottom can originate much earlier. Find the last successfully parsed identifier, inspect the next original record, and look for a field containing unbalanced punctuation or a truncated export. Retain complete record boundaries when isolating a reproducer, particularly when the suspect description spans multiple lines.
Maintain the original text while cleaning
Keep an untouched export and identify repaired copies by purpose. Record the affected identifier, the original cell content, the intended correction and the reason. When many rows share a defect, reproduce the transformation on a few examples first. Confirm ordinary rows and blank values survive. If the destination requires removal of internal quotes for a particular business field, treat that as a separate documented policy decision after parsing the original text.
Deliver the corrected CSV with evidence
Import the final serialized file through the same route the recipient uses. Count its logical records and compare its final amount column against a calculation from the verified source. If your file has quantities and discounts, compare a few rows individually alongside the total. Two incorrectly shifted amounts can offset each other. Include a direct comparison of one description with accents, a quote, and embedded punctuation where the original supplies a suitable example.
Answer the recurring questions
Can you switch to a semicolon to avoid quoting? That may reduce commas acting as field separators, but descriptions can contain semicolons too. The destination must explicitly support the chosen separator. For practical examples involving decimal commas, see the guide to field delimiters and decimal separators. Can you count spreadsheet rows instead? Count data rows after a successful parse, excluding the header and any totals that the destination treats separately.
Does double-quoting ensure text stays text in Excel? Field quoting tells a parser where a field starts and ends. A spreadsheet can subsequently convert a code into a number or date. Set identifier columns to text explicitly in the import step. A description with perfect punctuation can still sit beside an altered invoice number. Check both semantic content and table structure as independent parts of your acceptance test.
Build a repeatable supplier check
Save the small fictional test file, expected decoded values and destination import settings together. When the export tool or destination changes, run it again before processing real supplier records. If a policy changes so multiline notes must become single-line text, update the expected results and keep the previous test for reference. This gives colleagues a precise explanation of the changed output and a safe way to diagnose the next unusual description.
Verify a supplier's downstream field limits
A parser may successfully read a long quoted note while the destination product database accepts only a short description. Compare the actual saved record after the import with the decoded CSV value. If the destination truncates the note, track that as a field length issue rather than another quoting error. Ask whether the recipient provides a separate notes field or supports a larger text column. Preserve the complete source description until an authorized transformation is agreed and tested.