Upload CSV Error Code: incorrect number of columns

[Nagazzi]

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.

[Mike_Eastwood]

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

[Nagazzi]

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.

[Mike_Eastwood]

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.

[Nagazzi]

Thank you for your reply Mike.

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

[Nagazzi]

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.

[Mike_Eastwood]

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

[Nagazzi]

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.

[Mike_Eastwood · Accepted solution]

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

[SSkriavek]

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

[PamCotton]

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.

Thank you,

Pam

[SSkriavek]

Hi am new to this, and have a dead line to fix 1,291 errors. Could you advise the best way I can do this??