0

I am creating a process that will load a fixed width file into SQL Server. I have used the BULK INSERT and a format file and that works in most cases.

For 2 of my files, most of the rows are consistent, but in a few files, randomly there is a blank row that does not have the full length so it fails.

I am fine with skipping the row, but it does not seem to have a command to skip blank rows. I am fine with ignoring the failure, the default is of course 10 rows.

I really do not have any reasonable way of knowing how many, but I tried setting the MAXERRORS to a really large number (100,000), but it seems to allow 27 errors to ignore but no more.

Is there something I am missing about the MAXERRORS?

Any other ideas?

Improve this question
9
  • 2
    The problem, in truth, is your source data. I would focus on getting that fixed. Commented Feb 12 at 22:31
  • *.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. Or data elements are too long or missing completely, i.e. column positions will vary. The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, encoding, and enforce data quality. Commented Feb 12 at 22:50
  • 3
    SQL Server Bulk Insert is good for importing data that exactly matches the expected format, but is inflexible at handling variable formats. I would suggest first preprocessing your source file to clean it up using an external tool that is better at text processing. A simple powershell script may do the trick. See remove empty lines from text file with PowerShell. If you have shortened or incomplete lines instead of empty lines, you can modify the filter criteria. Commented Feb 12 at 23:01
  • @YitzhakKhabinsky that only works if the generators actually pay attention to their own XSD. I have several scientific labs that generate XML files that don't agree with their supplied XSDs, probably because the OEM-supplied software that they use doesn't use decent XML libraries to serialize their exports. One of them even generates XML files full of NUL bytes if they try to export too many samples in a single file. Commented Feb 13 at 4:03
  • @AlwaysLearning, that's exactly the entire point. Do not accept XML file that is failing a validation against an XSD, i.e. data contract between sender and receiver. A sender shall validate XML against the same XSD before it is shipped to a receiver. Commented Feb 13 at 4:18
Related questions
4343
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
2450
Concatenate text from multiple rows into a single text string
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.