Upload CSV Error Code: incorrect number of columns

SOLVE
Highlighted
New Contributor

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.

CSV image 1.PNGCSV image 2.PNGCSV image 3.PNG

Reply
0 Upvotes
1 Accepted solution

Accepted Solutions
Regular Advisor

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

Reply
0 Upvotes
8 Replies
Regular Advisor

Hi @Nagazzi 

 

Usually that error is from the CSV file not having empty columns included in the rows.

 

So, if you had 3 headings you would need to have at least 2 commas per row.

 

Heading 1, Heading 1, Heading 3
Value 1,,  Value 2,, Value 3,, 

Note the additonal commas after Value 1 which show column 2 and 3 are empty.

 

Or, you could remove the extra headers (on the right) that don't have any data.

 

Alternately you could try uploading the file as Microsoft Excel.

 

Did that work?

Mike

 

 

Reply
0 Upvotes
New Contributor

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.

Reply
0 Upvotes
Regular Advisor

Hi @Nagazzi 

 

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.

Reply
0 Upvotes
New Contributor

Thank you for your reply Mike.

I will look into it and report back the findings. Peace.

Reply
0 Upvotes
New Contributor

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.

Reply
0 Upvotes
Regular Advisor

Hi @Nagazzi 

 

Happy to have a look at your file as a sanity check.

 

I will see if you can send me a files as a private message - if not I will send you my email address.

 

More soon

Mike

Reply
0 Upvotes
New Contributor

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.

Reply
0 Upvotes
Regular Advisor

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

Reply
0 Upvotes