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