Monday, March 26, 2012

Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] for
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?
Do you have an extra line in your text file?

>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>
|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:

>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>
|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx.gbl...
> Do you have an extra line in your text file?
> using DTS.
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> last line in the TAB
> into [SIC_ID] for
> is it trying to
> insert?
|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
,
TAB
for
>
|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID] for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>
|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
NULL
> ,
> TAB
for
>

No comments:

Post a Comment