I'm trying to bulk insert data from a tab deliminated txt file onto a
table, however some of the rows within my txt file are are empty (e.g.,
last 5 of 8 fields are empty).  My bulk insert is appending the next
row onto the existing row when all 8 fields are not populated.  It
doesn't add a new record in my DB when the next row is encountered.
I've created a formatFile however not been able to upload correctly I
would greatly appreaciate any ideas.

thx

------Sample bulk insert wiith same bad results --------

use Northwind

CREATE TABLE [SampleReport2]
    (key1 varchar(100),
    key2 varchar(100),
    key3 varchar(100),
    key4 varchar(100),
    key5 varchar(100),
    key6 varchar(100),
    key7 varchar(100),
    key8 varchar(100),
    )


--- bulk statement ----
BULK INSERT NORTHWIND..[SampleReport2]
    FROM 'C:\AC_EXPORT.TXT'
    WITH
        (FIRSTROW=2
        ,ROWTERMINATOR= '\r\n'
        ,FIELDTERMINATOR= '\t '
        ,FORMATFILE= 'C:\AC_EXPORT.fmt'
        )

--- format file (C:\AC_EXPORT.fmt)-----
8.0
8
1    SQLCHAR    0    0    "\t"    1    key1    SQL_Latin1_General_CP1_CI_AS
2    SQLCHAR    0    0    "\t"    2    key2    SQL_Latin1_General_CP1_CI_AS
3    SQLCHAR    0    0    "\t"    3    key3    SQL_Latin1_General_CP1_CI_AS
4    SQLCHAR    0    0    "\t"    4    key4    SQL_Latin1_General_CP1_CI_AS
5    SQLCHAR    0    0    "\t"    5    key5    SQL_Latin1_General_CP1_CI_AS
6    SQLCHAR    0    0    "\t"    6    key6    SQL_Latin1_General_CP1_CI_AS
7    SQLCHAR    0    0    "\t"    7    key7    SQL_Latin1_General_CP1_CI_AS
8    SQLCHAR    0    0    "\r\n"    8    key8    SQL_Latin1_General_CP1_CI_AS

--- Sample Data File (C:\AC_EXPORT.TXT)
1SEA4CDCFVBCT
87    47    ABOLLL
-9    02    ABOLLL 9871
23    01    ABOLLL 9871
-78    15    ABOLLL 1111
-32    15    ABOLLL 2254
88    9981    ABOLLL    4    1    44    21
9    9981    ABOLLL    5    41    21    13
10    9981    ABOLLL    8    12    52    31
22    9981    ABOLLL    4    4    54    21
55    9981    ABOLLL    6    1
-2    9981    ABOLLL    66    12
3    9981    ABOLLL    44    12
4    9981    ABOLLL    21    12
99    9981    ABOLLL    11    14
26    9981    ABOLLL    112    14

(jose.mende***@gmail.com) writes:
The fields are so empty that there is not even a delimiter, you are out
of luck. Bulk insert treats the file as binary and keeps reading data
until it finds the termination of the current field, as defined by the
format file. So if there are no terminators, Bulk insert will gladly
continue on the next line. Which is great if you need to import data
with line breaks in it.

I don't know if DTS/SSIS can handle this better.

Else your options are two a) Write a preprocessing program in C, Perl,
VBscript whatever that adds the missing tabs. b) import into a staging
table with as many columns there are at a minimum, and then split that
column into fields as needed.

At least with SQL 2000 I would prefer the first option.

You can bulk write to a view on the table and then perform processing
on the data in an InsteadOfUpdate and/or InsteadOfInsert trigger.
As a general rule it would be better to put the data in a clean and
coherent format and structure before attempting a bulk write.


Erland Sommarskog wrote:
Show quoteHide quote

extracting data
Trying to match lowercase
too long...
datetime between problem
SQL Join question - getting most recent value in a history table