A customer address with a comma shifted the values across a whole row. How do I spot and fix it?
An unquoted comma splits one value into two, so every value after it moves one column along. The converter warns you with the row number and keeps the surplus value under the key __parsed_extra. The fix is to wrap that cell in double quotes in the file and convert again.
What the problem looks like in a real file
Take a small order file with three columns: name, city and total. The first row is fine, but in the second the city is written as Dubai, Marina with no quotes:
name,city,total
Sara,Riyadh,250
Omar,Dubai, Marina,80
By CSV rules the second row holds four values, not three: Omar, then Dubai, then ' Marina', then 80. There are only three headers, so the fourth value has nowhere to go.
What the tool produced
Converting to JSON brought up the note 'Row 3: has 4 fields but 3 were expected. The extra fields were kept under __parsed_extra.' In the output, Omar's object looked like this:
- city is just 'Dubai'.
- total is ' Marina' with a leading space: the district landed where the amount should be.
- The real amount, 80, moved into __parsed_extra.
The row number is 3, not 2, because the header counts as a row. The tool does not guess where the comma should have been; it keeps every value and points at the row so you can decide.
The fix in three steps
- Open the CSV and JSON converter, choose 'CSV to JSON', paste the file, then read the notes under the output and write down the row numbers they mention.
- In the original file, wrap the value containing a comma in double quotes, so the line becomes Omar,"Dubai, Marina",80.
- Paste the corrected file again and check that the notes are gone.
After the correction the tool gave Omar a city of 'Dubai, Marina' and a total of '80', with no notes at all.
Where unquoted commas come from
Well-behaved software quotes any value containing a comma automatically. The problem usually appears when a file is typed by hand, stitched together by pasting text from different places, or exported by an older system that skips the rule. Addresses, descriptions and notes are the usual suspects, because they are free text.
If the file is large and dozens of rows are shifted, it is easier to export it again from the source with a delimiter that never appears in the data, such as Tab or the pipe |, then convert it with the delimiter left on 'Auto-detect'.
Stray spaces after the fix
In our example the value ' Marina' had a leading space, because a space followed the comma. The tool does not strip spaces from values, since they are part of the data. To tidy extra spaces in a text before converting, try the Whitespace cleaner, bearing in mind it works on the whole text, not on a single column.
Common questions
Are the extra values thrown away?
No, they are kept under the key __parsed_extra in the same object, with a note giving the row number.
What if a row has fewer values than headers?
A different note says the row has fewer fields, and the missing fields are simply absent from that object.
Can the tool repair the row by itself?
No, because it cannot know which comma was meant. It points to the row and leaves the decision to you.