Flat File Import Truncates Rows

[DanEvans]

I have a tricky problem. I have a file of customer notes that I import as one long text string. For some reason, although I specify the column width, the import randomly truncates the text of a few of the fields.

For example, a record that reads:
ADDED 1234567890123456 NEW: SOMEDATET 20060727 DAN

Gets brought in as
ADDED 1234567890123456 NEW: SOMEDATET

It does not appear that there is a special character after the point it gets truncated.

But it gets better. In an effort to isolate the problem, I made a copy of the text file, deleted everything 4 or five rows above the problem record, and everthing a few records after it, ran the code, and it worked fine!

Does anyone know what is going on here? Why would things get better after I save a copy?

Thanks,
Dan

 

[DanEvans]

OK, I answered my own question. It appears that the file does in fact have special characters. I used bulk insert to bring in the file "as is", found a row that was being truncated on select, selected the ASCII value of the character occurring after the truncation began, and discovered the ASCII character 0.

I fixed this by replacing the ASCII 0 (ASCII's equivalent to NULL) with a space: replace(import_text,char(0),char(32))

Thanks,
Dan