Loading data into Table | Teradata Downloads

General

Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.

itsakash28 2 posts Joined 12/13

02 Feb 2015

Loading data into Table

I have a query while loading data into Target table.The requirement is to laod a flat file to teradata database.below are the details:
Source file data:

WAMAZ,707.16,ESCORT,157.78,MAHNIS,629.98,CGIGA,70.49,C LUAUT,103.40,TELCO,577.84,WHEIND,199.71,1,1,,,,,,,,,,, ,

CANBAN,179.25,CHOINV,209.14,CITUNI,268.36,CORBAN,444.8 7,RELCAP,490.19,STABAN,981.46,SYNBN,58.30,UNIBAN,191.2 8,UTIBAN,947.71,VIJBAN,73.71,VYSBAN,317.66,YESBAN,150. 55,1,1,,

DALCEM,547.40,GUJAMB,147.30,JKCEME,213.78,LARTOU,1598. 15,ULTCEM,988.12,1,1,,,,,,,,,,,,,,,,

BHAENG,31.49,CENPLY,553.17,CHAFER,66.56,CHEALK,102.23, COLCHE,284.89,CORFER,126.67,GIDREJ,278.97,RELIND,1249. 28,RELNAT,23.30,RELPET,62.30,1,1,,,,,,

BHAFOR,338.05,BPL,25.31,BPLENG,4.03,CROGRE,449.92,VIDA PP,35.27,1,1,,,,,,,,,,,,,,,,

CININD,121.82,COSFIL,117.84,CYBMED,80.10,PVRLIM,200.63 ,ZEETEL,296.11,1,1,,,,,,,,,,,,,,,,

COLPAL,396.46,CONCOF,275.71,CORHEA,7.31,DABIND,111.48, PROGAM,664.76,WESHAT,98.89,1,1,,,,,,,,,,,,,,

BFLSOF,275.38,COMINT,13.71,COMSYS,22.21,CYBTEC,13.13,D ATTEC,42.20,INFTEC,2370.16,PATCOM,310.87,PSIDAT,66.42, TATELX,229.66,TCS,927.87,TECMAH,1009.45,WIPRO,436.07,1 ,1,,

BERPAI,49.80,CENENK,165.26,CENPUL,49.92,CHESAN,13.39,C IMBIR,8.74,CINEVI,92.32,CLADIA,564.48,CMC,1524.31,CRIS IL,3211.63,CUMIND,474.97,JETAIR,545.19,PSLHOL,474.16,R ELII,502.75,1,1

CIPLA,269.30,DABPHA,93.84,DRREDD,576.06,GLAIND,177.41, RANLAB,316.14,WOCKHA,403.68,1,1,,,,,,,,,,,,,,

BHABIJ,1187.26,BHAELE,1157.24,BHEL,2887.51,ONGC,757.73 ,POWTRA,51.00,SUZENE,426.34,THERMA,254.42,1,1,,,,,,,,, ,,,

CENTEX,477.34,CHETEX,24.15,ARCGRE,107.44,TITIND,750.64 ,SHOSTO,468.25,PROVOG,404.95,1,1,,,,,,,,,,,,,,

BHATE,559.72,ADECEL,160.68,STEOPT,161.52,HUGTEL,19.41, RELCOM,673.84,VIDSAN,344.09,1,1,,,,,,,,,,,,,,

SENSEX,15867.52,1,1,,,,,,,,,,,,,,,,,,,,,,,,

SWAMAZ,712.40,ESCORT,157.04,MAHNIS,621.24,CGIGA,71.47, CLUAUT,103.47,TELCO,576.32,WHEIND,199.27,2,1,,,,,,,,,, ,,

CANBAN,175.43,CHOINV,209.16,CITUNI,267.10,CORBAN,445.3 6,RELCAP,599.90,STABAN,941.93,SYNBN,58.45,UNIBAN,190.8 8,UTIBAN,944.27,VIJBAN,73.83,VYSBAN,315.14,YESBAN,152. 86,2,1,,

DALCEM,548.41,GUJAMB,147.64,JKCEME,209.52,LARTOU,1338. 94,ULTCEM,994.75,2,1,,,,,,,,,,,,,,,,

BHAENG,31.58,CENPLY,550.55,CHAFER,66.39,CHEALK,101.23, COLCHE,287.53,CORFER,126.70,GIDREJ,269.83,RELIND,1282. 09,RELNAT,27.67,RELPET,64.50,2,1,,,,,,

BHAFOR,333.37,BPL,23.87,BPLENG,4.08,CROGRE,450.66,VIDA PP,35.44,2,1,,,,,,,,,,,,,,,,

CININD,121.90,COSFIL,117.76,CYBMED,80.36,PVRLIM,197.43 ,ZEETEL,296.66,2,1,,,,,,,,,,,,,,,,

COLPAL,398.36,CONCOF,271.79,CORHEA,7.48,DABIND,111.41, PROGAM,666.17,WESHAT,104.10,2,1,,,,,,,,,,,,,,

BFLSOF,274.30,COMINT,13.96,COMSYS,22.04,CYBTEC,12.50,D ATTEC,41.67,INFTEC,2401.24,PATCOM,310.93,PSIDAT,65.92, TATELX,221.78,TCS,928.81,TECMAH,1008.50,WIPRO,436.10,2 ,1,,

BERPAI,50.32,CENENK,164.43,CENPUL,49.79,CHESAN,13.00,C IMBIR,7.39,CINEVI,92.64,CLADIA,556.70,CMC,1532.55,CRIS IL,3260.42,CUMIND,476.07,JETAIR,534.28,PSLHOL,471.30,R ELII,497.15,2,1

CIPLA,269.21,DABPHA,93.19,DRREDD,576.94,GLAIND,177.45, RANLAB,313.14,WOCKHA,405.33,2,1,,,,,,,,,,,,,,

BHABIJ,1045.00,BHAELE,1142.85,BHEL,2901.22,ONGC,754.22 ,POWTRA,53.90,SUZENE,515.32,THERMA,234.09,2,1,,,,,,,,, ,,,

CENTEX,437.96,CHETEX,23.88,ARCGRE,107.18,TITIND,688.02 ,SHOSTO,454.56,PROVOG,407.94,2,1,,,,,,,,,,,,,,

BHATE,558.11,ADECEL,160.92,STEOPT,157.16,HUGTEL,19.51, RELCOM,676.52,VIDSAN,349.44,2,1,,,,,,,,,,,,,,

SENSEX,15837.82,2,1,,,,,,,,,,,,,,,,,,,,,,,,

SWAMAZ,709.81,ESCORT,157.09,MAHNIS,609.57,CGIGA,70.77, CLUAUT,102.95,TELCO,579.33,WHEIND,200.92,3,1,,,,,,,,,, ,,

CANBAN,176.81,CHOINV,210.23,CITUNI,267.08,CORBAN,445.0 8,RELCAP,484.46,STABAN,983.70,SYNBN,58.30,UNIBAN,191.7 9,UTIBAN,943.11,VIJBAN,73.65,VYSBAN,317.28,YESBAN,153. 97,3,1,,

DALCEM,549.44,GUJAMB,147.68,JKCEME,212.20,LARTOU,1497. 59,ULTCEM,983.37,3,1,,,,,,,,,,,,,,,,

BHAENG,31.68,CENPLY,550.46,CHAFER,66.28,CHEALK,102.32, COLCHE,285.68,CORFER,125.96,GIDREJ,268.95,RELIND,1262. 18,RELNAT,24.88,RELPET,67.53,3,1,,,,,,

BHAFOR,338.54,BPL,24.70,BPLENG,4.13,CROGRE,450.16,VIDA PP,35.53,3,1,,,,,,,,,,,,,,,,

CININD,122.32,COSFIL,118.23,CYBMED,80.50,PVRLIM,198.42 ,ZEETEL,296.36,3,1,,,,,,,,,,,,,,,,

COLPAL,399.21,CONCOF,277.36,CORHEA,6.87,DABIND,112.87, PROGAM,667.60,WESHAT,100.17,3,1,,,,,,,,,,,,,,

BFLSOF,274.15,COMINT,13.98,COMSYS,22.29,CYBTEC,13.18,D ATTEC,42.86,INFTEC,2384.17,PATCOM,310.67,PSIDAT,66.02, TATELX,223.00,TCS,927.31,TECMAH,1008.77,WIPRO,436.42,3 ,1,,

BERPAI,50.25,CENENK,164.55,CENPUL,49.95,CHESAN,13.49,C IMBIR,7.48,CINEVI,92.95,CLADIA,560.37,CMC,1532.22,CRIS IL,3244.93,CUMIND,465.76,JETAIR,545.72,PSLHOL,474.80,R ELII,408.75,3,1

CIPLA,267.48,DABPHA,94.10,DRREDD,576.44,GLAIND,177.61, RANLAB,307.06,WOCKHA,405.58,3,1,,,,,,,,,,,,,,

BHABIJ,1116.16,BHAELE,1164.00,BHEL,2891.87,ONGC,769.62 ,POWTRA,50.48,SUZENE,414.34,THERMA,236.54,3,1,,,,,,,,, ,,,

CENTEX,478.97,CHETEX,23.75,ARCGRE,109.19,TITIND,680.15 ,SHOSTO,442.01,PROVOG,410.08,3,1,,,,,,,,,,,,,,

BHATE,579.50,ADECEL,157.39,STEOPT,169.21,HUGTEL,17.77, RELCOM,655.98,VIDSAN,348.65,3,1,,,,,,,,,,,,,,

SENSEX,15982.76,3,1,,,,,,,,,,,,,,,,,,,,,,,,

like this set of values are available in the file .

it seems there are 4 columns in the table STOCKNAME,EOD RATE,DAY,MONTH.First, i need to load the above file into staging table .how to load this file as 4 columns in staging table.Please spare some time and let me know the solution.

Thanks In advance!!!

dnoeth 4628 posts Joined 11/04

02 Feb 2015

I would load the data as-is and then process it within database.
E.g. load as a single VarChar and extract the columns using STRTOK:

STRTOK(x, ',', n)

n will be hard-coded (one SELECT per name/value pair plus UNION ALL) or from a CROSS JOIN to a number table.
Additionally you need to find the DAY/MONTH columns, to add a WHERE n < dmPos:

CHAR_LENGTH(RTRIM(x, ', ')) - CHAR_LENGTH(OTRANSLATE(RTRIM(x, ' ,'), ',', '')) AS dmPos
-- column number of the DAY/MONTH columns

Dieter

itsakash28 2 posts Joined 12/13

03 Feb 2015

thanks for your rsponse.
BUT HERE THE PROBLEM IS THE NUMBER OF VALUES ALONG A ROW IS NOT FIXED.IN SOME ROWS IT IS 28,20,4 ETC.AND ALSO THE POSITION OF DATA AND DAY IS NOT CONSISTENT.PLEASE Elaborate more on this.i'm unable to understand.Please respond!!!!!

dnoeth 4628 posts Joined 11/04

03 Feb 2015

Create a table with all the odd number between 1 and the maximum number of columns, e.g. 27 and then cross join:

CREATE TABLE odd_numbers(n int);
INSERT INTO odd_numbers VALUES (1);
INSERT INTO odd_numbers VALUES (3);
...
INSERT INTO odd_numbers VALUES (27);


SELECT 
   CHAR_LENGTH(RTRIM(x, ', ')) - CHAR_LENGTH(OTRANSLATE(RTRIM(x, ' ,'), ',', '')) AS endPos
   ,STRTOK(x, ',', n)
   ,STRTOK(x, ',', n+1)
   ,STRTOK(x, ',', endPos)
   ,STRTOK(x, ',', endPos+1)
FROM odd_numbers CROSS JOIN vt
WHERE n < endPos

Dieter

You must sign in to leave a comment.

Search form