Using Bulk Insert from Text File without a delimiter....
Posted on 2007-08-10
I was wondering if there was a way to utitlize the 'Bulk Insert' from a text file when there is no apparant delimiter. The text file's values are based on position. For example 'ClaimNumber' would be position 1 - 20 on the line. FirstName would be position 21-41, and so on... there are a total of 10 fields per line.
These text files contain on average 180,000+ records each day. Right now I'm parsing the data from the files using an application I wrote that loops through each line of the file and sets the values to some variables and then calls a stored procedure to insert the record into the database. I'm convinced this is a very inefficient way to accomplish this.
All other examples I've seen regarding Bulk Insert have some kind of delimiter to identify where the field ends and the next begins. If this can't be done using Bulk Insert, I'm open to any suggestions. Possibly looping through each line in the text file and plugging in my own delimiters and resaving it, and then do a Bulk Insert?