We found the issue(s) - some spreadsheets don’t automatically save the CSV (Comma Separated Values) file with quote marks around text cells… and if a text cell contains any commas then the import will fail.
For example, here’s a simple CSV (not Excel) file:
Column 1, Column 2, Column 3
1, Line of text, GOOD
2, Another, line of text, BAD
3, Yet another line of text, GOOD
Apart from the incorrect use of a comma Row 2 will fail. The importer assumes there’s 4 columns of data because it’s “Comma Separated” and our header only has 3 columns.
So, to solve this wrap your text in Quotes like this:
Column 1, Column 2, Column 3
1, "Line of text", GOOD
2, "Another, line of text", GOOD
3, "Yet another line of text", GOOD
The Quotes make Row 2 into 3 columns as intended.
Warning
If your text contains quotes the you will need to “escape them” or change them to something else.
-
“This has “quote marks” in the data”
will fail
-
“This has \“quote marks\” in the data”
will work (quote marks escaped)
-
“This has ‘quote marks’ in the data”
will work (single quotes used instead of double quotes)
Important
The examples above look at the CSV file - not in Excel (or other spreadsheet). We’re looking at the raw data using a plain text editor NOT a spreadsheet and NOT Microsoft Word.
How you can check your data
Open the CSV file with Notepad (PC) or TextEdit (Mac). You should see the quotes around your text if there are commas in the text.
Double check your data
Once you’ve imported your data make sure you check it HubSpot.
The Error Log is your friend
If you import your data and you get errors download the error file - the instructions are helpful to find what went wrong and you can fix it.
Have fun… even though I know it’s not
Mike