bulk insert question? Another way?

10 Dec 2005 1:41 PM

kevin

hi guys,
I have a text file contains about 1 million rows, I tried to import to a
table but I received data conversion errors for some rows, however, the row
number is like 504500 for example, I want to troubleshoot this row but the
only way I know is to open the text file (300MB) which will take forever to
open on notepad, I'm not sure if there is an easy way to troubleshoot such
a problem. Another thing is, I set Batchsize=10000, so I guess if I
received errors for 20 rows, it would mean that 200000 rows (20 *10000)
didn't get inserted into table, correct?

Thanks

10 Dec 2005 4:10 PM

Steve Kass

Kevin,

Get a copy of TextPad or one of the many shareware or freeware text editors
that can handle large files with ease.

Another alternative is to import the file into a one-column staging table on
your SQL Server, and then scrub the data before inserting it into the target
table.

Steve Kass
Drew University

kevin wrote:

Show quote

>hi guys,
> I have a text file contains about 1 million rows, I tried to import to a
>table but I received data conversion errors for some rows, however, the row
>number is like 504500 for example, I want to troubleshoot this row but the
>only way I know is to open the text file (300MB) which will take forever to
>open on notepad, I'm not sure if there is an easy way to troubleshoot such
>a problem. Another thing is, I set Batchsize=10000, so I guess if I
>received errors for 20 rows, it would mean that 200000 rows (20 *10000)
>didn't get inserted into table, correct?
>
>Thanks
>
>
>
>
>

10 Dec 2005 11:27 PM

Erland Sommarskog

kevin (pearl***@hotmail.com) writes:

> I have a text file contains about 1 million rows, I tried to import to
> a table but I received data conversion errors for some rows, however,
> the row number is like 504500 for example, I want to troubleshoot this
> row but the only way I know is to open the text file (300MB) which will
> take forever to open on notepad, I'm not sure if there is an easy way
> to troubleshoot such a problem.

If you are running BCP, use the -e option to get errors to a file. BULK
INSERT has a similar option.

But as Steve said, a staging table where all columns are varchar is
usually a good idea.

> Another thing is, I set Batchsize=10000, so I guess if I received errors
> for 20 rows, it would mean that 200000 rows (20 *10000) didn't get
> inserted into table, correct?

Not necessarily. The same batch may have more than one error.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx