Get value or values from file is a common question in ODI then, in the following lines and examples, I will try to show how simple is to achieve this!
First of all, what is the problem description?
1) A flat file with just one value that need to be upload into a ODI variable
2) The flat file can or can’t have Header name (I used the case where there is a column header).
Example:
File: my_test_file.txt
Content:
Country_number 123456789
OK, now to explain how this work in ODI I will use a small trick:
1) Create a datastore to the file with the following characterists:
- Read to the file (c:temp my_test_file.txt in my case)
- one column (C1 in my case) and datatype String. I know that the file has a number content but let the column as String is intentional.
- 1 line as header
2) Import the LKM File to SQL
3) Create an interface that maps the datastore, as source, to any target, doesn’t matter if the interface works or not, it just need to be started in the Operator. At true, we’re “hunting” some code generation… Make sure that the imported LKM (step 2) was used in the interface
4) Go to operator, open the interface execution, go to “Load Data” step, Description tab and copy all content of the superior box. You should get something like:
5) Now all that is necessary is copy the code into a refresh variable and “Voila”! Now you can refresh your variable from a file! Just drag the variable into a package…
This is how the variable should looks like:
Best Regards to all!
Cezar Santos
August 6, 2018 at 8:53 PM
I tried this process in ODI 11G, but it is throwing below error. Please advise
ODI-1228: Task GV_Test (Variable) fails on the target connection XXXXX.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00906: missing left parenthesis
September 27, 2016 at 7:01 AM
Awesome Technique 🙂
February 22, 2016 at 9:04 PM
File in unix and need to pass file count to ODI variable,
os command executing successfully: wc -l /data/test.csv, but it’s not writing any where please let me know how to pass os command results to ODI variable.
February 22, 2016 at 4:47 PM
Hi, file in unix and need to pass file count to ODI variable, os command : wc -l /data/test.csv, please let me know how to pass os command results to ODI variable or any other alternative way. I tried above one which was mentioned by Cezar but it’s gave an error: ORA-00906: missing left parenthesis
June 26, 2015 at 12:19 AM
Did this top working in ODI 11 and above….currently trying 12c and it returns an error.
October 20, 2014 at 8:57 PM
I have tried to create a variable refresh as stated above but when I execute the code it says missing left Parenthesis. The ODI load of the 1 record with 1 field into a table works fine but I cant use the select statement generated by ODi in a variable refresh or in SQLdeveloper.\\
Any ideas what could be wrong?
select FILENAME C1_FILENAME
from TABLE
/*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=File_inSNP$CRLOAD_FILE=d:\IntData/File_in.txtSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0x002cSNP$CRFILE_SEP_LINE=0x000D0x000ASNP$CRFILE_FIRST_ROW=0SNP$CRFILE_ENC_FIELD=”SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=FILENAMESNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLENGTH=100SNP$CRPRECISION=100SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=Second_ColumnSNP$CRTYPE_NAME=STRINGSNP$CRORDER=2SNP$CRLENGTH=100SNP$CRPRECISION=100SNP$CR$$SNPS_END_KEY*/;
June 17, 2011 at 11:24 AM
Hello- I followed the steps described in this post to create a variable with the generated code. However, when I try to test or refresh the variable, I get the following error: Error while executing query: ORA-00906: missing left parenthesis
Here is the generated code I am using:
select C1 C1_C1
from TABLE
/*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=File_ServicesCheckSNP$CRLOAD_FILE=//server1/ODIFiles//ServicesHyperionCheck.logSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=09SNP$CRFILE_SEP_LINE=0D0ASNP$CRFILE_FIRST_ROW=1SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=C1SNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLINE_OFFSET=1SNP$CRLENGTH=75SNP$CRPRECISION=75SNP$CR$$SNPS_END_KEY*/
June 2, 2011 at 9:26 AM
Hi Kshitiz,
The double quotes is a mistake. (although it returns this error when I change the code to CRLOAD_FILE=C:ODIWORKING_DIR/”#GET_INTERFACE”)
The error generated using single quotes:
CRLOAD_FILE=C:ODIWORKING_DIR/’#GET_INTERFACE’
is
File not found: C:ODIWORKING_DIR/’Mor12.txt’
If I hardcode the filename, then it works perfectly e.g.
CRLOAD_FILE=C:ODIWORKING_DIR/Mor12.txt
However, because I am running several files through a single interface, I want to be able to dynamically get the current file.
February 2, 2016 at 10:06 PM
Hi Olu
Give a space after variable name and do not use any quotes. It will work then
June 1, 2011 at 7:38 AM
This works fine for one file. However i am processing several files using the same interface. In this case, the code generated by the LKM is slightly different as it refers to the name of the variable GET_INTERFACE as follows:
CRLOAD_FILE=C:ODIWORKING_DIR/#GET_INTERFACE
On execution, I get :
File not found: C:ODIWORKING_DIR/#GET_INTERFACE.
I have tried changing the code to
CRLOAD_FILE=C:ODIWORKING_DIR/’#GET_INTERFACE’
but i get this error:
File not found: C:ODIWORKING_DIR/”Mor12.txt”
It correctly identifies the current file, but cannot interprete the directory path.
Any ideas?
Cheers
June 2, 2011 at 6:42 AM
does ODI have read permission to that folder and make sure you are reading the filer correctly. Here in your example its with double quote , hope ODI is not looking for double quotes file and raising error. Please check for that too.
March 24, 2011 at 12:46 AM
Hi,
I am using a variable(Dataype: Text,Action: Latest Value) in a package(say pack1) to retrieve the value of a directory path from an oracle table and write a file in that path.I have generated a scenario of pack1 and used it in another package(say pack2).This pack2’s scenario is called from another master package.
Now I am observing that whenever I am updating the content of that column (directory path )of the oracle table and executing the master package the variable’s value is changed to the new path value but the file is being written in the path referring to the previous value.If I execute this package once again only then the file is written in the new updated path.I am unable to find out the reason for this behaviour of the variable..Please help…
Thanks in advance,
Parama
March 15, 2011 at 11:33 AM
Hey i have an issue over here in ODI 10.1.3.6.4 version. The issue is, I have double clicked a scenario and from the execution tab i tried executing the scenario. Then I clicked OK button and try to write a string value for the parameter. but letters are not enable and only allows me to write numbers. where in when i looked into the appropriate variable , the datatype says Text and Action says Historize. Can you please help me why its not allowing the characters when the datatype shows text
Please help me
Thanks in advance… Venkat
March 15, 2011 at 12:56 PM
I believe that should not be the case . Can you please send the screenshot at kdevendr@gmail.com .
June 11, 2010 at 4:33 AM
Hey cezar,
how r u?
i have one question, if file have more then 1 row, how get the row count (number of record) in file??
June 10, 2010 at 7:54 AM
great !!!
thanks
June 9, 2010 at 5:12 AM
I can´t see the code image, please put the text image thanks
August 25, 2009 at 5:11 AM
Hi Rathish,
Yes, it is possible to create a loop and “refresh” the lines from flat file…
That will be the “How to refresh ODI variables from file – Part 2 – More than one value” post…
I intend to release it this week!
Good to see you here!
August 24, 2009 at 8:43 AM
Hi Cezar,
Good to see new Posts here.
This is a very useful post which we can implement in our real time projects for the replacement of temperory tables with a single value column (like Lock status, Current User ) etc ….
Also one question :-
Can we use update command to update the value of this column (in file)…
( I know before asking a question like this, I need to try myself 🙂 , any way I will try this by 2mrw and will update this reply. )
And Thanks for your posts ……
Regards,
Rathish