MS SQL Server DTS - DTS IMPORT TXT FILE

Author

DTS IMPORT TXT FILE

Peter Neumaier

2006-01-20, 3:23 am

Hi NG!

I'm trying to import some data from a txt file, containing in the first
column a string and the others having (floating point) numbers:

column1 column2 column3 column4 column5
column1 67 column3 column4 column5
column1 123 column3 column4 column5
column1 5432 column3 column4 column5

..
..
..
column1 column2 column3
column1 column2 column3

The first couple of lines have 5 columns (in real at least 15, I
reduced it here to make it easier), later in the file, the columns
shrink to 3. My problem is that my DTS package screws up the data from
the lines with 3 columns, in my table it looks like this, the lines
with
5 columns are fine, but later the lines with 3 columns are totaly
messed up:

column1 column2 column3 column4 column5
column1 column2 column3 column4 column5
column1 column2 column3 column4 column5
..
..
..
column1 column2 column3 column1 column2
column3 column1 column2 column3 column1

DTS adds up the empty columns with columns from the next line ....

My importing settings are as following (I played around with all
possible combinations of these settings, no one really led to success):
-Columns are defined as "Fixed field" --> has to be fixed, pls dont ask
why, it's a mess, but believe me it's the only way to seperate columns
(when number in the columns rise, they increase "to the right", columns
are seperated by spaces)
-File type is ANSI
-Row delimiter is {LF} -> other delimiter dont work out

Are there any workarounds possible with ActiveX? I tried to use it, but
no real success ...

Please help me guys, I spent two very long nights with this and I have
no idea how to solve this!

Thanks in advance!
Peter

Allan Mitchell

2006-01-21, 7:23 am

Hello Peter,

This article may help you

http://www.databasejournal.com/feat...cle.php/1462341

Allan

> Hi NG!
>
> I'm trying to import some data from a txt file, containing in the
> first column a string and the others having (floating point) numbers:
>
> column1 column2 column3 column4 column5 column1 67
> column3 column4 column5 column1 123 column3 column4
> column5 column1 5432 column3 column4 column5
>
> .
> .
> .
> column1 column2 column3
> column1 column2 column3
> The first couple of lines have 5 columns (in real at least 15, I
> reduced it here to make it easier), later in the file, the columns
> shrink to 3. My problem is that my DTS package screws up the data from
> the lines with 3 columns, in my table it looks like this, the lines
> with
> 5 columns are fine, but later the lines with 3 columns are totaly
> messed up:
> column1 column2 column3 column4 column5
> column1 column2 column3 column4 column5
> column1 column2 column3 column4 column5
> .
> .
> .
> column1 column2 column3 column1 column2
> column3 column1 column2 column3 column1
> DTS adds up the empty columns with columns from the next line ....
>
> My importing settings are as following (I played around with all
> possible combinations of these settings, no one really led to
> success):
> -Columns are defined as "Fixed field" --> has to be fixed, pls dont
> ask
> why, it's a mess, but believe me it's the only way to seperate columns
> (when number in the columns rise, they increase "to the right",
> columns
> are seperated by spaces)
> -File type is ANSI
> -Row delimiter is {LF} -> other delimiter dont work out
> Are there any workarounds possible with ActiveX? I tried to use it,
> but no real success ...
>
> Please help me guys, I spent two very long nights with this and I have
> no idea how to solve this!
>
> Thanks in advance!
> Peter

Sponsored Links