Using Bulk Insert from Text File without a delimiter....

Posted on 2007-08-10
Medium Priority
Last Modified: 2013-11-05
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..... :)
Question by:JD_Steele2
LVL 15

Expert Comment

ID: 19674003
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.
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 19674141
You could create a format file to define the fixed length fields to SQL Server and then use BULK INSERT to load the data directly into the final destination table.

Since format files are such a pain, however, it's probably easier to BULK INSERT the entire line into a work table, then INSERT to your final table by using SUBSTRING() to pull out the relevant data.  For example:

BULK INSERT table_name   --table_name has only one column, VARCHAR(200), or whatever length
FROM 'c:\full\path\to\data\file\datafile.dat'
    WITH ( FIELDTERMINATOR = '~'   --<<-- some character that does not exist in the input file
                ROWTERMINATOR = '\n')   --<<-- you might need to use \n\r

INSERT INTO main_table (col1, col2, col3, col4)
SELECT SUBSTRING(onlyColumn, 1, 20), SUBSTRING(onlyColumn, 21, 21), SUBSTRING(onlyColumn, 42, ...), SUBSTRING(onlyColumn, ..., ...)
FROM table_name

Author Comment

ID: 19674667
Wow. Talk about a difference in speed! What used to take almost an hour literally took about 10 seconds! BULK INSERT is incredible! :-)

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question