Load N files into a single table

[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!

[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.

[karthi_gana]

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?

[karthi_gana]

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?

[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.

[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?

[ray.wurlod]

FileName: .\NUL
Filter Command: TYPE D:\Karthik\apl_*.txt

[karthi_gana]

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]

[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.

[karthi_gana]

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
?

[mahadev.v]

In the Filter command field in the Sequential File stage, which becomes visible when you check the "stage uses filter command" check box. ...

[chulett]

You need to read the help on the Folder stage so you know how it works and thus know when to use it.

[sbass1]

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?