Page 1 of 1
Load N files into a single table
Posted: Wed Apr 29, 2009 12:12 am
by karthi_gana
Hi All,
I am newbie to this forum as well as to ETL concepts.
I have a set of files ( the count may be 6,8 or 3. It depends.) stored under one folder(say for example D:\karthik). We don't know the exact file count in the folder.
But the thing is we need to upload all those files into a single table.
How should i achieve this?
Inputs are welcome!
Posted: Wed Apr 29, 2009 12:28 am
by ray.wurlod
Do all files have the same structure (metadata)? If so, probably the easiest approach is to use one Sequential File stage with a Filter command (such as TYPE file1 file2 file3... ) to capture all rows from all files.
Posted: Wed Apr 29, 2009 12:36 am
by karthi_gana
ray.wurlod wrote:Do all files have the same structure (metadata)? If so, probably the easiest approach is to use one Sequential File stage with a Filter command (such as TYPE file1 file2 file3... ) to capture ...
Yes! All of them have the same structure.
HDR0001
ENO, ENAME, SALARY,
1,aa,3000,
2,bb,1000,
3,cc,4000,
TRL0001
like above i have 6 files( the count may differ in future).
i am not getting you...
where i have to type FILE1,FILE2,FILE3 etc.,
can you be more specific?
Posted: Wed Apr 29, 2009 12:39 am
by karthi_gana
karthi_gana wrote:ray.wurlod wrote:Do all files have the same structure (metadata)? If so, probably the easiest approach is to use one Sequential File stage with a Filter command (such as TYPE file1 file2 file3... ) to capture ...
Yes! All of them have the same structure.
HDR0001
ENO, ENAME, SALARY,
1,aa,3000,
2,bb,1000,
3,cc,4000,
TRL0001
like above i have 6 files( the count may differ in future).
i am not getting you...
where i have to type FILE1,FILE2,FILE3 etc.,
can you be more specific?
You mean to say
1) I have to tick 'stage uses filter commands' under stage table.
2) in the output tab, i have to enter something in the 'Filer Command' bax.
Am i corect?
Posted: Wed Apr 29, 2009 12:40 am
by ray.wurlod
In the Filter command field in the Sequential File stage, which becomes visible when you check the "stage uses filter command" check box.
You can use wildcards if that's easier.
Posted: Wed Apr 29, 2009 12:46 am
by karthi_gana
I have given like below
FileName:
#LoadFileDirectory#apl.txt
I have six files.
D:\Karthik\apl_c_28apr2009.txt
D:\Karthik\apl_u_28apr2009.txt
D:\Karthik\apl_c_27apr2009.txt
D:\Karthik\apl_u_27apr2009.txt
D:\Karthik\apl_c_26apr2009.txt
D:\Karthik\apl_u_26apr2009.txt
#LoadFileDirecotory# = D:\Karthik
I set it in the Job Properties.
can you tell me what i have to enter in the 'FileName' textbox
and 'FilerCommand' textbox?
Posted: Wed Apr 29, 2009 1:04 am
by ray.wurlod
FileName: .\NUL
Filter Command: TYPE D:\Karthik\apl_*.txt
Posted: Wed Apr 29, 2009 1:48 am
by karthi_gana
ray.wurlod wrote:FileName: .\NUL
Filter Command: TYPE D:\Karthik\apl_*.txt ...
I am getting error message...
MTD_Performance..Folder_0.: DSD_DIROpen Folder Stage output link cannot have more than 2 columns.
MTD_Performance..Folder_0: DSD_DIROpen set &Folder& to
what .\NUL means?
[/img]
Posted: Wed Apr 29, 2009 2:00 am
by ray.wurlod
Who mentioned a Folder stage?
If this is a different problem, you must start a different thread. Otherwise future searchers will be confused.
Posted: Wed Apr 29, 2009 5:55 am
by karthi_gana
ray.wurlod wrote:Who mentioned a Folder stage?
If this is a different problem, you must start a different thread. Otherwise future searchers will be confused.
i mean i have kept all those files in a single folder....so i used FOLDER STAGE...
Can you tell me which stage that i have to use for this kind of scenario
?
Posted: Wed Apr 29, 2009 6:13 am
by mahadev.v
ray.wurlod wrote:In the Filter command field in the Sequential File stage, which becomes visible when you check the "stage uses filter command" check box. ...
Posted: Wed Apr 29, 2009 6:34 am
by chulett
You need to read the help on the Folder stage so you know how it works and thus know when to use it.
Posted: Wed Apr 29, 2009 11:23 pm
by sbass1
karthi_gana wrote:what .\NUL means?
http://www.google.com.au/search?sourcei ... ull+device
The sequential file filter stage pipes the Filename thru the Filter and reads the output. So, if you pipe the null file into your filter, your filter can then logically concatenate your physical files into one input stream.
Try this in a command window:
Code: Select all
type .\NUL | type D:\Karthik\apl_*.txt
Do you get your physical input files logically concatenated together in the command window?