Upload CSV Error Code: incorrect number of columns
resolver
Hello all, when uploading a csv file I always get one error. The incorrect number of columns: Header (35) and Row (24) sizes don't match. The header or column that is listed in the download file always has an a problem with the very last column in my spread sheet, and it does have a name, and each row does have a value. Since this is a new upload every row value (the word: new) is the same for each row.
I've also tried deleting the last column and that data for each row under that column, but then I get another error in the last column.
Upload CSV Error Code: incorrect number of columns
resolver
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.
Upload CSV Error Code: incorrect number of columns
resolver
I'm the OP on this thread, and admittedly I haven't used HS much since this post, that being said I do need to use it again, and I think I'm having the same issue I was having back in Feb 2019. Going through this thread and reading my old emails I've been able to open my .csv file with notepad but I don't know how to spot the errors. Specifically, how the commas separate the columns.
Upload CSV Error Code: incorrect number of columns
resolver
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.
Upload CSV Error Code: incorrect number of columns
resolver
Guys looking at my error log, I don't know where to start, I have a learning disability. And have a deadline to get this 1,291 errors fix. Can some please advise me on this thanks
Upload CSV Error Code: incorrect number of columns
resolver
Hello @SSkriavek, would love to guide you and provide more details on how to fix those errors, could you please provide us more details? The more information, screenshots, and details you can provide, the better I can advise on the next steps.
Upload CSV Error Code: incorrect number of columns
resolver
I'm sure you had a good look at the screen shots of my csv, that is the entire spreadsheet. I did not leave out any columns or rows (the entire sheet is 35x24). I only redacted the data in the sheet. There are some cells which do not have any values in columns AB, AD, AE, AG, and AH. As per your example do I need to fill those empty cells with two commas each? I think I'm misundertanding you here.
When I'm uploading my csv the next step in the process is to map the columns to the contact properties. At the bottom of that screen there is a check box that says: don't import data in unmatched columns, would this also take care of a column with some cells that are blank? I know I'm not understanding this properly.
It's going to be messy but... if you open the CSV file in something other than Excel you need to be able to see the exact number of columns - separated by commas.
What you're doing in HubSpot sounds correct.
Typically we find the CSV file is the issue. It even varies from Mac to PC, Excel to Google Sheets.
Also, you may want to check your file to see there are any empty rows after your data.
Using our example from before:
Heading 1, Heading 1, Heading 3
Value 1,,
Value 2,,
Value 3,,
,
That last row, with just a single comma, can throw out the CSV file because the importer can only see 2 empty columns in row 4.
In Excel, or Google Sheets, your spreadseet will seem normal.
It's worth doing a sanity check by opening your CSV with a text editor like Notepad (PC) or TextEdit (Mac). Do NOT try and check it in Microsoft Word - it will try and help you and make things worse.
Let me know how you get on... Mike
p.s. in answer to your questions you don't need to add any commas in your spreadsheet. This is only visible in the CSV file.
Upload CSV Error Code: incorrect number of columns
resolver
Hello again Mike, sorry about the late response, work has been kicking my butt lately. I did open the csv in notepad and all the columns at the top only have one comma separating them. Sometimes there are spaces after a comma, but I doubt that would have an effect. The very last column doesn't have a comma at the end.
When looking at the data in the rows I sometimes see two commas for (row) entries in the last column but I also see them with one comma. I assume these are a problem?
The very last column and row do not have any comma at the very end, just two commas before the data.
If it's allowed I can perhaps provide a dropbox or google drive link to my csv, if I can send you a private message.
Upload CSV Error Code: incorrect number of columns
resolver
Will do Mike. I should also mention what exactly happens with the file. First, it is created as an excel, then it is converted into a google sheet, then downloaded as a CSV, and finally uploaded to hubspot.