Hi all I have a txt file that i need to insert into a table with just one column varchar(max). So the bulk insert cmd should be straight forward. Problem is, when I run the cmd, the entire file is loaded into a single record! I've used various rowterminators including \r\n \n '''varchar(10)''' etc... The cmd doesn't seem to read the rowterminator so I'm wondering whether anyone out there has come across this anomoly before.
The table def to import into is
The syntax I've used includes:
plus a few others. Each combination returns the entire file (3000+ lines) as a single record....
The first couple of lines from the file Y:\derm_import.txt follow (next line
much appreciated Grant
Thanks for the replies guys Amazingly, I found the answer accidentally.
Try this: bulk insert protest.dbo.usr_DERM_IMPORT FROM 'Y:\derm_import.txt' with (ROWTERMINATOR = ' ')
Meaning, I actually enter after the first single quote. And that worked.
Go figure all....???
answered Mar 11 '10 at 10:02 PM
Have you tried using LogParser instead? Its a command line tool that might handle the file more easily for you. You dont mention how often this process takes place so it may be you still have to research the Bulk Insert though. LogParser can be found here and you might well end up with a command close to
answered Mar 09 '10 at 05:41 AM
The answer depends on which computer generated the file. If you don't select the line terminator correctly you'll get a mess. The standard line terminator for the PC is CRLF, while the standard terminator for any UNIX is LF and the standard terminator for the MAC is CR.
answered Mar 08 '10 at 10:46 PM