Hey everyone 👋
I'm trying to learn more about real-world CRM setups by testing trials. My goal is to compare how Salesforce and HubSpot handle importing actual contact data from a simple CSV. I want to do it properly, not just with dummy data.
I have a small AWS RDS (PostgreSQL) instance with a `contacts` table. I exported it to a CSV. My plan was to use the CRM's web import tool, but I hit a snag with the CSV formatting. The date fields and notes with commas broke the import.
Here's a sample of my CSV's first few lines:
```csv
id,first_name,last_name,email,created_at,notes
1,John,Doe,john@example.com,2023-10-26 08:30:00,"Note one, with a comma"
2,Jane,Smith,jane@example.com,2023-10-25,Note without comma
```
I tried wrapping the notes field in quotes, but Salesforce still seemed to get confused by the timestamp format. HubSpot accepted the timestamp but didn't parse it to its own date field automatically.
My question: Is there a standard pre-processing step you all do? Like running the CSV through a Python script to format dates and escape special characters before any CRM import? Or do the big CRMs have tools for this that I'm missing?
I'm doing this manually now, but thinking about automating it for larger datasets, maybe with a Lambda function. Would love to hear how you handle real data imports for fair testing.
You're right to suspect you need a pre-processing step. I always run CSV exports through a script to normalize dates and escape text fields before any CRM import. The big platforms expect specific formats, and their web tools are often brittle with real-world data.
For your specific case with Salesforce and HubSpot, the timestamp issue is common. Salesforce expects ISO 8601 format (YYYY-MM-DDTHH:MM:SSZ), while HubSpot is more flexible but still needs you to map the field correctly during import. Your note field with commas requires proper quoting, and double quotes inside the field need to be escaped with another double quote.
I use a simple Python script with the `pandas` library for this. It loads the CSV, standardizes the date column to separate date and time if needed, and ensures all text fields are properly quoted. You can also use `csvkit` for command-line processing. The alternative is to manually edit in a proper CSV-aware editor like VS Code with the CSV extension, but that doesn't scale.
Do you want me to share the basic structure of my normalization script? It's about 15 lines and handles the quoting and date parsing you're running into.
Data > opinions
Agree on pre-processing. For ad-hoc tasks I skip pandas and just use a one-liner with `csvformat` from csvkit:
```bash
csvformat -U 1 -M '^"' input.csv > output.csv
```
The -U 1 forces quoting on all fields. Handles commas in text.
But for repeated imports, the script approach is better. You'll also need to strip null bytes. Databases dump them sometimes and CRMs choke. Add this to your script:
```python
df = df.replace('x00', '', regex=True)
```
null
The null byte point is critical for Salesforce specifically. Their import tools will silently fail or truncate data at x00 characters, which Postgres outputs for empty text fields more often than people realize.
However, forcing quoting on all fields with `-U 1` can cause its own mapping issues in HubSpot's importer. It sometimes misinterprets a fully-quoted CSV, treating the quotes as part of the string value. You might end up with literal quote marks in your contact notes. I've found quoting only where necessary (text fields with commas, line breaks) more reliable for that platform.
For repeated processes, a script is mandatory, but I'd also recommend validating the character encoding. I've seen UTF-8 with BOM break imports that were otherwise clean.
Show me the data