import a textfile to sql using DTS 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

import a textfile to sql using DTS 2005

Author

Topic

vswvsw
Starting Member

13 Posts

Posted - 2011-08-22 : 05:42:31

I imports a text file to my temporary table tempA.
The field to which i import is PRICE nvarchar(256),I have a negative number 99.00- from the text file.Importing is done fine. From tempA i load to another table temp2 where i have PRICE Decimal(15,3). The trailing negative numbers is showing errors during the conversion.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-22 : 05:50:32

While loading from tempA to Temp2, replace the contents of PRICE column to remove trailing '-' character and add it as prefix:

(case when charindex('-',price) > 0 then '-' else '' end + replace(price, '-','')) as PRICE

Harsh Athalye
http://www.letsgeek.net/

vswvsw
Starting Member

13 Posts

Posted - 2011-08-22 : 06:20:06

Thank you for your reply
i am doing a bulk insert
insert into temp2
(...
...
price)
select
..
..
replace(Temp1PRice,',',''),
from temp1
I can do in this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-22 : 06:23:36

Yes. You need to run the query after you bulk-inserted in tempA.

Harsh Athalye
http://www.letsgeek.net/

vswvsw
Starting Member

13 Posts

Posted - 2011-08-22 : 06:26:20

also can we have checks while importing from textfile to temp1
eg if i want to trim datas,check whether numeric etc..can i do it during DTS. Is there any Transformations for that

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-22 : 06:37:43

Yes, you can use derived column transformation to do those kind of tasks.

Harsh Athalye
http://www.letsgeek.net/

vswvsw
Starting Member

13 Posts

Posted - 2011-08-22 : 06:47:57

Thank you.. I am doing it...
if i want two checks to be carried for one coloumn then can i have 2 expressions in one line...
I am new to DTS.

vswvsw
Starting Member

13 Posts

Posted - 2011-08-22 : 06:59:41

can i solve the trailing sign problem issue also using derived column transformation
if so can you please help me out

vswvsw
Starting Member

13 Posts

Posted - 2011-09-06 : 04:47:35

hi all,
I am not able to resolve the trailing negative sign issue. When i follow the above procedure i am getting error converting datatype nvarchar to numeric.. Please help me out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources