Published on July 2, 2016
1. ODI Multiple Flat Files to Table 2016 1 ODI (22.214.171.124.0) Multiple CSV to Table via Interface with dynamically getting of File Name with Status and File Moved to Archive Folder Document Made by: Darshankumar Prajapati & Ravindrakumar Document Version: 1.0 Document Date: 02-Jul-2016 Document LastUpdated: 02-Jul-2016
2. ODI Multiple Flat Files to Table 2016 2
3. ODI Multiple Flat Files to Table 2016 3 Purpose: Suppose we have multipleCSV Files,thatwe have toloadinto Single Table viaSingle Interface,alsoFile Name musttakenDynamic;alsowe have tomake a table withFile Name’sListwith Loadedinto Table Status alongwiththeirRecordCount. Prerequisites : It was assumed that before this Tutorial you are able to load a Data from Flat File to Table via Single Interface in ODI(126.96.36.199.0 or Greater). Software and Hardware Requirements (Optional): The followingis alistof software requirements: The systemshouldinclude the followinginstalledproducts: Oracle Database 188.8.131.52.0 or Greater Oracle Data Integrator184.108.40.206.0 If notdone before,startthe servicesandcomponentsforOracle Database 11g. Created Model: You are goingto make thistype of Model,so eachand everycomponentcanbe explainedindetailin below(DetailedTechnical Steps) section.
4. ODI Multiple Flat Files to Table 2016 4 Detailed Technical Steps: We have to make one Package underPackagesname usedhere is:Multiple_Csv. 1) Made_List_Of_Files: Go to Diagram Tab and Select:ODIOSCommand. Thiswill usedforTakingAll FilesName inOne FlatFile toachieve DynamicallyGettingof File Name. Put BelowcommandinCommandtoExecute Tab: dir D:ODI12SOURCEseha*.CSV /b/o:gn>D:ODI12SOURCEFILES.CSV In case of Linux: Cd /u01/files/ Ls > FILES.csv In above commandassume thatOur Multiple FlatFilesSource Locationis: D:ODI12SOURCE*.* AndWe are doingonlyListingof all filesintoFlatFile called FILES.CSV. ScreenShotof filesinMyDirectory:
5. ODI Multiple Flat Files to Table 2016 5 Afterexecutingthisyouwill have thesevalues inFILES.CSV. 2) Get_File_Name: Nowwe have to create 1 Interface,whichwillloadDatafrom above createdFile (PQ.CSV)to Table. For thiswe needtocreate one table like below: CREATE TABLE FILE_LIST ( FILENAME VARCHAR2(240 BYTE), STATUS CHAR(1 BYTE), RECORD_COUNT NUMBER) Nowyouwill have tocreate On Interface inwhich FILES.CSVas a Source andFILE_LIST as Target. Screenshotof Interface created:
6. ODI Multiple Flat Files to Table 2016 6 - In Statusyoucan Hardcode ‘N’for a time. - We will Update Record_CountLaterviaseparate procedure. 3) File_name Nowwe needtocreate on LOCAL VARIABLEas showninBelow ScreenShots: Thisis Refresh Variable: Step1:
7. ODI Multiple Flat Files to Table 2016 7 Step2: In thisBelowqueryisused: selectfilename fromfile_listwhere loaded='N' and rownum<=1 It will justgetsfile name forwhichwe are goingtoload data inTarget Table. 4) Nowwe needtohave One Mapping whichwill loadDatafromFlat File toTable. In our case we have simplymade table same asFile,andmapeach and every column. Step1:
8. ODI Multiple Flat Files to Table 2016 8 Step2: Physical diagramDetails:
9. ODI Multiple Flat Files to Table 2016 9 ThisMapping will simply insertDatafrom FlatFile toour Target Table. 5) Update_loaded_file_status: Nowwe needtohave One PROCEDURE whichwill Update File Statuswhichwasbydefault‘N’in step2, thiswill setthat as ‘Y’,andWill AlsoputRECORD COUNT inthat FILELIST_STATUS table. Queryusedto achieve thisis: update file_listsetloaded='Y',record_count='<%=odiRef.getPrevStepLog("INSERT_COUNT")%>' where filename='#FILE_NAME' In this odiRef.getPrevStepLog("INSERT_COUNT") willsimplygetsRecordCountforParticularStepin ODI. Step1: Step2:
10. ODI Multiple Flat Files to Table 2016 10 Step3:
11. ODI Multiple Flat Files to Table 2016 11 . 6) File Remainsto Process: Nowwe needtohave one Variable whichwill countHow manyfilesare still remainstobe proceesed. Step1:
12. ODI Multiple Flat Files to Table 2016 12 Step2: QueryUsed: selectcount(filename) fromfile_listwhere loaded='N' Step3:
13. ODI Multiple Flat Files to Table 2016 13 7) Moving_files_to_Archive: Nowwe needtoMove thisprocessedflatfile toArchive Directory. For thiswe needtouse “ODIFILEMOVE” as below: Step1: Step2: 8) File_remains_to_Process: Nowwe need tocross check How Many Filesare Still RemainstoLoad. For thiswe needtoReuse Variable File_remains_to_Process asEVALUATEVARIBALEasshownbelow: So itwill be automaticallystoppedwhenFile_CountNOT>0.
14. ODI Multiple Flat Files to Table 2016 14 So at the endYou have createdthe below Model. Afterrunningof thisProcedure youcan checkfile statusinFILELIST_STATUSTable as below: You can cross checknow FilesSuccessfullymovedtotargetdirectory:
15. ODI Multiple Flat Files to Table 2016 15 You can cross checkRecord Countinyour Final Table inwhichyouhave loadedData. Summary: In thistutorial,youhave learnedhowto: Verifythe Prerequisites. Create one ODI_OS_COMMAND forjustlistingof youall flatfliesintoSingle FlatFile. Create a NewODI Model forthe FlatFile Source toTarget Database Table. Create a NewODI Source Datastore for Use withODI Interface Create a NewODI TargetDatastore forUse withODI Interface Create a Variable forFile_remains_to_Process,File_Name. Create a Mapping whichwill LoadData fromFlatfile toOracle Table. Create a procedure whichwill UpdatesaFile Status inTable alongwithRecordCount. Create a ODI_FILE_MOVE for movingyourprocessedfile toARCHIVEDirectory.
16. ODI Multiple Flat Files to Table 2016 16 Hope you have understoodwhich we have developedwiththe helpof thisdocument. Hope you have learnednewthings,if youhave done somethinglikethis,please share iton email@example.com firstname.lastname@example.org . THANKYOU ! KEEP ODIING!!!!
Instead we can use a single interface to load all the flat files of same structure into the single target table.
Reading and writing a delimited and Fixed Length Files ... to hold the data loaded from csv file and ... Loading multiple target tables in ...
... Creating an ODI Project and Interface : Exporting a Flat File to a Flat File; ... Create your target table by executing the SQL commands provided below.
How to load CSV file into SQL Server Database Table? ... about the files and the target tables. ... multiple .csv files that I now ...
... Load CSV file data in Oracle table ... a csv file that has multiple double ... file line into target DB table column. e.g: CSV file line ...
then you can load a file with "create table t as select * from flat_file" or ... load CSV data from a flat file ... the target file. ...
sql to get data from flat file ... Is there any way to use external tables in our scenario with multiple files? ... if a field in the flat file (csv) ...
Learn how to use complex ASCII files as ... flat, ASCII file than in a ... ASCII files come in several forms: CSV ...
We shall be discussing using ODI to integrate text file data into Oracle Database ... target Oracle Database table the ... 1 flat file to a table, ...
Hello Orhan, This is Srinivas . I am a developer working in Oracle ODI . I am stuck with a issue . I have a flat file ( The file has 5 different ...