Load N files into a single table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
Load N files into a single table
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!
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!
Karthik
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
Yes! All of them have the same structure.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 ...
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?
Karthik
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
You mean to saykarthi_gana wrote:Yes! All of them have the same structure.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 ...
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?
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?
Karthik
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
You can use wildcards if that's easier.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
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?
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?
Karthik
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 729
- Joined: Tue Apr 28, 2009 10:49 pm
i mean i have kept all those files in a single folder....so i used FOLDER STAGE...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.
Can you tell me which stage that i have to use for this kind of scenario
?
Karthik
http://www.google.com.au/search?sourcei ... ull+devicekarthi_gana wrote:what .\NUL means?
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
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues