Bulk Insert End of File 2

[njvsummit]

I'm doing what I think is a simple Bulk Insert into a table from a tab delimited file.

BULK INSERT dbo.table
FROM '\\server\file.asc'
WITH (FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n')

The last line of the file contains no data other than what looks like a ^z in some text editors or I think a "1A" in a hex editor--and it causes the insert to fail. The file comes from an outside source daily and the number of rows changes each time. If I open it in an editor and delete the character, it works fine, but I would like to automate the import and manipulation of the file. Also, though I like DTS, I'm trying to not use it for this particular project. Any thoughts on how to ignore the last line?

Thanks

Nick

 

[yksvaan]

Could you copy it in windows batch before bulk? Try
copy /Y sourcefile /a resultfile /b

( maybe with master..xp_cmdshell )
and then bulk resultfile. That will get rid of control-Z

end-of-lines might go bonkers, so double-check

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[njvsummit]

yksvaan

Thanks, gotta love simple solutions!

Nick