473,385 Members | 1,912 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

BULK INSERT not loading all rows from a text file.

1
I have written a stored procedure that is used to load data into a SQL Server 2000 and 2005 databases from some 40ish different text files. One of the text files is loading all of the rows apart from the last one. No error message is issued when this happens.
The command I am using to load the data is as follows:

Expand|Select|Wrap|Line Numbers
  1. BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt', MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc\Basicdata.bad') 
(I understand that the ERRORFILE option only works in SQL Server 2005, but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of data in the last row.

Expand|Select|Wrap|Line Numbers
  1. 49708    60646    2006-11-15    1    CITY SALES TAX    1    00    00    02    02    
  2. 49709    60647    1990-01-01    1    GROSS RECEIPTS TAX    2    03    01    02    02<tab>
Unless the last tab is manually inserted, the last row does not get loaded to the database, and no error message is issued. If the tab is inserted all of the data is loaded correctly. However, the text files come from a third party so I cannot ensure that this problem will not occur again.

The .fmt file used to load the data is as follows:

Expand|Select|Wrap|Line Numbers
  1. 8.0
  2. 11
  3. 1       SQLCHAR       0       12      "\t"                      1     BasicDataID            ""
  4. 2       SQLCHAR       0       12      "\t"                      2     BasicTaxOverrideID     ""
  5. 3       SQLCHAR       0       24      "\t"                      3     LegalEffectiveDate     ""
  6. 4        SQLCHAR       0       12      "\t"                      4     ReleaseNo              ""
  7. 5       SQLCHAR       0       50      "\t"                      5     BasicDataDesc          SQL_Latin1_General_CP1_CI_AS
  8. 6       SQLCHAR       0       1       "\t"                      6     PassFlag               SQL_Latin1_General_CP1_CI_AS
  9. 7       SQLCHAR       0       2       "\t"                      7     PassType               SQL_Latin1_General_CP1_CI_AS
  10. 8       SQLCHAR       0       2       "\t"                      8     BaseType               SQL_Latin1_General_CP1_CI_AS
  11. 9       SQLCHAR       0       2       "\t"                      9     DateFlag               SQL_Latin1_General_CP1_CI_AS
  12. 10      SQLCHAR       0       2       "\t"                      10    RoundingLevel          SQL_Latin1_General_CP1_CI_AS
  13. 11      SQLCHAR       0       8       "\r\n"                    11    TaxGroupingCode        SQL_Latin1_General_CP1_CI_AS
  14.  
My question is, is there any way to trigger an error message to denote that (in my example) the last row did not get loaded successfully? Is there a parameter associated with the BULK INSERT command that I am not currently using that could help with this problem?
Jun 22 '07 #1
0 2572

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: cmars | last post by:
Hi, I want to insert massive amounts of data in near real-time into a MySQL database. The data is in a structured binary format, and I have code that parses the data into logical data structures. ...
2
by: David Sharp | last post by:
I've been doing some experiments with speeding up copying tables of approximately 1 million rows between databases using BCP and BULK INSERT. I noticed that the total time for removing the...
5
by: me | last post by:
I'm also having problems getting the bulk insert to work. I don't know anything about it except what I've gleened from BOL but I'm not seeming to get anywhere...Hopefully there is some little (or...
7
by: iqbal | last post by:
Hi all, We have an application through which we are bulk inserting rows into a view. The definition of the view is such that it selects columns from a table on a remote server. I have added the...
6
by: pk | last post by:
Sorry for the piece-by-piece nature of this post, I moved it from a dormant group to this one and it was 3 separate posts in the other group. Anyway... I'm trying to bulk insert a text file of...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
5
by: yeoj13 | last post by:
Hello, I have a db2load script I'm using to populate a large table. Ideally, my target table is required to have "Not Null" constraints on a number of different columns. I've noticed a ...
0
by: rshivaraman | last post by:
BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' ) --------------------------------- This is the query used to populate bill_tbl....
2
by: zswanson | last post by:
Hi everyone, I receive a variety of text based files that seperates each row by a single delimiter, usually '~'. I use BULK INSERT to transfer the file from a text file to a temporary table. It...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.