The HubSpot contacts export: a hundred columns wide, timestamps with a timezone, and semicolons inside cells
An export of contacts, companies or deals from HubSpot has one row per record and one column per property you chose — which, with the defaults, is dozens and can be over a hundred. Timestamps carry the date, the time and a timezone; multi-value properties such as associated companies or checkbox fields join their values with semicolons in one cell; and phone numbers keep their leading plus. Most of what anyone does with the file is keep the columns that matter and the rows that match, which the tools here do without retyping a value.
What the export usually contains
The columns are the property labels, so the headings read as First Name, Email, Lifecycle Stage, Create Date rather than internal names. A Record ID column identifies the row and is the key for merging back into HubSpot or against another export. Dates and times are written together with the timezone the account is set to, which Excel reads as text unless the format happens to match its locale.
A property that holds several values — associated companies, associated deals, multiple checkboxes — is one cell with the values separated by semicolons, in the same column as records that hold one. Large exports are prepared in the background and arrive as a link to a zip rather than as a direct download.
Keep the columns you need
Drop the file on the delete-columns tool here, pick the properties nobody will read, and download a file that is a few columns wide instead of a hundred. The tool shows the new width before you save, and every remaining value is as HubSpot wrote it — the plus on a phone number, the zone on a timestamp, the full record id.
Then rows: the filter tool keeps the contacts whose lifecycle stage, owner or country matches, and shows the count. For anything the filter’s rules cannot say, the SQL console reads the file in place.
Semicolons, duplicates and merges
A cell with several semicolon-separated values stays one cell here; to give each value its own column, the split-column tool splits on the semicolon into as many columns as the widest cell needs. Contacts that appear twice — the same email with different capitalisation, a trailing space — are found by the duplicate remover with its capitalisation and spacing options on, and it shows what it would remove before removing it.
To compare this month’s export with last month’s, or a HubSpot export with a list from elsewhere, merge on the email or the record id here and see which rows are in one file and not the other.
Questions
- Why does my HubSpot export have so many columns?
- Because every property selected for the export is a column, and the default selection is wide. Delete the ones you do not need here and download a narrow file; the values in the rest are untouched.
- What are the semicolons inside some cells?
- A property with several values — associated companies, checkbox fields — is written as one cell with the values separated by semicolons. Split that column on the semicolon here to give each value its own column.
- Why does Excel show the dates as text?
- Because they carry a time and a timezone in a format Excel does not recognise for your locale. They stay as written here; the SQL console can cut them down to a plain date in one expression.
- How do I find contacts that are in the file twice?
- Run the duplicate remover here on the email column with the capitalisation and spacing options on. It then matches emails that differ only by case or spacing, shows the groups, and lets you choose which row survives.
- Can I compare two exports?
- Yes. The compare tool lists the rows added, removed and changed between two exports of the same list, keyed on the record id, cell by cell.
- Is my export uploaded anywhere?
- No. The columns you do not need are dropped inside this page, so the contacts in your HubSpot export are trimmed on your own machine rather than handed to another service first.