DSXchange

Hi All,

I have a CSV file with only 1 row containing 10 columns .

all the columns have data type as Integer.

My requirement is to pass only the columns in the target CSV file having value not equal to zero.

I'm not quite sure what your requirements really are; but text files need to be read line-by-line, so the reader sequential stage will read the whole line with all of the columns. After that, one can selectively remove columns from the stream that have specific values. But how about column naming and should each row (assuming the file gets more than one row) have a different number of columns?

Write the output record as one string field and craft it in a transformer from the appropriate source columns. Concatenate them together with the appropriate field delimiter between them.

Let me clarify more

Source contains only 1 row with 5 columns
A,B,C,D,E
0,5,4,0,1

Want output as whose value are not Zero

B,C,E
5,4,1

(see previous answer)

Craig

Can you pls elaborate your answer i didnt got it

Which part is unclear? Build the output record yourself. Leave out the zero values. Write it out as one long string. Ask specific questions if you still need help.

To build on Craig's answer, this is what you could do

That's close to what I was thinking.

It doesn't need to be read as one column so much as written out that way. And I don't think the Column Import stage is needed. As the final step I would trim off the 'extra' trailing delimiter you'd get from concatenating the columns and delimiters together:

You can try doing it in Unix (assuming that your environment is unix). an awk script can brilliantly come to your rescue. But if you need column names, that will be complex as the selection of columns is dynamic here.

You can alternatively use Craig's solution which has been discussed in one of my posts (link below):
viewtopic.php?p=443641#443641

I'm thinking you would handle the column names the same way... dynamically paired with the matching non-zero values. It would need to be written out as a row of data rather than automatically by the stage, so perhaps a horizontal pivot? Or a funnel to bring the two rows together.

Does your source always have 1 row or it can have multiple rows too? In that case how do you want your column names to be populated?

lna_dsuser wrote:Source contains only 1 row

I'm assuming that means always. The required output won't make much sense with multiple rows.

You can read the one row as a number of rows ( with only one column of type integer ) if your Record Delimiter property is set to Comma, in the transformer , pass only rows where integer data is not zero ( Using a transfomer constraint Or Filter stage Where clause) and write this to another file with record delimiter as a Comma .
For both file stages ( source and target) set the field delimiter and quote character to 'none'

To Access record Delimiter property , go to Format tab of the sequential file, and choose the Record Level property folder node, change the record delimiter from Unix Newline to Comma (,) .