Sounds like it's going into Excel as text to me.

So if you remove all display formatting in Excel, what does the field actually contain?

OK, if your table is called t1, this should do it:

with t as (

select distinct ID, Max(Cost) Cost

from T1

group by ID

)

select t1.ID, max(t1.Cost), Max(t1.ItemNumber) from T1

join T on T1.ID =...

If you can use SQL Server to do the work:

Select Convert(Varchar(10) , Cast('12-OCT-2009 18:23:20' as Datetime), 103)

--edit - oops, misread your post. That's not a date datatype 🙁 ignore me.

OK, try

select ID, Max(Cost), Max(ItemNumber)

from ...

Group By ID

If there is more than one item number, as in the case of

2 30 D

2 30 EE

which item number would you want to select? D, EE or does not...

Don't do the deinstall, it won't fix this.

Sounds like an Excel driver 'feature', see here[/url]. You could try the solutions offered there. Or ...

Try adding a column to the right...

Maybe you could add the variables as derived columns in your dataflow and then use a Data Viewer ...

That was what I was trying...

Tim, I think you may have misunderstood the requirement.

Sounds to me like the OP needs to read up on the use of FOREACH containers in iterating round folders of 0...

Please post the command line which executes the job.

SQL Learner-684602 (10/22/2009)

---

Thanks a lot... Phil..Erik

it works....... 😎

I have one more question,

Is there any way to split this long string into columns partitioned by DOTS

For Ex:

ab.cd.ef.gh

gives

Col1 Col2 Col3...

Same logic ... must be good! 🙂

OK, maybe this works:

declare @a varchar(100)

set @a = 'a.b.c.d.f.g'

SELECT REPLACE(SUBSTRING(@a, charindex('.', @a), LEN(@a) - charindex('.', REVERSE(@a))-1), '.','')

SELECT SUBSTRING(REPLACE('ab.cd.ef.gh.kl','.',''), 3, len(REPLACE('ab.cd.ef.gh.kl','.',''))-4)

SELECT SUBSTRING(REPLACE('hh.jj.kk.rr.ee.bb','.',''), 3, len(REPLACE('hh.jj.kk.rr.ee.bb','.',''))-4)

You didn't remove the left and right 2 characters 🙂

--edit aaah, just seen the latest post - that's tougher