JD_Steele2
asked on
Using Bulk Insert from Text File without a delimiter....
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?
Help..... :)
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?
Help..... :)
m1tk4
I'd try linking to this text file from an Access DB, using "Fixed width" option, then using a DTS package to read from that Access file into the MS SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow. Talk about a difference in speed! What used to take almost an hour literally took about 10 seconds! BULK INSERT is incredible! :-)