HOW TO LOAD DELIMITED FLATFILE INTO TARGET TABLE!!

[anjidwh.cognos]

Hi

we have to add another job2 to load the same target table from a file. The file is a delimited file. We have four columns in target table.


a.   column1 – do not load
b.   column2
c.   column3
d.   colum_flag

It would be a separate job and then the other job1 should run. Also, update the DM_TARGET to not over lap the column_flag if it is already set to Y.

Can any body give steps how to achieve this. Please help me out to get out of this. Appreciated your help!!!

Thanks & Regards

[MFGF]

Hi,

1. Use SQLTXT Designer to define the location and structure of your delimited file, and save the definition of this as a .def file.
2. Add a Connection to your catalog with a type of SQLTXT, pointing to your .def file.
3. Create a Fact Build to read in the data from the new connection and deliver it to your target table.
4. Add your fact build into a new jobstream.

Good luck!

MF.

[anjidwh.cognos]

Thanks for your response.

Do we have any other way to do this or else only this the way?

Regards

[MFGF]

Thanks for your response.

Do we have any other way to do this or else only this the way?

Regards

It's not the only way but it's probably the easiest. Other ways I can think of are to get hold of an ODBC text driver and use this to define a connection to your flat file, then use a fact build to load this into your target table.

What's the issue with the original approach? Is it something that doesn't work in your situation? Can you explain more?

MF.

[anjidwh.cognos]

This is fine. But my question is when the data changes happen in PROD.Because, we have 10 row of data in flatfile as of now. But the data will change in PROD right? Does it make sense? .

Can you please tell me how to do this?

Regards

[MFGF]

This is fine. But my question is when the data changes happen in PROD.Because, we have 10 row of data in flatfile as of now. But the data will change in PROD right? Does it make sense? .

Can you please tell me how to do this?

Regards

Sorry - not quite following what you are asking here. Can you explain in terms a muppet can understand? :)

MF.

[anjidwh.cognos]

Sorry to get confuse!! I mean to say that...

We change 'flat file' into '.def' file and I loaded the flat file using 'SQLTXT' into target table.

For the first time flat file consists only 10 rows.

If flat file data is changed in second time and  it consists of 30 rows now. My question is...

Does it require to change this flat file also into .def file and then load into target or it automatically done?

Regards

[MFGF]

Sorry to get confuse!! I mean to say that...

We change 'flat file' into '.def' file and I loaded the flat file using 'SQLTXT' into target table.

For the first time flat file consists only 10 rows.

If flat file data is changed in second time and  it consists of 30 rows now. My question is...

Does it require to change this flat file also into .def file and then load into target or it automatically done?

Hi,

The SQLTXT def file describes the structure of the flat file only. As long as the updated flat file has the same structure as the original one, you can continue to read from it as normal with no issues and no need to re-define it. If you add a field or change the size of a field, however, you would need to update the DEF file ro reflect this before you can read from the flat file successfully. As long as the structure of the file remains constant, you can simply run the fact build again to read the new contents of the file and deliver them to the target table.

Regards

MF.

[anjidwh.cognos]

If I don't have flat file in the specified location, even though I have to make the job get success.
Can you please advise how to achieve this ?

Regards

[MFGF]

I'm not quite sure what your requirement is here? You could use the filecheck() function to determine whether the file exists - is this what you mean?

Cheers!

MF.

Sent from my iPad using Tapatalk HD

[anjidwh.cognos]

yes exactly. Can you suggest how to do this.

Regards

[MFGF]

The approach I would use is to use filecheck() in a condition node, and use the True output to fire off the build that reads the file. Use the False output to either stop the Jobstream, bypass the build in question, or maybe loop around via a procedure node that introduces a pause for whatever period you deem appropriate.

Cheers!

MF.

Sent from my iPad using Tapatalk HD

[anjidwh.cognos]

While i am extracting flat file, for one of the column I  had 8 digit number set with preceding zeros like: 00000025.
But it is trimmed while I am extracting from select statement and show like:25 only.

Can anybody advise not to trim and get original data what the flat file consists.

Regards

[MFGF]

While i am extracting flat file, for one of the column I  had 8 digit number set with preceding zeros like: 00000025.
But it is trimmed while I am extracting from select statement and show like:25 only.

Can anybody advise not to trim and get original data what the flat file consists.

Regards

Why have you posted this here when you have a separate thread for this question?

MF.