> Hey All,
> I hoping someone can help me import a text file with 2
> columns where the first column has a fixed width of 4 and
> the last column does not have a fixed width, but the
> record has a row delimiter of a return character. I want
> to use the fixed field properties and add a vertical line
> to split the first and last column, but I can not seem to
> find a way for DTS to notice the return charter at the end
> of the record. I am assuming this is possible since I can
> do this in MS Access.
> I have attached a portion of the file.
> Thanks for your help ahead of time.
Your data is a little unusual since it appears to be hierachical in nature.
The procedure code 0101 is a child of 010 which is a child of 01.
I would use a delimited import, specifying the column delimiter of space,
and a row delimiter of CR+LF. You can then use an ActiveX transformation to
strip or re-pad the spaces at the end of procedure code and the begining of
the procedure description. You could also strip the * from the end of the
Import the data as a single delimited column with a row delmiter of CD+LF
and nor column delimiter. Then use T-SQL string manipulation to split into
two fields and insert into your final destination. This would also be the
easist method if you wish to normalise your data into three tables.
Darren Green (SQL Server MVP)