Newbie Question Concerning Importing a Text File

Newbie Question Concerning Importing a Text File

Post by Brad Feldma » Wed, 19 Jun 2002 23:54:38



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.
Brad

  data.txt
12K Download
 
 
 

Newbie Question Concerning Importing a Text File

Post by Darren Gree » Thu, 20 Jun 2002 01:10:19



Quote:> 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.
> Brad

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
description.

Or

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)
http://www.sqldts.com

 
 
 

Newbie Question Concerning Importing a Text File

Post by Brad Feldma » Thu, 20 Jun 2002 03:15:52


Darren,

Thank you for your reply back.  I was trying to import the
data the way you suggested but I do not have the ability
in DTS to select CR+LF or CD+LF (neither of these options
are in the drop down for row delimited).  Can you explain
how I can fix this?

Thanks again,

>-----Original Message-----


message

>> 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.
>> Brad

>Your data is a little unusual since it appears to be

hierachical in nature.

- Show quoted text -

Quote:>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
>description.

>Or

>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

- Show quoted text -

Quote:>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)
>http://www.sqldts.com

>.

 
 
 

Newbie Question Concerning Importing a Text File

Post by Darren Gree » Thu, 20 Jun 2002 03:46:16




Quote:>Darren,

>Thank you for your reply back.  I was trying to import the
>data the way you suggested but I do not have the ability
>in DTS to select CR+LF or CD+LF (neither of these options
>are in the drop down for row delimited).  Can you explain
>how I can fix this?

{CR}{LF} is the same thing.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

 
 
 

1. Text file import strategy - DTS newbie question

I have an ascii file that has no column delimiters. Fixed-
fields are present but the data is homogeneous over two
lines(separated by CrLf), not one.

Also, the report contains page headers, etc with a CrFF
between each page.

Any DTS strategies would be appreciated.
Currently, I am using a Connection (Text File (Source))
and an ActiveX script within a Transform Data Task.

2. identify duplicates

3. newbie question: import a table from a text file

4. Dynamic Parameter Assignment for Stored Procs -Ideas??

5. Newbie : import text file into several tables

6. Newbie Question: Which String Function?

7. Newbie: import text files but do concatenating first.

8. How to implement parent-update-cascade on SQL 7?

9. Newbie question: Text import

10. Newbie question - Importing file with fixed length data ...

11. Newbie Question concerning ODBC-connection to Oracle

12. Stupid Newbie question concerning Database Design

13. import/export file to LOB (newbie question)