Need help loading data

UDA
Enthusiast

Need help loading data

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;
4 REPLIES
Enthusiast

Re: Need help loading data

Hi,
I have not understood your question.
You are saying
1)"I am trying to insert multiple files that are in csv format into one tera data table..."
if you want to insert into one table then This can be achieved in 2 ways
a)If you are executing the job through unix you can make the job as loop
You can pass the file to be loaded as a parameter from a parameter file
so each time the loop executed the parameter will change
and the next file will be taken and loaded in the same table.
You can create the script with additional code as below

.ACCEPT FILE_NAME FROM FILE /bdw/nrd/devt/scripts/Scr-16100-AVQ_SG/params.txt;
.IMPORT INFILE /sourcepath/&FILE_NAME FORMAT VARTEXT

wherein the param.txt will have full file name.
b) Concatenate the files into 1 if the size is small but I think this will not be possible.

2) "I have to load n number of tables like this."
If the table names are also different then you can create a common parameter name for file and table name and pass it to loading script using same as above.
something like below

.BEGIN IMPORT MLOAD TABLES
PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_&date_param
WORKTABLES
PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_WT_&date_param
ERRORTABLES
PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ER_&date_param PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_UV_&date_param
.IMPORT INFILE /sourcepath/sales_graph_motel_level_&date_param..dat

wherein the date_param will be the date for which you want to load the data.
Provided the table name and filename share the same pattern.
Hope this solves your problem.

Enthusiast

Re: Need help loading data

Just cat the files together in Unix/ Linux.
It is a simple command:

cat \\IS02corp\rec\Files\Corp\IS\80720\HUB\HOTELSUMM\Logs\sales_graph_motel_level_*.csv >> allfiles.csv

You will need to specify a directory for allfiles. Then load allfiles.csv
Enthusiast

Re: Need help loading data

thanks for the replies guys. I cannot do it on unix because we run entirely on windows.

from your comments above does it mean the load file should look like this.

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

I should add

.ACCEPT FILE_NAME FROM FILE /bdw/nrd/devt/scripts/Scr-16100-AVQ_SG/params.txt;
.IMPORT INFILE /sourcepath/&FILE_NAME FORMAT VARTEXT

and instead of

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

I should use

.BEGIN IMPORT MLOAD TABLES
PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_&date_param
WORKTABLES
PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_WT_&date_param
ERRORTABLES
PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_ER_&date_param PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_UV_&date_param
.IMPORT INFILE /sourcepath/sales_graph_motel_level_&date_param..dat

Sorry guys I am new to teradata and cannot understand your reply correctly..
Enthusiast

Re: Need help loading data

Merge all the files of different dates into one file, say all_files.csv

Then use this file all_files.csv as the input file.

Previous input file:

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

Modified code:

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