September 2, 2004 at 2:32 pm
I am importing a text file with '~' column delimiter and my text has in the file has some format.If i import the data into the sql table all the text is appearing in one line by missing the formart.
(here is the sample data.
empid~empname~emp_txt*
1~jjjj~kkkgkhfdhg
hhhghg
hoghodshgohdsoiahgfhoghojhqgh*
2~3333~gdskhkgjh
ghfdgh
fkjhbdskjhkghdghah*
)
IF i import the above data into a table with emp_txt as text data type
i am getting
kkkgkhfdhg
hhhghg
hoghodshgohdsoiahgfhoghojhqgh
all in one row like:kkkgkhfdhg hhhghg hoghodshgohdsoiahgfhoghojhqgh.But i should get it same as in the notepad.
for that i have changed the datatype of the emp_text column as image in my database.
But when i change the datatype my DTS is giving the following error:
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error,Column3('emp_text',DBTYPE_BYTES),status 2: Error converting value.
Invalid character value for cast specification.
September 3, 2004 at 12:37 am
Hie
try to customise this strored proc that I got from http://www.sqlteam.com.I think it will solve your problem
Create procedure sp_ParseArray
( @Array varchar(1000),
@separator char(1) )
AS
-- Created by graz@sqlteam.com
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int
-- This is used to locate each separator character
declare @array_value varchar(1000)
-- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator
-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin
-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
select Array_Value = @array_value
-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
set nocount off
go
September 3, 2004 at 1:39 am
Set your import to use the '~' as the column delimter and '*<cr>' as your row delimter. By default it will be thinking the row delimter is a <cr> or <cr><lf>
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply