Insterting CSV File data into a tera data table

Database
Enthusiast

Insterting CSV File data into a tera data table

Hi Experts,
I am new to Tera data development...
I am trying to insert multiple files that are in csv format into one tera data table...
The code mentioned below works file for one file at a time but I have 3 years of data in csv format for each individual day..
I have to load n number of tables like this.
I want to run the script mentioned below through our inhouse application and load all the data at once into the table.

Can some one help me solve this.
the data is in files with following name format.

sales_graph_motel_level_20070429.csv
sales_graph_motel_level_20070430.csv
sales_graph_motel_level_20070501.csv
sales_graph_motel_level_20070502.csv
sales_graph_motel_level_20070503.csv

The last numbers in the name indicate the date. And I have to do this one time and we have data till dec 2009.
Any help in really appreciated.
Thanks in Advance.

DATEFORM ANSIDATE;

ERRLIMIT 50000000;
TENACITY 4;
SESSIONS 8;
SLEEP 6;

.LOGON HHY2/FASC_PORT_DCH,portbch;

DATABASE PRODHOT;

DROP TABLE PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STG;
DROP TABLE PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ERR1;
DROP TABLE PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ERR2;

CREATE SET TABLE PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STG,
NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
REGION_ID varchar(20),
DISTRICT_ID varchar(20),
MONTH_ID varchar(20),
WEEK_ID varchar(20),
ADD_ID varchar(20),
ADD_NM varchar(20),
ADD_TYPE varchar(20),
HOTEL_NAME varchar(20),
TRANSACTION_DATE varchar(20),
TAX varchar(20),
PAIDEARLY_ROOMS varchar(20),
PAIDLATER_DOLLAR varchar(20),
PAIDEARLY_ROOMNUM varchar(20),
PAIDEARLY_DOLLAR varchar(20),
HIGH_SALE_ROOMS varchar(20),
HIGH_SALE_ROOMDOLLAR varchar(20),
MONTHLY_ROOMS varchar(20),
MONTHLY_DOLLARS varchar(20),
DATA_RESERVATIONS varchar(20),
DATA_REVDOLLAR varchar(20),
TOTAL_ROOMS_OCPvarchar(20),
TOTAL_DOLLAR varchar(20),
GAIN_DOLLAR varchar(20),
CMD varchar(20)

)

PRIMARY INDEX ( REGION_ID ,DISTRICT_ID ,MONTH_ID ,WEEK_ID ,ADD_ID ,ADD_NM ,ADD_TYPE ,HOTEL_NAME ,TRANSACTION_DATE ,TAX ,PAIDEARLY_ROOMS ,PAIDLATER_DOLLAR ,PAIDEARLY_ROOMNUM ,PAIDEARLY_DOLLAR ,HIGH_SALE_ROOMS ,HIGH_SALE_ROOMDOLLAR ,MONTHLY_ROOMS ,MONTHLY_DOLLARS ,DATA_RESERVATIONS ,DATA_REVDOLLAR ,TOTAL_ROOMS_OCP,TOTAL_DOLLAR ,GAIN_DOLLAR ,CMD );

CLEAR;

set record vartext ",";

DEFINE
F1 (varchar(100)) ,
F2 (varchar(100)) ,
F3 (varchar(100)) ,
F4 (varchar(100)) ,
F5 (varchar(100)) ,
F6 (varchar(100)) ,
F7 (varchar(100)) ,
F8 (varchar(100)) ,
F9 (varchar(100)) ,
F10 (varchar(100)) ,
F11 (varchar(100)) ,
F12 (varchar(100)) ,
F13 (varchar(100)) ,
F14 (varchar(100)) ,
F15 (varchar(100)) ,
F16 (varchar(100)) ,
F17 (varchar(100)) ,
F18 (varchar(100)) ,
F19 (varchar(100)) ,
F20 (varchar(100)) ,
F21 (varchar(100)) ,
F22 (varchar(100)) ,
F23 (varchar(100)) ,
F24 (varchar(100))

FILE=\IS02corp\rec\Files\Corp\IS\80720\HUB\HOTELSUMM\Logs\sales_graph_motel_level_20070429.csv;
SHOW;

BEGIN LOADING PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STG;
ERRORFILES PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ERR1,
PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ERR2
CHECKPOINT 0;

INSERT INTO PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STG
VALUES(
:F1 ,
:F2 ,
:F3 ,
:F4 ,
:F5 ,
:F6 ,
:F7 ,
:F8 ,
:F9 ,
:F10,
:F11,
:F12,
:F13,
:F14,
:F15,
:F16,
:F17,
:F18,
:F19,
:F20,
:F21,
:F22,
:F23,
:F24

);

END LOADING;

.LOGOFF;
1 REPLY
Enthusiast

Re: Insterting CSV File data into a tera data table

hi,
You can refer to solution
http://www.teradata.com/teradataforum/Topic17627-1-1.aspx
Let me know if this solves your problem