Load from sequential files

hondaccord94 · Post by **hondaccord94** » Mon May 09, 2005 3:29 pm

Hai,

I have a situation here and I dont know how to tackle this in windows.
I have a DS server job that loads from a .csv file into a oracle table.
There are some days, that the data comes in one file and some days they come in two or more files. So my requirement is there a way I can automate this process, like, to find all the files in that particular folder and load from one file after the other.
Please advise.

Thanks
Sandeep

chulett · Post by **chulett** » Mon May 09, 2005 8:04 pm

There are different ways to approach this and it depends on what you are comfortable with.

DataStage has a Folder stage that can be used to bring a list of filenames in from a directory. Those filenames can then be passed to another job or routine that, one by one, processes them. One problem, though, is the stage will also want to pass the entire contents of the file through as well. It was really built to handle XML data, hence the behaviour, which is meant to feed the XML Reader stage from what I recall.

Or you can go the Job Control / scripted route. You could consider taking any and all files found in the folder that match your criteria and concatenating them into one file - and then processing that file. Or manually looping through a directory listing and launching the processing job over and over, passing in the next filename as a job parameter. That would depend on how comfortable you were writing either job control code in DataStage or batch files in Windows.

ray.wurlod · Post by **ray.wurlod** » Tue May 10, 2005 12:40 am

Welcome aboard! :D

If the muliple files are all in the one directory, all are governed by the same metadata ("table definition") and are moved out of there after processing, you can still use a Sequential File stage. Simply specify a filter like type *.* - output from this command will become the "stdin" of the Sequential File stage. On UNIX you'd use cat *

hondaccord94 · Post by **hondaccord94** » Tue May 10, 2005 9:33 am

Ray/Chulett

Thanks for your help. I will try all the three options and see which suits my reqs. I will get back to you guys if I find any probs.
Thanks once again

hondaccord94 · Post by **hondaccord94** » Tue May 10, 2005 9:39 am

Sorry Craig, I got ur name wrong.

chulett · Post by **chulett** » Tue May 10, 2005 10:30 am

No problem, happens all the time.

Ray's suggestion of leveraging the Filter command of the Sequential file stage would be best, I would think. Only issue after that would be how you keep it from reprocessing the same files later. And the answer to that will depend on how 'dynamic' the list of files in that folder are. :D

You'd have that issue with any of the suggested methods, however.

hondaccord94 · Post by **hondaccord94** » Tue May 10, 2005 12:38 pm

Ray/Craig,

I am trying to use the filter command option now ,that Ray quoted. I have a question, this might sound silly. But I hope you can help me with this.

Say for, I am trying to search for the files with the extension 'txt', in this folder E:\dev\srcdata. I guess I dont have to give anything in the filename option. And in the filter command window. I gave this command
E:\dev\srcdata\type *.txt. This doesnt seem to work. It is throwing me an error. Please advise.

Thanks
Sandeep

chulett · Post by **chulett** » Tue May 10, 2005 12:52 pm

Try this instead:

Code: Select all

type E:\dev\srcdata\*.txt

hondaccord94 · Post by **hondaccord94** » Tue May 10, 2005 1:00 pm

Ray,

Tried that, it throws an error "no file name to open". Do I have to give anything in the file name option. And for your info my test job looks like this

seq_file_stg -----------> transformer ----------------> seq_file_stg
(straight moves)

Please advise.

Thanks
Sandeep

chulett · Post by **chulett** » Tue May 10, 2005 1:08 pm

You can call me Ray if you like.

Yes - you do have to put something in the filename box. As far as I know it doesn't use it, but you need something there.

hondaccord94 · Post by **hondaccord94** » Tue May 10, 2005 2:43 pm

Craig,

Am sorry again...

.

I tried doing that now I am getting this error.

ds_seqopen() - Win32 error in CreateProcess() - The system cannot find the file specified.

Please advise

Thanks
Sandeep

chulett · Post by **chulett** » Tue May 10, 2005 2:53 pm

That should mean that there are no files in your directory that match your wildcard pattern.

What happens if you take your exact same filter command and execute it from the command line in a 'Cmd window'? I'm guessing you'll get the exact same error.

hondaccord94 · Post by **hondaccord94** » Tue May 10, 2005 3:10 pm

Craig,

I have given the same command in the cmd window. It works fine. I am not sure why it is not working in DS.

I gave the complete file name in the file name option window and the wildcard filter type <dir>*.txt. I tried giving jus the filter option too.
No use.

I guess, I am making some sense here.

Thanks for ur time
Sandeep

chulett · Post by **chulett** » Tue May 10, 2005 4:48 pm

Unfortunately, I don't have a Windows based server to try this out on. It "should" work fine once you figure out whatever the issue is, as I've done things like this from the UNIX side more than once in the past.

Sorry.

hondaccord94 · Post by **hondaccord94** » Wed May 11, 2005 10:00 am

Craig,

I am going with the Folder Stage. It seems to work. 'Coz my boss says I have spent too much time on that. Thanks for ur help.

Sandeep