May 19, 2003 at 7:58 am
I have an old system that I need to import data from. The database exports to a text file; each field is a set size. However the size and number of fields after the 1st 6 depends on what is in the 2nd field.
I imported the "mixed data" at the end into one field in a holding table hoping to be able to split it depending on the record in the 2nd field? How can I do this if at all possible?
Thank you for any help you can give me!
Brian
May 19, 2003 at 8:03 am
Hi,
it should be possible to use conditional processing depending on what value is in the second field. Can you provide more information on what value field 2 can contain and what should be done with it?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 19, 2003 at 8:17 am
Thanks for the help,
Field 2 contains 1 character. A,B,C,D,or E.
In its simplest form - if that field = D then the last field will be split into 2 fields, one that is 60 characters and the other set to 87. There may be no data at all in some of the fields imported but the 2nd field always contains a value of A-E.
If you need more info please let me know and thank you again for the help.
Brian
May 20, 2003 at 12:34 am
Hi,
sorry for being that late!
You can write a script in QA to handle this. As this is hopefully one-time action, I think a cursor is quite ok for it.
use zz_fai_play --make sure you're using the correct db
go
declare @last_field varchar(255) -- variable to hold the string to be splitted
declare @condition char(1) --variable to hold value of field 2
declare @new_field_first varchar(60)
declare @new_field_second varchar(87)
DECLARE NEWCURSOR CURSOR FOR
SELECT <field2,lastfield> FROM <yourtable>
OPEN NEWCURSOR
FETCH NEXT FROM NEWCURSOR INTO @condition, @last_field
WHILE ( @@FETCH_STATUS<>-1 )
BEGIN
if @condition='D' --you can add more conditions to handle A,B,C,E
begin
set @new_field_first = left(@last_field,60)
set @new_field_second = substring(@last_field,61,87)
--here should go some validation action and then some insert action
end
FETCH NEXT FROM NEWCURSOR INTO @condition, @last_field
END
DEALLOCATE NEWCURSOR
You maybe have to modify this a little to fit your needs, but then it should work
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 20, 2003 at 5:37 am
Thanks a bunch. You probably saved me a few days more of frustration!
Brian
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply