All Forums UDA
monty4u1 14 posts Joined 11/09
20 Dec 2009
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.csvsales_graph _motel_level_20070430.csvsales_graph_motel_level_20070 501.csvsales_graph_motel_level_20070502.csvsales_graph _motel_level_20070503.csvThe 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_ERR2CHECKPOINT 0;INSERT INTO PRODHOT.PRODHOT_PREPROD_SALESMOTELLVL_STGVALUES(: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;

RB185048 33 posts Joined 09/08
20 Dec 2009

Hi,I have not understood your question.You are saying1)"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..datwherein 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.

Jimm 298 posts Joined 09/07
21 Dec 2009

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.csvYou will need to specify a directory for allfiles. Then load allfiles.csv

monty4u1 14 posts Joined 11/09
21 Dec 2009

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_ERR2CHECKPOINT 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..datSorry guys I am new to teradata and cannot understand your reply correctly..

UpaMishra 35 posts Joined 01/08
27 Jan 2010

Merge all the files of different dates into one file, say all_files.csvThen 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;

You must sign in to leave a comment.