import data

Database
Enthusiast

import data



hi.....
i want to import large ammount of data form one .csv fle to one teradata
table . i have tried following stems:
1. open teradata sql assistance
2. open file menu-----> import data
3.run a query insert into insert into dq_app_data.test (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
4. then i am selecting aour file insurance data.csv file
i am getting error" wrong number of value in the in import file."plz help me to solve that problem

my table definition is

CREATE SET TABLE DQ_APP_DATA.test ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
policy_id VARCHAR(11) CHARACTER SET LATIN NOT CASESPECIFIC,
Agent VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC,
agent_city VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
ModelCluster VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
ModelName VARCHAR(75) CHARACTER SET LATIN NOT CASESPECIFIC,
Product VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
Region VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
RTOLocation VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
rtocluster VARCHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,
RTOState VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
AgeofVehicle VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,
TransactionType VARCHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC,
IDV DECIMAL(18,8),
Netpremium DECIMAL(18,8),
Gross_premium DECIMAL(18,8),
booking_date DATE FORMAT 'yyyy/mm/dd',
policyenddate DATE FORMAT 'yyyy/mm/dd',
Agent_commission DECIMAL(18,8),
servicetax DECIMAL(18,8),
Booking_month VARCHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,
Booking_year VARCHAR(11) CHARACTER SET LATIN NOT CASESPECIFIC,
Policy_period VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX test ( policy_id );

thank you
Neeraj shukla

6 REPLIES
Enthusiast

Re: import data

At which record is import failing. I mean is import failing at record#1 of .csv file?

can you send me us few records of your .CSV file ?

Enthusiast

Re: import data

Neeraj,

The data you posted has first row booking_date as '5/13/2006' make sure all the dates are in format 'MM/DD/YYYY'.So the first row should have '05/13/2006'.

Once that is done change the follwing setttings in queryman
1. Tools>Options>Export/import tab> set "use this delimeter between columns in files" as , ( comma )
2. Check "ignore first record in the file" as your file first row contians column names.

Once you do all the above changes then run your old import statement and it will run through.

Cheers
Senior Apprentice

Re: import data

It's nice that you post all that interesting data, could you please add even more sensible information like account and credit card numbers, too?

Dieter
Enthusiast

Re: import data



Dieter, you are being too ironic!! ;)

Neeraj,

Please don't post/share any information like the one you have uploaded. It may cost you your job!!
Enthusiast

Re: import data

hi
actually that is not my company data .it is a sample data only. i am doing a excercise.
so only i posted
Neeraj
Enthusiast

Re: import data

Neeraj,

In the insert query add one more '?'.
we have 23 columns in table definition and you are giving only 22 '?' in insert statement.

Hope this helps you

cheers:-)