Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    31

    Unanswered: Flat File Records Dropped During Import

    Hello,
    I am attempting to import a fixed width flat file into a SQL Server table. When I import the file, 704 records don't make it into the table. I know this because if I do the import with MS Access 2003 into an Access table, all of the records from the flat file make it into the table. The flat files have a .txt extension.

    The only possible problem that I can see is that some of the rows in the flat file do not contain the full set of characters. When I do the import into SQL Server and create a table on the fly, I still end up 704 records short. There are no error messages during or after the import.

    I suppose I could isolate some of the missing records, put them into a different file and try to import them to see what would happen. Other than that, how do I begin to troubleshoot this problem? Are there known issues where records can be dropped from a fixed width file?

    Thank you for your help!

    cdun2

  2. #2
    Join Date
    Apr 2007
    Posts
    31
    I may have found the problem. The first record does not contain a full set of characters, and when I set up the fixed field column positions originally, I was not able to define the columns for the full string. Only the first one third of the row characters need to be imported, so I ignored this issue.

    I have moved a single full length record to the top of the flat file, and the text file properties box now can 'see' the full string. I'll include the rest of the columns (which are not imported) and see if that works.

  3. #3
    Join Date
    Apr 2007
    Posts
    31
    No, that didn't work. I moved a record containing all characters to the top of the file, redefined the columns based on the full string, changed the column mappings, and reconfigured the transformations. When I did the import, the destination SQL Server table received even fewer records.

    What can I do about this problem?

    Thanks again.

    cdun2

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    If this is a one time only situation, why don't you just import the flat file into Access 2003 and then import the Access table to SQL server?

  5. #5
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    What are you using to import the data? DTS? BCP?
    Don't Bogart That Squishee!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •