I found a bug in MSSQL DTS import wizard

We have a textfile that we import in Excel(provided by our mainframe folks).
It's fixed width with 142 columns. When I import it in excel or ms access,
it gives me about ~34000 rows. But when I import using DTS package that is
exported to Access mdb, rows becomes ~17000 rows :(
And another hurdle that I'm faced is that there are rows that contains
combined data such as street, city and state all together without spaces.
Unfortunately, we can't modify the source text file. However, I do have a
fieldmarker(save a .txt file) which I always use in Excel so that when I
import it, I can select the area to create a vertical line just right that
marker.
Here is a sample of my fieldmarker.
12345p1234567p12pp123456p
Everytime i see a p, I click it with a mouse to create a vertical line in
fixed width importing. In that case, there are 5 columns. These are all
similar to ms access or excel.
The bug.
Please see this link below about the bug I found in MSSQL DTS Import wizard.
It doesn't happen in Excel or MS Access. Excel and MS Access can handle it
properly.
http://restricted.dyndns.org/dtsimportissue.html
Please help. Solution needed very badly!
Thanks,
Neil"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:Oj$m4gz5EHA.1292@.TK2MSFTNGP10.phx.gbl...
> We have a textfile that we import in Excel(provided by our mainframe
> folks). It's fixed width with 142 columns. When I import it in excel or ms
> access, it gives me about ~34000 rows. But when I import using DTS package
> that is exported to Access mdb, rows becomes ~17000 rows :(
> And another hurdle that I'm faced is that there are rows that contains
> combined data such as street, city and state all together without spaces.
> Unfortunately, we can't modify the source text file. However, I do have a
> fieldmarker(save a .txt file) which I always use in Excel so that when I
> import it, I can select the area to create a vertical line just right that
> marker.
> Here is a sample of my fieldmarker.
> 12345p1234567p12pp123456p
> Everytime i see a p, I click it with a mouse to create a vertical line in
> fixed width importing. In that case, there are 5 columns. These are all
> similar to ms access or excel.
> The bug.
> Please see this link below about the bug I found in MSSQL DTS Import
> wizard. It doesn't happen in Excel or MS Access. Excel and MS Access can
> handle it properly.
> http://restricted.dyndns.org/dtsimportissue.html
> Please help. Solution needed very badly!
It looks as though DTS is using the CRLF in the first line to define the
line lengths, and concatenating data in the rows because they are too short
and therefore incomplete records. What happens if you trim the first line
back to the same length as the other lines? Or how about padding the lines
out so they are all the same length?
According to http://support.microsoft.com/default.aspx?scid=kb;en-us;271247
if any rows are longer than expected then additional characters appear
wrapped in the DTS wizard, but are actually discarded.
However, in article
http://support.microsoft.com/default.aspx?scid=kb;en-us;247360 it mentions
that DTS expects a fixed length file to have lines of fixed length, and
having shorter lines will cause additional characters to be read from the
next line.
So the answer appears to be that in a fixed length import, make sure every
line is the same length to avoid this problem.
Dan|||"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:uZDTPI05EHA.3076@.TK2MSFTNGP15.phx.gbl...
> It looks as though DTS is using the CRLF in the first line to define the
> line lengths, and concatenating data in the rows because they are too
> short and therefore incomplete records. What happens if you trim the first
> line back to the same length as the other lines? Or how about padding the
> lines out so they are all the same length?
> According to
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271247 if any rows
> are longer than expected then additional characters appear wrapped in the
> DTS wizard, but are actually discarded.
> However, in article
> http://support.microsoft.com/default.aspx?scid=kb;en-us;247360 it
> mentions that DTS expects a fixed length file to have lines of fixed
> length, and having shorter lines will cause additional characters to be
> read from the next line.
> So the answer appears to be that in a fixed length import, make sure every
> line is the same length to avoid this problem.
>
Ok. Then we're out of luck then. I will contact the vendor if there will be
data for those columns in the future. If not, I can just delete it for now
to avoid the problem. It's just that MS Excel and Access support it. I don't
know why they didn't do that for DTS :(
Thanks.
Neil|||Neil,
I don't quite understand your explanation or the online example. You
say this is a fixed-width file, but the example you give online shows a
first line that is much longer than the second line. Fixed-width means
every line of the text file has the same length. Your file has lines of
different lengths, but it also doesn't have a separator character
between each column, so the wizard won't help you, and you'll need to do
something else, I guess.
Maybe one of these will work:
Import using BULK INSERT with a format file.
Preprocess the file with Unix-like utilities to add delimiters, then
import as delimited
Import the file into a staging table with one long column, then split
it up with
insert into FinalDestinationTable
select substring(s,1,4), substring(s,5,7), ...
Steve Kass
Drew University
Neil wrote:
>We have a textfile that we import in Excel(provided by our mainframe folks).
>It's fixed width with 142 columns. When I import it in excel or ms access,
>it gives me about ~34000 rows. But when I import using DTS package that is
>exported to Access mdb, rows becomes ~17000 rows :(
>And another hurdle that I'm faced is that there are rows that contains
>combined data such as street, city and state all together without spaces.
>Unfortunately, we can't modify the source text file. However, I do have a
>fieldmarker(save a .txt file) which I always use in Excel so that when I
>import it, I can select the area to create a vertical line just right that
>marker.
>Here is a sample of my fieldmarker.
>12345p1234567p12pp123456p
>Everytime i see a p, I click it with a mouse to create a vertical line in
>fixed width importing. In that case, there are 5 columns. These are all
>similar to ms access or excel.
>The bug.
>Please see this link below about the bug I found in MSSQL DTS Import wizard.
>It doesn't happen in Excel or MS Access. Excel and MS Access can handle it
>properly.
>http://restricted.dyndns.org/dtsimportissue.html
>Please help. Solution needed very badly!
>Thanks,
>Neil
>
>