Hello All

The text file containing weekly sales comes from a legacy system which
reports negative numbers with the negative sign in postfix form rather
than prefix form as follows:


customer Sales Qty
1000000 100 220
1909110 82- 430
2010102 125 98-

Can you please suggest how I can transform all incoming postfix
negative numbers sign to a prefix notation as follows:

customer Sales Qty
1000000 100 220
1909110 -82 430
2010102 125 -98


Your help is highly appreciated.

Thanks
Karen

Don't do anything, there is no need. A simple CSV to SQL server with a
destination table of type int works fine. Suffixed signed numbers are valid
in DTS/SQL, or so it appears from my quick tests.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Karen Middleton" <karenmiddleol (AT) yahoo (DOT) com> wrote

Darren

Many thanks for the tip. But I want the suffix sign to be moved as a
prefix sign so instead of the data in the sql table being shown as 85-
I want it to be shown -85

Thanks
Karen

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

If you load the data into a s SQL Server integer column, then the position
of the suffix is not actually stored. The number is stored and the sign is
stored, a signed integer, but not the fact that it came in as a signed
integer and that sign was a suffix. It is only soired as a suffix if you
stire it as a character data type, which seems like a bad idea for what is
clearly int data.

When you then extract the data out to see where the suffix is, then the
format is determined by the client-side presentation layer.

Are you not importing the data into a system such as SQL which has strong
types?

If going from string to string, then write an ActiveX Script Transform. You
could do something like-

DTSDestination("Col1").Value = CStr(CInt(DTSSource("Col1").Value))

Only use script transforms when you really need too as they are a lot slower
than copy transforms.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Karen Middleton" <karenmiddleol (AT) yahoo (DOT) com> wrote