CSV File LookUp

Hi all, I am designing a scenario following the given blog: <i><b>/people/kausik.medavarapu/blog/2005/12/29/csv-file-lookup-with-http-request-and-response-in-xi In this blog i am mainly dealing with the first half i.e the CSV File Lookup part. I have created the csv file as mentioned. Converted it into a jar and included that jar file in the repository under the Imported Archive tab. I have done certain manipulation in the given code to suit my design: <b>String price = "0"; try Class.forName("<i>the name of my imported jar file</i>"); Connection con=DriverManager.getConnection("jdbc:driver:""/""/"+"pciib04530/users/"); PreparedStatement stmt=con.prepareStatement("select itemno,price from price where itemno=?"); stmt.setString(1,a); ResultSet rs=stmt.executeQuery(); while(rs.next()) price = rs.getString("price"); con.close(); return price; catch(Exception e) return "-1"; }</b> On testing the mapping the error msg i get is Method findPrice with 1 arguments not found in class com.sap.xi.tf._Request_MM_ where findPrice is the name of my method and Request_MM is my message mapping. I dont know what is wrong. Pls guide. If anyone can mention help documents on CSV Lookup, it would be very helpful. Thanks in advance Regards Neetu

Hi Prashanth,
I have already gone through the both the blogs mentioned by you.
The first one speaks about CSV Lookup.
Here the author mentions about some driver <b><i>"its possible by means of a driver that makes the flat file (here CSV file) to appear as a database table to the API."</i></b>
But he does not mention the name of the driver.
The Blog is also not very elaborate.
Can you suggest the name of the driver?
Regards,
Neetu

Similar Messages

Hello! In our project we discussing about possibility to use CSV-files to transfer information. Can I use CSV in PI ?

Hi,
Yes you can. Use File adapter to read and do a file content conversion on it. By doing it the CSV format will be converted to XML format in PI
Lot of weblgs are available on this
/people/venkat.donela/blog/2005/03/02/introduction-to-simplefile-xi-filescenario-and-complete-walk-through-for-starterspart1
/people/sundararamaprasad.subbaraman/blog/2005/12/09/making-csv-file-lookup-possible-in-sap-xi
/people/rahul.nawale2/blog/2006/07/18/java-mapping-an-alternate-way-of-reading-a-csv-file
/people/swaroopa.vishwanath/blog/2005/06/24/generic-approach-for-validating-incoming-flat-file-in-sap-xi--part-1
Regards
Suraj
Edited by: S.R.Suraj on Sep 30, 2009 9:18 AM

Hi
I have a master CSV file created by querying the AD. I need to populate the "Team" attribute by doing a lookup using Name against the Name Column any of 6 CSVs, someone has prepared for me . The problem is the data is inconsistent
in the 6 CSV files so Name can hold either the Name or Username i.e. SAmAccountName or LoginName
Now my ps script works when the data is good. but I have around 30 exception cases whereby there is a mismatch . To explain:
is in this line ( see the big code block below)
$userObject = $nameAndTeamCsvData | Where-Object {$_.Name -eq $masterUserName}
The $_.Name is using the top level masterCSVData Name but I was hoping that it was using the
$nameAndTeamCSVData.Name as in only within the scope of the Where-Object {} ..I guess I have misunderstood the syntax of what I had written.
. A quick a dirty fix would be rename this column in all of the 4 spreadsheets or trying to fix the code.....
# now update the empty team value for user object
$csvMasterData | ForEach-Object `
$masterName = $_.Name
$masterUserName = $_.Username # to deal with exception cases
#force scope to as the properties in the outer with the same name should be out of scope
# lookup the Name to see if we can extract the user's Team
$userObject = $nameAndTeamCsvData | Where-Object {$_.Name -eq $masterName }
# deal with the situation where the name in the businesses spreadsheets is actually the Username (login name) in the master csv
if ( $userObject -eq $null )
# lookup the username (loginname) to see if we can extract the user's Team # !!!!error occurs here with the $_.Name !!!!!
$userObject = $nameAndTeamCsvData | Where-Object {$_.Name -eq $masterUserName}
if ( $userObject -eq $null )
$_.Team = "UNKNOWN"
else
# replace the mastercsv.Team with the one we have looked up
$_.Team = $userObject.Team
else
# Name matches so replace the mastercsv.Team with the one we have looked up
$_.Team = $userObject.Team
Daniel

I see your challenge. How about collecting the column names in an array and looking for the potential names? Following my example before, try this:
$dataColumns = $I | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name
Now, you can loop through the names and collect the appropriate data.
if ($dataColumns.Contains('Column1'))
#add ForEach-Object { $_.Column1... }
HTH
~fr3dd
Hi I had to wait until Friday as I have someone ( my boss) checking the business & team csv files against valid usernames and names.
# now update the empty team value for user object
$nameAndTeamCsvData | ForEach-Object `
$nt_name = $_.Name
# $masterUserName = $_.Username # to deal with exception cases
# lookup the Name to see if we can extract the username and mobile from the masterCSV
$userObject = $null # Replace my original code: $csvMasterData | Where-Object {$_.SName -eq $nt_name } # with foreach code below
$uo = $null
foreach ($uo in $csvMasterData)
if ($uo.SName -eq $nt_name)
$userObject = $uo
# ouch can't break out of this even though I found it so have to move on to the next
}if ( $userObject -ne $null ){ # Name matches so replace the few propeerties in mastercsv.Team with the ones we have looked up
$_.Username =$userObject.SamAccountName
$_.Mobile =$userObject.Mobile
This does work but is clearly not very efficient since I don't think I can
break or exit out of the foreach loop. I am thinking I could quickly modify and add your code - my import is delayed as I am having a new property added to user class... Let me know what you think.

Hiya guys After some guidance please. I have a CSV with following details MailboxName, PRFNAME,TempConstant usera, a, usera.prf,TEMPLATEPRFUSER userb, b, userb.prf,TEMPLATEPRFUSER userc, c, userc.prf,TEMPLATEPRFUSER Im after creating multiple copies of a prf file with the PRFNAME Import-Csv $UsernamesCSV | % { Copy-Item "C:\TemplatePRF\Template.prf" "C:\TEST\$($_.NewPRFName)"} This creates multiple prfs named correctly based on the prfnames provided in the CSV Now I want to search for mailboxname=TEMPLATEPRFUSER and replace to "MailboxName" from my csv. so it will be usera.PRF ContentsMailboxname=user, a userb.prf contents mailboxname=user, b So the common field to replace will be TEMPALTEPRF but replace with the relevatnt mailboxname depending on prf name. After looking around I found the following Param ( #$List = "C:\TemplatePRF\mailbox.csv", $Files = "c:\Test\*.prf" $ReplacementList = Import-Csv $UsernamesCSV; Get-ChildItem $Files | ForEach-Object { $Content = Get-Content -Path $_.FullName; foreach ($ReplacementItem in $ReplacementList) $Content = $Content.Replace($ReplacementItem.TEMPConstant, $ReplacementItem.mailboxn) Set-Content -Path $_.FullName -Value $Content At the moment all the files will then have the content "mailboxname=" set as user,a and it does not seem to loop through the remaining and replace correctly.

It looks like something along these lines should work, though the CSV data you posted is currently invalid. If you want to have a comma in a field (such as user, a), it needs to be quoted. Otherwise the comma is treated as a delimiter in the CSV. Generally,
I would recommend just quoting everything, to avoid problems. That's what PowerShell does when you use Export-Csv. Here's the test file I used:
"MailboxName","PRFNAME"
"usera, a","usera.prf"
"userb, b","userb.prf"
"userc, c","userc.prf"
I got rid of the TempConstant field; there's no reason to repeat that data on every line of the CSV file. It's just hard-coded in the script right now. Here's the code:
$templateFile = 'C:\TemplatePRF\template.prf'
$csvFile = 'C:\TemplatePRF\mailbox.csv'
$outputDirectory = 'C:\TemplatePRF\New'
if (-not (Test-Path -Path $outputDirectory -PathType Container))
New-Item -Path $outputDirectory -ItemType Directory -ErrorAction Stop
$templateContents = Get-Content -Path $templateFile -ErrorAction Stop
Import-Csv -Path $csvFile |
ForEach-Object {
$record = $_
$newContents = $templateContents -replace '(?<=mailboxname\s*=\s*)%TEMPLATEPRFUSER%', $record.MailboxName
$newFile = Join-Path -Path $outputDirectory -ChildPath $record.PRFNAME
Set-Content -Path $newFile -Value $newContents

Hi Gurus, I have a requirement to split the data into two csv file from a table using a threshold value(in Percentage) . Assume that If my source select query of interface fetches 2000 records , I will provide a threshold value like 20%. I need to generate a csv1 with 400 records(20% of 2000) and the rest of the records into another csv2. For implementing this I am trying to use the following process. 1) Create a procedure with the select query to get the count of records. Total Records count: select count(1) from source_table <Joins> <Lookups> <Conditions>; 2) Calculate the Record count to first CSV using the threshold_value. CSV1_Count=Total records count /threshold_value 3) Create a view that fetches the CSV1_Count(400) records for CSV1 as follows. Create view CSV1_view as select Col1,Col2,Col3 from source_table <Joins> <Lookups> <Conditions> Where rownum<=CSV1_Count; 4) Generate CSV1 file using View 'CSV1_View' 5) Generate CSV2 File using the Interface with same select statement (with columns ) to generate a CSV. select Col1,Col2,Col3 from source_table ST <Joins> <Lookups> <Conditions> Left outer join (Select Col1 from CSV1_View ) CS on CS.Col1=ST.Col1 where CS.Col1 is null; Which gives the Total records minus the CS1_View records. The above process seems a bit complex and very simple . If any changes in my Interface I also need to change the procedure (counts the no:of records). Please provide your comments and feedback about this and looking for your inputs for any new simple approach or fine tune the above approach. Thanks, Arjun

Arjun,
This are my thoughts and Lets do it in 3 Steps
Step 1. ODI Procedure
Drop table Temp_20 ;
Create table Temp_20 as select * from table where rownum < ( SELECT TRUNC( COUNT(1) /5) FROM TABLE ) .
[ ** This way iam fetching approx 20% of the table data and loading into Temp table . 1/5 th is 20% so i am dividing count by 5
I don't believe View will help you especially with RowNum as if you run the same query with rownum < N the rows order might differ . so Temp table is great ]
Step 2 . Use OdiSqlUnload with select columns from temp_20
Step 3 . Use again OdiSqlUnload with select columns from table where ( uk keys ) not in ( selecy uk_keys from temp_20)
[** this way you can pick the remaining 80% ** and the data will be not repeat itself across 20% and 80% , as might happen with view ]
what do you think ?

Hey all, I have two csv files residing on the server that I will be processing using the UTL_FILE package. One of these files (File A) has only one column and the other one (File B) has over ten. The data in file A is the same as the one in column 2 of File B. File A is mainly there for a lookup purpose whereas File B is the one that has all the data that will be processed. The following example should describe better: File A[b] Number "TR_56575" "TY_76756" etc File B Column1, Number, Column3, Column4 "Mine","TR_56575","uhsht","76744" "Yours","TY_76756","nghdjd","45645" What I have to do is check whether a Number in File B exists anywhere in File A. If it does then I just skip that record and move on to the next. So basically, I wanted to find out if there is any way to compare the two columns on the server side. I know there is a dbms_lob.fileexists function to check the existence of a file on a server, just wanted to see if there is an extension to that or something that checks the existence of a string in file. Any feedback would be appreciated. Thanks.

Yeah I thought about that. It would be the ideal way to go, however in this case, I already have a Pl/Sql procedure that grabs File B and just processes it from the server. It works fine and everything but now that this extra logic and file is added where I have to check the existence of a corresponding record in File A, I'm just looking for something to add in my code to just make the comparison on the fly and the rest would be the same.

Hi, How can I stop a long number in a CSV file that is opened in excel from appearing in scientific notation by default? eg. "hello","778002405501 ", "yes" becomes: hello | 7.78002E+11 | yes I have tried wrapping the data in quotes in the csv but to no avail. Thanks in advance, Alistair

Hi, i am trying to update an existing Item in SAP using a CSV file. in the message log i get an error message that the item already exists. what should i do in order to update the existing record? Thanks, Udi

Hi..........
I would sugest you to use Tab delimited file and choose proper option in order to update the itsm master in DTW......
Regards,
Rahul

Hi, One of my collegue loaded 361000 records from one file file , how is this possible as excel accepts 65536 records in one file and even in the infopackage the following are selected what does this mean Data Separator ; Escape Sign " Separator for Thousands . Character Used for Decimal Point , Pls let me know

hi Maya,
it just possible, other than ms-excel, we have editor like Textpad that support more 65k rows (and windows Notepad), the file may be generated by program or edited outside in excel, or newer version of excel is used, ms-excel 2007 support more 1 million rows.
e.g we have csv file
customer;product;quantity;revenue
a;x;"1.250,25";200
b;y;"5.5";300
data separator ;
- char/delimeter used to separate field, e.g
escape sign, e.g
- "1.250,25";200 then quantity = 1.250,25
separator for thousands = .
- 1.250,25 means one thousand two hundred ...
char used for decimal point
- - 1.250<b>,</b>25
check
http://help.sap.com/saphelp_nw70/helpdata/en/80/1a6581e07211d2acb80000e829fbfe/frameset.htm
http://help.sap.com/saphelp_nw70/helpdata/en/c2/678e3bee3c9979e10000000a11402f/frameset.htm
hope this helps.

Hi, I'm using BI 4.1 SP02. While using Rich client, I've created a report with some merged queries, while one of the queries is a local CSV file - saved on AD in some server, and not on the repository inside BO. While trying to refresh the report with the Rich client, it all went great. Now, while using BI Launchpad java based app, I can't refresh the report - I get the following error: "An Internal error occurred while calling 'processDPCommandsEx' API. (Error: ERR_WIS_30270) (WIS 30270)" Should I be able to refresh a report without the Rich if it contains a local file (which is possible to EDIT only with rich) ? If so, then did someone ran into this error? Thank you, Or.

First of all, thanks for both of the replies.
Second,
my problem is unlikely have to do something with permissions from one reasons -
when the report is using XLS\XLSX on same folder(with same name prefix) - the report is running without any problem.
Only problem is while refreshing without Rich while the source is network CSV file.
Any suggestions?
Thanks.

hi, I am using GUI_DOWNLOAD to convert the internal table that am getting as the output of an alv into a csv(comma separated file) file.I am using the following code but its not generating a csv file instead it is generating a normal space delimited file. The code is as follows: data : lv_fname type string. lv_fname = 'C:\Users\pratyusha_tripathi\Desktop\status8.csv'. " Provide the file path & file name with CSV extention CALL FUNCTION 'GUI_DOWNLOAD' EXPORTING filename = lv_fname " File name including path, give CSV as extention of the file FILETYPE = 'DAT' WRITE_FIELD_SEPARATOR = '#' " Provide comma as separator tables data_tab = ITAB " Pass the Output internal table FIELDNAMES = EXCEPTIONS OTHERS = 22 IF sy-subrc 0. MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4. ENDIF. Kindly let me know what changes can be made to make my code work.Also can GUI_download be used for batch processing and storing the output in application server? Thanks , Pratyusha

Hi,
the short text description for WRITE_FIELD_SEPARATOR is "Separate Columns by Tabs in Case of ASCII Download", so why do you expect a comma?
Try SAP_CONVERT_TO_CSV_FORMAT and then download.
And no, GUI_DOWNLOAD is only for download via SAP GUI to a users computer.
Best regards,
Oliver

Hi, My requirement is to generate a CSV file(or .XLS) on the client machine ie local drive with the details shown in a form. Oracle version - Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Solaris: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production. I have searched the web for last couple of days and got to know that TEXT_IO is to be used to generate files on client machine. However, when I tried TEXT_IO, it was not able to generate the file on client rather it was able to generate on database server. After further browsing on this, there was a link which said that we need to use CLIENT_TEXT_IO to generate file on client side. For this, i was required to subclass the webutil.pll which i did and corrected the code to use CLIENT_TEXT_IO. The form was unable to compile and was not able to find "webutil_core" package. I am very confused with the disparity in the information available on the web as in what to use to generate a file on client side. If anyone has use it in the past, can he/she please detail what to use to get things sorted. Thanks, R

Oracle version - Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production. >
So, what is your FORMS version. This is more important than your database version.
My requirement is to generate a CSV file(or .XLS) on the client machine ie local drive with the details shown in a form. Depending on your Forms version, you would use TEXT_IO (if Forms 6i running in Client/Server mode) or WebUtil (if Forms 9i or higher).
I'm going to guess that you are at least using Oracle Forms 9i since you stated that your attempt at using TEXT_IO produced a file on the DB server.
There is more to using WebUtil than just attaching the WEBUTIL.PLL. If you had performed a simple search of the Forms Help System would have found numerous WebUtil topics to include: Introduction to WebUtil, Configuring WebUtil, Using WebUtil in Your Applications and the WebUtil User's Guide. If you have Oracle Forms release 10g or higher, WebUtil is included when you installed Forms, however, you do need to configure your installation to use WebUtil and you must download the Java COM Bridge (jacob.jar) from Source Forge. Take a look at the Configuring WebUtil Forms Help topic to find out which version of the Java COM Bridge you will need to download.
After you have successfully configured WebUtil, take a look at the Using WebUtil in Your Applications topic to find out how to implement WebUtil in a form.
Searching the Internet for answers is great, but don't forget the look at the Forms Help System because the majority of your questions can be answered there. :)
Lastly, configuration of WebUtil is primarily done on your Application Server (AS). However, if you plan to perform preliminary runtime testing by running your Form from the Forms Builder, then you will configure your local runtime to support WebUtil as well as configure your AS. The steps are exactly the same. A common mis-step is to skip a step during the configuration because you don't think the step applies. Take a look at the Forms Help Runtime Setup Checklist topic for a list of step you need to complete in order to enable WebUtil.
Hope this helps,
Craig B-)
If someone's response is helpful or correct, please mark it accordingly.

Hi there I'm looking for some help in configuring a sender mail adapter that receives ".csv" files. I did read some blogs that mention using the "PayloadSwapBean" module to read the mail attachment instead of the mail content. My problem is to now convert the ".csv" file into a message. Is there a module that I can use ( is it the "MessageTransfomBean" ) and how. Any help would be appreciated. Thanks Salil

Hi Salil,
If you want to send a mail with a body and attachments, the message sender HAS to provide an XI message with attachments. I doubt a CSV file does justice.
As Renjith said you need to convert CSV to XmL.
A short description about the Standard Modules:
MessageTransformationBean is a standard module used to apply the XSLT mapping to the adapter module by using <i>Transform.class</i> ( This xslt mapping is done to create a mail package, Dont confuse with the actual mapping in your case this is NOT for converting csv to xml).
Also this module can be used to change the name and type of payloads by using <i>Transform.contentType</i>, <i>Transform.contentDisposition</i>, <i>Transform.contentDescription</i>.
PayloadSwapbean is a standard module for replacing payloads with other payloads (SWAP)
If you want to give each attachment a certain name use Parameters, <i>swap.keyname</i> for name of the payload and <i>swap.keyvalue</i>.
I Hope the use of standard modules is understood.

Maybe you are looking for

hi all, Im applying support pak SAPKU50008 including CRT SAPKITL421 in solution manager system. In the middle, at XPRA_EXECUTION phase it has cancelled without giving any error. I restarted the server still getting same problem. Here im using

My Macbook Pro (bought July 2012) doesn't see my wifi network at home but see others available. When I join other networks in different country (I travel a lot) and come home I can see it but when the mac goes to sleep I cannot connect again. Tried t

dear madam/sir i have a iphone 4 i want to check my phone original or fake model MC318LL/A AND S.R. 79034QM84S AND IMEI 012425008121464 AND ICCID 8991020113326384483 so can you tell how can i check my phone if you can check so plz tell me original or

Okay so basically I bought an album on iTunes and want to burn it to a cd, anytime I try its saying disc burner or software are not found, I've absolutely no clue what to do and would appreciate any help at all. Also I accidently deleted the album I