Loading data into Table

General

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,CLUAUT,103.40,TELCO,577.84,WHEIND,199.71,1,1,,,,,,,,,,,,

CANBAN,179.25,CHOINV,209.14,CITUNI,268.36,CORBAN,444.87,RELCAP,490.19,STABAN,981.46,SYNBN,58.30,UNIBAN,191.28,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,VIDAPP,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,DATTEC,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,CIMBIR,8.74,CINEVI,92.32,CLADIA,564.48,CMC,1524.31,CRISIL,3211.63,CUMIND,474.97,JETAIR,545.19,PSLHOL,474.16,RELII,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.36,RELCAP,599.90,STABAN,941.93,SYNBN,58.45,UNIBAN,190.88,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,VIDAPP,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,DATTEC,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,CIMBIR,7.39,CINEVI,92.64,CLADIA,556.70,CMC,1532.55,CRISIL,3260.42,CUMIND,476.07,JETAIR,534.28,PSLHOL,471.30,RELII,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.08,RELCAP,484.46,STABAN,983.70,SYNBN,58.30,UNIBAN,191.79,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,VIDAPP,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,DATTEC,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,CIMBIR,7.48,CINEVI,92.95,CLADIA,560.37,CMC,1532.22,CRISIL,3244.93,CUMIND,465.76,JETAIR,545.72,PSLHOL,474.80,RELII,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!!!


3 REPLIES
Senior Apprentice

Re: Loading data into Table

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

Re: Loading data into Table

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!!!!!

Senior Apprentice

Re: Loading data into Table

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