how to read and then delete a flat file on job server

[former_member186160]

hi all,

i have a requirement to read files from a folder on jobserver from a BODS job into an oracle server table.

this job to run once daily.

should read all files available at that moment.  [will use wild card to read file names like test*.csv ]

after reading, my BODS job should also delete all the processed files from the folder.

also i should make a note of those file names processed.

can anyone help me with this ?

[Former Member · Accepted solution]

Create a batch file with the following script and save it as test.bat (for example)

dir  \\sharedpath\newfolder\*.xls /a-d /b >\\sharedpath\newfolder\test.txt

what the above batch file will do it will read all the xls file names and write to test.txt file .

now you have the txt file .

so in BODS create a script with the follwoing code

print(exec('\\\sharedpath\\newfolder\\test.bat', '', 8));
sleep(4000);

make sure that above path there is double '\\'

now create a DF1 as below

now connect script to DF.

so when you execute it will run the batch file it will refresh the txt file with updated xls file names in that folder and write to test.txt file .

and then DF1 will get the file name and write to ZTEST table.

now you can use this table to read the file names and to load the data.

Regards,

Sree

[former_member186160 · Accepted solution]

hi all,

thanks for all the suggestions.

i could successfully read the file names using this script:

exec('/bin/sh','-c "find /usr/sap/test1/TEST* "',8)

but facing a weird issue while trying to delete each file after reading its contents.

i executed this script after reading each file:

$variable = file name

exec('/bin/sh','-c "find /usr/sap/test1/$variable "',8);

but this command has deleted the entire folder "test1"

and when i try the command: exec('/bin/sh','-c "find /usr/sap/test1/TEST* "',8),

getting message that the folder and file is not available.

pls suggest.

[former_member187605 · Accepted solution]

Try exec('/bin/sh','-c "find /usr/sap/test1/[$variable] "',8);

[former_member186160 · Accepted solution]

Dirk,

i already have tried this and this didnt work.

[former_member187605 · Accepted solution]

I am sorry, you confused me a bit by the way you wrote the find-command .

If I am not mistaken, the correct syntax for the find-command is:

     find /folder -name 'filename_and_or_wildcard'

So in your case, it would be:

     find /usr/sap/test1 -name 'TEST*'    

Or as of your notation:

     /bin/sh -c "find /usr/sap/test1 -name 'TEST*'"

Calling this from a script in DS:

     exec('/bin/sh -c "find /usr/sap/test1 -name \'TEST*\'"',8);

Or when you put the filename prefix in a global variable:

     $variable = 'TEST*';

     exec('/bin/sh -c "find /usr/sap/test1 -name {{$variable}"',8);

Good practice when developing script is inserting some debugging statements, like

     print('/bin/sh -c "find /usr/sap/test1 -name {{$variable}"');

The trace file will show the generated command:

          /bin/sh -c "find /usr/sap/test1 -name 'TEST*'"

If the command fails, you can copy-and-paste it into your Linux command line and run it manually. That may give you a better understanding of the reason why. 

[former_member186160]

thanks for all the replies.

this is how we resolved it.

script to move a file after processing

exec('/bin/sh', '-c "mv /test/{$GV_FILENAME} /test//PROCESSED/{$GV_FILENAME} "',8)

srcipt to delete a file

exec('/bin/sh','-c "find /test/'||FILENAME||' -delete"',8)

[former_member40112]

Hi Swetha,

can you help me understanding steps how to read excel file from UNIX server ?

Thanks, Brijesh

[Former Member]

Hi,

Check this

http://wiki.scn.sap.com/wiki/display/EIM/Selective+Reading+and+Postprocessing

For deleting you could use a .bat script calling from DS using exec() function.

http://stackoverflow.com/questions/13764103/batch-script-to-delete-files

Arun

[former_member186160]

Thank you Dirk & Arun.

i have a requirement to read all the file names in a specific folder in job server into a table, then for each file name in that table want to do some processing,

but i am stuck in the initial step of reading file names in the folder of job server.

i tried each of these below commands one after the other, commenting the previous one, but never got to accomplish my task.

can any one suggest me where i might be going wrong?

#exec ( cd /usr/sap/bods/project1/test );

#exec( 'cd' '/usr/sap/bods/project1/test');

#print ('test');

#print ( exec('cd', '/usr/sap/bods/project1/test'));

#print( exec ( 'ls', '-al'));

#Exec('cmd.exe',' Del "D:\File.xls",8) ;

#Exec('Del' ,' C:\Users\USER_XXX\Desktop\New Folder\test1.csv ') ;

#exec('del', 'C:\Users\USER_XXX\Desktop\New Folder\test1.csv', 8);

print ( 'hello');

#print ( exec('ls' , '/usr/sap/bods/project1/test') );

#print(exec('/sh','-c "find /usr/sap/project1/test/ "'));

#print (exec (' ls' ,' -d /*'));

#print(exec('/bin/sh', 'find /usr/sap/bods/project1/test//* '));

#print ( exec ('ls','https://server-address/usr/sap/project1/test/*'));

assigned the value to global variable $path = '/usr/sap/bods/project1/test'

print( exec('pwd', '', 8));

print ( exec('cmd.exe', 'ls [$path]' ));

#print( exec('pwd', '', 8));

#print ( exec ( ' ls ' , ''));

if (file_exists($path||'/20140121_101404132_846_INV_Daily.csv') <> 1)

begin

  print ('enter');

end

all the above commands failing with error message saying that program terminated with error code.

[former_member187605]

The examples given were "Assuming Windows platform". It seems you're running on Linux. Make sure the first parameter of the exec function is a genuine Linux command, 'sh' for instance.

[Former Member]

Hi Swetha,

You have to start the exec() function like this - exec ('/bin/sh',..............)

Arun

[former_member187605]

You can read multiple files in a single dataflow by specifying wildcards in the input file format.

Add a script at the end of your job for deleting the files. Assuming Windows platform:

     exec('cmd','del <path_name>\\*.csv');

If you don't have too many files (the output of the exec function is limited to 255 characters!), you could simply put

     print(exec('cmd','dir <path_name>\\*.csv'));

in the same script to write the names of your files to the DS trace file before deleting them. .