516 lines
17 KiB
Markdown
516 lines
17 KiB
Markdown
# Dealers CSV Import Guide
|
||
|
||
This guide explains how to format and import dealer data from a CSV file into the PostgreSQL `dealers` table.
|
||
|
||
## ⚠️ Important: Auto-Generated Columns
|
||
|
||
**DO NOT include these columns in your CSV file** - they are automatically generated by the database:
|
||
|
||
- ❌ `dealer_id` - Auto-generated UUID (e.g., `550e8400-e29b-41d4-a716-446655440000`)
|
||
- ❌ `created_at` - Auto-generated timestamp (current time on import)
|
||
- ❌ `updated_at` - Auto-generated timestamp (current time on import)
|
||
- ❌ `is_active` - Defaults to `true`
|
||
|
||
Your CSV should have **exactly 44 columns** (the data columns listed below).
|
||
|
||
## Table of Contents
|
||
- [CSV File Format Requirements](#csv-file-format-requirements)
|
||
- [Column Mapping](#column-mapping)
|
||
- [Preparing Your CSV File](#preparing-your-csv-file)
|
||
- [Import Methods](#import-methods)
|
||
- [Troubleshooting](#troubleshooting)
|
||
|
||
---
|
||
|
||
## CSV File Format Requirements
|
||
|
||
### File Requirements
|
||
- **Format**: CSV (Comma-Separated Values)
|
||
- **Encoding**: UTF-8
|
||
- **Header Row**: Required (first row must contain column names)
|
||
- **Delimiter**: Comma (`,`)
|
||
- **Text Qualifier**: Double quotes (`"`) for fields containing commas or special characters
|
||
|
||
### Required Columns (in exact order)
|
||
|
||
**Important Notes:**
|
||
- **DO NOT include** `dealer_id`, `created_at`, `updated_at`, or `is_active` in your CSV file
|
||
- These columns will be automatically generated by the database:
|
||
- `dealer_id`: Auto-generated UUID
|
||
- `created_at`: Auto-generated timestamp (current time)
|
||
- `updated_at`: Auto-generated timestamp (current time)
|
||
- `is_active`: Defaults to `true`
|
||
|
||
Your CSV file must have these **44 columns** in the following order:
|
||
|
||
1. `sales_code`
|
||
2. `service_code`
|
||
3. `gear_code`
|
||
4. `gma_code`
|
||
5. `region`
|
||
6. `dealership`
|
||
7. `state`
|
||
8. `district`
|
||
9. `city`
|
||
10. `location`
|
||
11. `city_category_pst`
|
||
12. `layout_format`
|
||
13. `tier_city_category`
|
||
14. `on_boarding_charges`
|
||
15. `date`
|
||
16. `single_format_month_year`
|
||
17. `domain_id`
|
||
18. `replacement`
|
||
19. `termination_resignation_status`
|
||
20. `date_of_termination_resignation`
|
||
21. `last_date_of_operations`
|
||
22. `old_codes`
|
||
23. `branch_details`
|
||
24. `dealer_principal_name`
|
||
25. `dealer_principal_email_id`
|
||
26. `dp_contact_number`
|
||
27. `dp_contacts`
|
||
28. `showroom_address`
|
||
29. `showroom_pincode`
|
||
30. `workshop_address`
|
||
31. `workshop_pincode`
|
||
32. `location_district`
|
||
33. `state_workshop`
|
||
34. `no_of_studios`
|
||
35. `website_update`
|
||
36. `gst`
|
||
37. `pan`
|
||
38. `firm_type`
|
||
39. `prop_managing_partners_directors`
|
||
40. `total_prop_partners_directors`
|
||
41. `docs_folder_link`
|
||
42. `workshop_gma_codes`
|
||
43. `existing_new`
|
||
44. `dlrcode`
|
||
|
||
---
|
||
|
||
## Column Mapping
|
||
|
||
### Column Details
|
||
|
||
| Column Name | Type | Required | Notes |
|
||
|------------|------|----------|-------|
|
||
| `sales_code` | String(50) | No | Sales code identifier |
|
||
| `service_code` | String(50) | No | Service code identifier |
|
||
| `gear_code` | String(50) | No | Gear code identifier |
|
||
| `gma_code` | String(50) | No | GMA code identifier |
|
||
| `region` | String(50) | No | Geographic region |
|
||
| `dealership` | String(255) | No | Dealership business name |
|
||
| `state` | String(100) | No | State name |
|
||
| `district` | String(100) | No | District name |
|
||
| `city` | String(100) | No | City name |
|
||
| `location` | String(255) | No | Location details |
|
||
| `city_category_pst` | String(50) | No | City category (PST) |
|
||
| `layout_format` | String(50) | No | Layout format |
|
||
| `tier_city_category` | String(100) | No | TIER City Category |
|
||
| `on_boarding_charges` | Decimal | No | Numeric value (e.g., 1000.50) |
|
||
| `date` | Date | No | Format: YYYY-MM-DD (e.g., 2014-09-30) |
|
||
| `single_format_month_year` | String(50) | No | Format: Sep-2014 |
|
||
| `domain_id` | String(255) | No | Email domain (e.g., dealer@royalenfield.com) |
|
||
| `replacement` | String(50) | No | Replacement status |
|
||
| `termination_resignation_status` | String(255) | No | Termination/Resignation status |
|
||
| `date_of_termination_resignation` | Date | No | Format: YYYY-MM-DD |
|
||
| `last_date_of_operations` | Date | No | Format: YYYY-MM-DD |
|
||
| `old_codes` | String(255) | No | Old code references |
|
||
| `branch_details` | Text | No | Branch information |
|
||
| `dealer_principal_name` | String(255) | No | Principal's full name |
|
||
| `dealer_principal_email_id` | String(255) | No | Principal's email |
|
||
| `dp_contact_number` | String(20) | No | Contact phone number |
|
||
| `dp_contacts` | String(20) | No | Additional contacts |
|
||
| `showroom_address` | Text | No | Full showroom address |
|
||
| `showroom_pincode` | String(10) | No | Showroom postal code |
|
||
| `workshop_address` | Text | No | Full workshop address |
|
||
| `workshop_pincode` | String(10) | No | Workshop postal code |
|
||
| `location_district` | String(100) | No | Location/District |
|
||
| `state_workshop` | String(100) | No | State for workshop |
|
||
| `no_of_studios` | Integer | No | Number of studios (default: 0) |
|
||
| `website_update` | String(10) | No | Yes/No value |
|
||
| `gst` | String(50) | No | GST number |
|
||
| `pan` | String(50) | No | PAN number |
|
||
| `firm_type` | String(100) | No | Type of firm (e.g., Proprietorship) |
|
||
| `prop_managing_partners_directors` | String(255) | No | Proprietor/Partners/Directors names |
|
||
| `total_prop_partners_directors` | String(255) | No | Total count or names |
|
||
| `docs_folder_link` | Text | No | Google Drive or document folder URL |
|
||
| `workshop_gma_codes` | String(255) | No | Workshop GMA codes |
|
||
| `existing_new` | String(50) | No | Existing/New status |
|
||
| `dlrcode` | String(50) | No | Dealer code |
|
||
|
||
---
|
||
|
||
## Preparing Your CSV File
|
||
|
||
### Step 1: Create/Edit Your CSV File
|
||
|
||
1. **Open your CSV file** in Excel, Google Sheets, or a text editor
|
||
2. **Remove auto-generated columns** (if present):
|
||
- ❌ **DO NOT include**: `dealer_id`, `created_at`, `updated_at`, `is_active`
|
||
- ✅ These will be automatically generated by the database
|
||
3. **Ensure the header row** matches the column names exactly (see [Column Mapping](#column-mapping))
|
||
4. **Verify column order** - columns must be in the exact order listed above (44 columns total)
|
||
5. **Check data formats**:
|
||
- Dates: Use `YYYY-MM-DD` format (e.g., `2014-09-30`)
|
||
- Numbers: Use decimal format for `on_boarding_charges` (e.g., `1000.50`)
|
||
- Empty values: Leave cells empty (don't use "NULL" or "N/A" as text)
|
||
|
||
### Step 2: Handle Special Characters
|
||
|
||
- **Commas in text**: Wrap the entire field in double quotes
|
||
- Example: `"No.335, HVP RR Nagar Sector B"`
|
||
- **Quotes in text**: Use double quotes to escape: `""quoted text""`
|
||
- **Newlines in text**: Wrap field in double quotes
|
||
|
||
### Step 3: Date Formatting
|
||
|
||
Ensure dates are in `YYYY-MM-DD` format:
|
||
- ✅ Correct: `2014-09-30`
|
||
- ❌ Wrong: `30-Sep-14`, `09/30/2014`, `30-09-2014`
|
||
|
||
### Step 4: Save the File
|
||
|
||
1. **Save as CSV** (UTF-8 encoding)
|
||
2. **File location**: Save to an accessible path (e.g., `C:/Users/COMP/Downloads/DEALERS_CLEAN.csv`)
|
||
3. **File name**: Use a descriptive name (e.g., `DEALERS_CLEAN.csv`)
|
||
|
||
### Sample CSV Format
|
||
|
||
**Important:** Your CSV should **NOT** include `dealer_id`, `created_at`, `updated_at`, or `is_active` columns. These are auto-generated.
|
||
|
||
```csv
|
||
sales_code,service_code,gear_code,gma_code,region,dealership,state,district,city,location,city_category_pst,layout_format,tier_city_category,on_boarding_charges,date,single_format_month_year,domain_id,replacement,termination_resignation_status,date_of_termination_resignation,last_date_of_operations,old_codes,branch_details,dealer_principal_name,dealer_principal_email_id,dp_contact_number,dp_contacts,showroom_address,showroom_pincode,workshop_address,workshop_pincode,location_district,state_workshop,no_of_studios,website_update,gst,pan,firm_type,prop_managing_partners_directors,total_prop_partners_directors,docs_folder_link,workshop_gma_codes,existing_new,dlrcode
|
||
5124,5125,5573,9430,S3,Accelerate Motors,Karnataka,Bengaluru,Bengaluru,RAJA RAJESHWARI NAGAR,A+,A+,Tier 1 City,,2014-09-30,Sep-2014,acceleratemotors.rrnagar@dealer.royalenfield.com,,,,,,,N. Shyam Charmanna,shyamcharmanna@yahoo.co.in,7022049621,7022049621,"No.335, HVP RR Nagar Sector B, Ideal Homes Town Ship, Bangalore - 560098, Dist – Bangalore, Karnataka",560098,"Works Shop No.460, 80ft Road, 2nd Phase R R Nagar, Bangalore - 560098, Dist – Bangalore, Karnataka",560098,Bangalore,Karnataka,0,Yes,29ARCPS1311D1Z6,ARCPS1311D,Proprietorship,CHARMANNA SHYAM NELLAMAKADA,CHARMANNA SHYAM NELLAMAKADA,https://drive.google.com/drive/folders/1sGtg3s1h9aBXX9fhxJufYuBWar8gVvnb,,,3386
|
||
```
|
||
|
||
**What gets auto-generated:**
|
||
- `dealer_id`: `550e8400-e29b-41d4-a716-446655440000` (example UUID)
|
||
- `created_at`: `2025-01-20 10:30:45.123` (current timestamp)
|
||
- `updated_at`: `2025-01-20 10:30:45.123` (current timestamp)
|
||
- `is_active`: `true`
|
||
|
||
---
|
||
|
||
## Import Methods
|
||
|
||
### Method 1: PostgreSQL COPY Command (Recommended - If CSV has 44 columns)
|
||
|
||
**Use this if your CSV does NOT include `dealer_id`, `created_at`, `updated_at`, `is_active` columns.**
|
||
|
||
**Prerequisites:**
|
||
- PostgreSQL client (psql) installed
|
||
- Access to PostgreSQL server
|
||
- CSV file path accessible from PostgreSQL server
|
||
|
||
**Steps:**
|
||
|
||
1. **Connect to PostgreSQL:**
|
||
```bash
|
||
psql -U your_username -d royal_enfield_workflow -h localhost
|
||
```
|
||
|
||
2. **Run the COPY command:**
|
||
|
||
**Note:** The COPY command explicitly lists only the columns from your CSV. The following columns are **automatically handled by the database** and should **NOT** be in your CSV:
|
||
- `dealer_id` - Auto-generated UUID
|
||
- `created_at` - Auto-generated timestamp
|
||
- `updated_at` - Auto-generated timestamp
|
||
- `is_active` - Defaults to `true`
|
||
|
||
```sql
|
||
\copy public.dealers(
|
||
sales_code,
|
||
service_code,
|
||
gear_code,
|
||
gma_code,
|
||
region,
|
||
dealership,
|
||
state,
|
||
district,
|
||
city,
|
||
location,
|
||
city_category_pst,
|
||
layout_format,
|
||
tier_city_category,
|
||
on_boarding_charges,
|
||
date,
|
||
single_format_month_year,
|
||
domain_id,
|
||
replacement,
|
||
termination_resignation_status,
|
||
date_of_termination_resignation,
|
||
last_date_of_operations,
|
||
old_codes,
|
||
branch_details,
|
||
dealer_principal_name,
|
||
dealer_principal_email_id,
|
||
dp_contact_number,
|
||
dp_contacts,
|
||
showroom_address,
|
||
showroom_pincode,
|
||
workshop_address,
|
||
workshop_pincode,
|
||
location_district,
|
||
state_workshop,
|
||
no_of_studios,
|
||
website_update,
|
||
gst,
|
||
pan,
|
||
firm_type,
|
||
prop_managing_partners_directors,
|
||
total_prop_partners_directors,
|
||
docs_folder_link,
|
||
workshop_gma_codes,
|
||
existing_new,
|
||
dlrcode
|
||
)
|
||
FROM 'C:/Users/COMP/Downloads/DEALERS_CLEAN.csv'
|
||
WITH (
|
||
FORMAT csv,
|
||
HEADER true,
|
||
ENCODING 'UTF8'
|
||
);
|
||
```
|
||
|
||
**What happens:**
|
||
- `dealer_id` will be automatically generated as a UUID for each row
|
||
- `created_at` will be set to the current timestamp
|
||
- `updated_at` will be set to the current timestamp
|
||
- `is_active` will default to `true`
|
||
|
||
3. **Verify import:**
|
||
```sql
|
||
SELECT COUNT(*) FROM dealers;
|
||
SELECT * FROM dealers LIMIT 5;
|
||
```
|
||
|
||
### Method 2: Using Temporary Table (If CSV has 48 columns including auto-generated ones)
|
||
|
||
**Use this if your CSV includes `dealer_id`, `created_at`, `updated_at`, `is_active` columns and you're getting errors.**
|
||
|
||
This method uses a temporary table to import the CSV, then inserts into the actual table while ignoring the auto-generated columns:
|
||
|
||
```sql
|
||
-- Step 1: Create temporary table
|
||
CREATE TEMP TABLE dealers_temp (
|
||
dealer_id TEXT,
|
||
sales_code TEXT,
|
||
service_code TEXT,
|
||
-- ... (all 48 columns as TEXT)
|
||
);
|
||
|
||
-- Step 2: Import CSV into temp table
|
||
\copy dealers_temp FROM 'C:/Users/COMP/Downloads/DEALERS_CLEAN.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
|
||
|
||
-- Step 3: Insert into actual table (ignoring dealer_id, created_at, updated_at, is_active)
|
||
INSERT INTO public.dealers (
|
||
sales_code,
|
||
service_code,
|
||
-- ... (only the 44 data columns)
|
||
)
|
||
SELECT
|
||
NULLIF(sales_code, ''),
|
||
NULLIF(service_code, ''),
|
||
-- ... (convert and handle empty strings)
|
||
FROM dealers_temp
|
||
WHERE sales_code IS NOT NULL OR dealership IS NOT NULL; -- Skip completely empty rows
|
||
|
||
-- Step 4: Clean up
|
||
DROP TABLE dealers_temp;
|
||
```
|
||
|
||
**See `DEALERS_CSV_IMPORT_FIX.sql` for the complete working script.**
|
||
|
||
### Method 3: Using pgAdmin
|
||
|
||
1. Open pgAdmin and connect to your database
|
||
2. Right-click on `dealers` table → **Import/Export Data**
|
||
3. Select **Import**
|
||
4. Configure:
|
||
- **Filename**: Browse to your CSV file
|
||
- **Format**: CSV
|
||
- **Header**: Yes
|
||
- **Encoding**: UTF8
|
||
- **Delimiter**: Comma
|
||
5. Click **OK** to import
|
||
|
||
### Method 4: Using Node.js Script
|
||
|
||
Create a script to import CSV programmatically (useful for automation):
|
||
|
||
```typescript
|
||
import { sequelize } from '../config/database';
|
||
import { QueryTypes } from 'sequelize';
|
||
import * as fs from 'fs';
|
||
import * as path from 'path';
|
||
import * as csv from 'csv-parser';
|
||
|
||
async function importDealersFromCSV(csvFilePath: string) {
|
||
const dealers: any[] = [];
|
||
|
||
return new Promise((resolve, reject) => {
|
||
fs.createReadStream(csvFilePath)
|
||
.pipe(csv())
|
||
.on('data', (row) => {
|
||
dealers.push(row);
|
||
})
|
||
.on('end', async () => {
|
||
try {
|
||
// Bulk insert dealers
|
||
// Implementation depends on your needs
|
||
console.log(`Imported ${dealers.length} dealers`);
|
||
resolve(dealers);
|
||
} catch (error) {
|
||
reject(error);
|
||
}
|
||
});
|
||
});
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
## Troubleshooting
|
||
|
||
### Common Issues and Solutions
|
||
|
||
#### 1. **"Column count mismatch" Error**
|
||
- **Problem**: CSV has different number of columns than expected
|
||
- **Solution**:
|
||
- Verify your CSV has exactly **44 columns** (excluding header)
|
||
- **Remove** `dealer_id`, `created_at`, `updated_at`, and `is_active` if they exist in your CSV
|
||
- These columns are auto-generated and should NOT be in the CSV file
|
||
|
||
#### 2. **"Invalid date format" Error**
|
||
- **Problem**: Dates not in `YYYY-MM-DD` format
|
||
- **Solution**: Convert dates to `YYYY-MM-DD` format (e.g., `2014-09-30`)
|
||
|
||
#### 3. **"Encoding error" or "Special characters not displaying correctly**
|
||
- **Problem**: CSV file not saved in UTF-8 encoding
|
||
- **Solution**:
|
||
- In Excel: Save As → CSV UTF-8 (Comma delimited) (*.csv)
|
||
- In Notepad++: Encoding → Convert to UTF-8 → Save
|
||
|
||
#### 4. **"Permission denied" Error (COPY command)**
|
||
- **Problem**: PostgreSQL server cannot access the file path
|
||
- **Solution**:
|
||
- Use absolute path with forward slashes: `C:/Users/COMP/Downloads/DEALERS_CLEAN.csv`
|
||
- Ensure file permissions allow read access
|
||
- For remote servers, upload file to server first
|
||
|
||
#### 5. **"Duplicate key" Error**
|
||
- **Problem**: Trying to import duplicate records
|
||
- **Solution**:
|
||
- Use `ON CONFLICT` handling in your import
|
||
- Or clean CSV to remove duplicates before import
|
||
|
||
#### 6. **Empty values showing as "NULL" text**
|
||
- **Problem**: CSV contains literal "NULL" or "N/A" strings
|
||
- **Solution**: Replace with empty cells in CSV
|
||
|
||
#### 7. **Commas in address fields breaking import**
|
||
- **Problem**: Address fields contain commas not properly quoted
|
||
- **Solution**: Wrap fields containing commas in double quotes:
|
||
```csv
|
||
"No.335, HVP RR Nagar Sector B, Ideal Homes Town Ship"
|
||
```
|
||
|
||
### Pre-Import Checklist
|
||
|
||
- [ ] CSV file saved in UTF-8 encoding
|
||
- [ ] **Removed** `dealer_id`, `created_at`, `updated_at`, and `is_active` columns (if present)
|
||
- [ ] Header row matches column names exactly
|
||
- [ ] All 44 columns present in correct order
|
||
- [ ] Dates formatted as `YYYY-MM-DD`
|
||
- [ ] Numeric fields contain valid numbers (or are empty)
|
||
- [ ] Text fields with commas are wrapped in quotes
|
||
- [ ] File path is accessible from PostgreSQL server
|
||
- [ ] Database connection credentials are correct
|
||
|
||
### Verification Queries
|
||
|
||
After import, run these queries to verify:
|
||
|
||
```sql
|
||
-- Count total dealers
|
||
SELECT COUNT(*) as total_dealers FROM dealers;
|
||
|
||
-- Verify auto-generated columns
|
||
SELECT
|
||
dealer_id,
|
||
created_at,
|
||
updated_at,
|
||
is_active,
|
||
dlrcode,
|
||
dealership
|
||
FROM dealers
|
||
LIMIT 5;
|
||
|
||
-- Check for null values in key fields
|
||
SELECT
|
||
COUNT(*) FILTER (WHERE dlrcode IS NULL) as null_dlrcode,
|
||
COUNT(*) FILTER (WHERE domain_id IS NULL) as null_domain_id,
|
||
COUNT(*) FILTER (WHERE dealership IS NULL) as null_dealership
|
||
FROM dealers;
|
||
|
||
-- View sample records
|
||
SELECT
|
||
dealer_id,
|
||
dlrcode,
|
||
dealership,
|
||
city,
|
||
state,
|
||
domain_id,
|
||
created_at,
|
||
is_active
|
||
FROM dealers
|
||
LIMIT 10;
|
||
|
||
-- Check date formats
|
||
SELECT
|
||
dlrcode,
|
||
date,
|
||
date_of_termination_resignation,
|
||
last_date_of_operations
|
||
FROM dealers
|
||
WHERE date IS NOT NULL
|
||
LIMIT 5;
|
||
|
||
-- Verify all dealers have dealer_id and timestamps
|
||
SELECT
|
||
COUNT(*) as total,
|
||
COUNT(dealer_id) as has_dealer_id,
|
||
COUNT(created_at) as has_created_at,
|
||
COUNT(updated_at) as has_updated_at,
|
||
COUNT(*) FILTER (WHERE is_active = true) as active_count
|
||
FROM dealers;
|
||
```
|
||
|
||
---
|
||
|
||
## Additional Notes
|
||
|
||
- **Backup**: Always backup your database before bulk imports
|
||
- **Testing**: Test import with a small sample (5-10 rows) first
|
||
- **Validation**: Validate data quality before import
|
||
- **Updates**: Use `UPSERT` logic if you need to update existing records
|
||
|
||
---
|
||
|
||
## Support
|
||
|
||
For issues or questions:
|
||
1. Check the troubleshooting section above
|
||
2. Review PostgreSQL COPY documentation
|
||
3. Verify CSV format matches the sample provided
|
||
4. Check database logs for detailed error messages
|
||
|
||
---
|
||
|
||
**Last Updated**: December 2025
|
||
**Version**: 1.0
|
||
|