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

Posted on 2007-08-10
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

    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 68

    Accepted Solution

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    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.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now