Data Validator checking name of fields

[KettleFan]

Hi,

i want to use the Data Validator for follow problem :
I have Excel Files with Columns. The name of colums can be change and the position and the number of it.

With the Data Validator i can only check when i know the name of fields.
I want not only check the content but also the name of the columns and if the columns on the right place.

if new columns in the excel files i want to create the columns in the database and the DataValidator give a message for all that.

Is there any solutions ? Maybe with an other step ?

[sboden]

If you just want to check column names... read in the excel switching of the header in the excel input step and reading 1 row. Then you have the column names normally.

If you want to process the data as well use an extra transformation.

Regards,
Sven

[KettleFan]

Thx thats a good idea.

Another Question. Can i put a mapping table into a database and read it with a transformation to use it for an input step ?`

Table looks like :

Column 1, Column2
money, allmoney
time, worktime
give_me_more, all_in

and so on. So i can always use one Mapping Table for different transformations.

[sboden]

No... and for the moment with a reason... most of the meta-data in PDI is static, if you would make 1 part dynamic you would get stuck in the next parts.

Regards,
Sven

[KettleFan]

If you just want to check column names... read in the excel switching of the header in the excel input step and reading 1 row. Then you have the column names normally.

If you want to process the data as well use an extra transformation.

Regards,
Sven

I try this but the result was not satisfied. I read the Excel File. Header off and Limit 1. Preview rows show me 2 records not one. I dont know why.

Next Problem in other Excel File the Header is not in one Excel row but split in 2. So i must read 2 records. The File have Header and Sub-Header. Need both to check this.

@Data Validator developer
Can u upgrade this step so i can check in excel on this position the header must be there.
I want only check whatever has change to the last execute of the transformation and that show me.

A loyalty user needs help :-)

[sboden]

I try this but the result was not satisfied. I read the Excel File. Header off and Limit 1. Preview rows show me 2 records not one. I dont know why.

Write the output to a file... if there are still 2 rows, come back

Next Problem in other Excel File the Header is not in one Excel row but split in 2. So i must read 2 records. The File have Header and Sub-Header. Need both to check this.

No idea why anyone would want to use headers split over multiple lines in excel. Sub headers ok, but what you're going to do wit them, it probably won't process anyway in PDI.

@Data Validator developer
Can u upgrade this step so i can check in excel on this position the header must be there.
I want only check whatever has change to the last execute of the transformation and that show me.

Don't think that will work... if you process data from excel the normal way you don't see the header in the input rows. The metadata for the Excel step is fixed at design time, at the moment you do the "get fields" in the Excel input dialog.

Regards,
Sven

[KettleFan]

Write the output to a file... if there are still 2 rows, come back

I'm back are still 2 rows after output to a file. First row header second row first recordset.
Need only the header record.

No idea why anyone would want to use headers split over multiple lines in excel. Sub headers ok, but what you're going to do wit them, it probably won't process anyway in PDI.

I dont know why but it is. I want to check this if changed anything since last time.

Don't think that will work... if you process data from excel the normal way you don't see the header in the input rows. The metadata for the Excel step is fixed at design time, at the moment you do the "get fields" in the Excel input dialog.

Regards,
Sven

Yes it is fixed at design time but i want not to execute once ! I think every week i get a new file and i want to create a job to start the transformation to import the data. I want to guarantee that nothing has changed since the last time. For every week and every file create a new transformation or update the transformation (preview rows, data field names ) cost a lot of time that i think isn't neccessary.
Maybe the file header and data change every half year.
So i need a Data Validator or anything else.

Greetings

[sboden]

Limit 1 not working I would consider a bug http://jira.pentaho.com/browse/PDI-1921
As work around add a "add sequence" step and a filter step on 1.

Regards,
Sven

[KettleFan]

Limit 1 not working I would consider a bug http://jira.pentaho.com/browse/PDI-1921
As work around add a "add sequence" step and a filter step on 1.

Regards,
Sven

Thanx a lot !

Yes it is fixed at design time but i want not to execute once ! I think every week i get a new file and i want to create a job to start the transformation to import the data. I want to guarantee that nothing has changed since the last time. For every week and every file create a new transformation or update the transformation (preview rows, data field names ) cost a lot of time that i think isn't neccessary.
Maybe the file header and data change every half year.
So i need a Data Validator or anything else.

This is still open. I hope it can implement in a next version from pdi. I think i'm not alone to have this feature.

[sboden]

This is still open. I hope it can implement in a next version from pdi. I think i'm not alone to have this feature.

Not likely .. but you can raise a JIRA of course.