laxmanhalaki/Re_Backend

Fork 0

laxmanhalaki 9c8a8512bc dealer table added and claim type filter implemented

2025-12-23 19:25:29 +05:30

17 KiB

Raw Permalink Blame History

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).

CSV File Format Requirements
Column Mapping
Preparing Your CSV File
Import Methods
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:

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

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

Open your CSV file in Excel, Google Sheets, or a text editor
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
Ensure the header row matches the column names exactly (see Column Mapping)
Verify column order - columns must be in the exact order listed above (44 columns total)
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

Save as CSV (UTF-8 encoding)
File location: Save to an accessible path (e.g., C:/Users/COMP/Downloads/DEALERS_CLEAN.csv)
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.

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:

Connect to PostgreSQL:

psql -U your_username -d royal_enfield_workflow -h localhost

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

\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

Verify import:

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:

-- 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

Open pgAdmin and connect to your database
Right-click on dealers table → Import/Export Data
Select Import
Configure:
- Filename: Browse to your CSV file
- Format: CSV
- Header: Yes
- Encoding: UTF8
- Delimiter: Comma
Click OK to import

Method 4: Using Node.js Script

Create a script to import CSV programmatically (useful for automation):

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:
```
"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:

-- 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:

Check the troubleshooting section above
Review PostgreSQL COPY documentation
Verify CSV format matches the sample provided
Check database logs for detailed error messages

Last Updated: December 2025
Version: 1.0

17 KiB Raw Permalink Blame History Unescape Escape