December 13, 2005 at 8:59 am
I have a fixed width text file that will only import 92 rows of the 185. I am not sure why. At first I thought it was a validation error on the table receiving but when I create a temp table col1,col2,col3.... it still only imported 92 rows.
When I import it into access I get all 185 rows without a problem. It should work in SQL but I do not know what the problem is.
December 13, 2005 at 3:23 pm
this sounds stupid....you may want to check line 93 in your text file to see any different from other lines.
are you able to post your text file and dll?
December 13, 2005 at 3:37 pm
What is your line terminator set to ? Have you opened the text file in a hex/binary editor and verified that you are getting the expected line terminator ?
eg: we've had issues with inconsistent CR/LF versus just CR line terminators. If Access is looking for just CR, but you've got DTS looking for CR/LF and not always getting it, that would account for the difference.
December 14, 2005 at 3:32 am
I've had the exactly same issue and I analized the txt file and haven't found any difference in the line where the DTS stopped. As well there was no error message from DTS - but I just had half of the file in my table. Removing the lines before and after the DTS stopped wasn't make any difference. The DTS stopped again in the middle of the file.
Import to Access and then import to SQL was solving the issue.
December 14, 2005 at 7:11 am
Are all the lines in the file the same number of characters ?
December 14, 2005 at 7:55 am
I don't know if this is exactly the same but when importing from excel, DTS reads the first 8 lines (by default) and makes assumptions on those lines as to the contents.
If a line then comes up that doesn't match it just stops. Changing to a straight csv normally fixes this. There is a registry setting somewhere which you can swich off the 8 line rule.
As I say this sounds like a similar problem and although not exactly what you said maybe you might find a hint in this.
December 14, 2005 at 9:02 am
the file is fixed width so a designator is not needed. I tell the process where the terminator is.
i did look at lines 90,91,92,93,94 and they are no different then any other.
I should have added that it doesn't pull only the first 92 lines. It pulls 92 lines out of the whole file. For instance line 185 became line 92. It skips around and I don't know why. Access did solve the issue but it is a monthly process making this painful. I will contact the vendor to see if I can get to the bottom of his output. Thanks for the help
December 14, 2005 at 9:32 am
I have also seen inexplicable errors importing text files in DTS, and no amount of analyzing the file could find any strange characters or invalid EOL sequences. Of course, the explicable errors of mismatched qualifiers or missing delimiters are much more common, but sometimes the cause can't be determined by mere mortals.
I've seen the trick of importing through Access before, but it bothers me to have to do it that way. I had a job where I had to process a big text file daily, and every week or two I would get a file that refused to work. The solution I used was to have a DTS package import the text file as a single varchar(8000) field with no field delimiters (assuming it's under 8000 chars) and export it to a temp text file, then import that file with the desired column definitions. I never did a binary diff on the files to see what was different about the DTS-exported file, but it never failed to import correctly.
December 14, 2005 at 12:11 pm
Yea That sounds like fun. I asked him to change it to a pipe delimiter. Hopefully he is willing to play along with my testing
December 14, 2005 at 12:18 pm
How are the lines skipped? I note that 185/2 = 92 plus 1 row. Are you importing every other row? Maybe each pair of lines is being treated as a single line. How many characters per row?
December 14, 2005 at 4:48 pm
That is weird. I was looking at the data and it looked to be aggregating like you are saying that is what I thought. Now skipping lines you may be onto something. I am working with 358 characters.
There are actually 186 lines, last line being empty. Like a carrage return. So why would it skip?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply