June 7, 2007 at 11:12 am
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!
CSDunn
June 7, 2007 at 11:53 am
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, 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.
June 7, 2007 at 12:41 pm
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
June 8, 2007 at 7:21 am
Are the missing records from the end of the file or are they scattered throughout the file? Are the 704 the ones that don't have all of the fields? I have seen issues with the short records before and I use this script to put an X at the end of each record to make them conform.
set fso=createobject("scripting.filesystemobject")
set fl= fso.OpenTextFile("import.TXT")
set nfl = fso.createtextfile("reimport.txt")
do while not fl.atendofstream
line=fl.readline
do while len(line)<566 'length of record
line = line & " "
loop
line=line & "X"
nfl.writeline line
loop
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply