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
CREATE TABLE [dbo].[usr_DERM_IMPORT]( [blob] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
The syntax I've used includes:
(i) bulk insert protest.dbo.usr_DERM_IMPORT FROM 'Y:\derm_import.txt' with (ROWTERMINATOR = '\n') (ii) bulk insert protest.dbo.usr_DERM_IMPORT FROM 'Y:\derm_import.txt' with (ROWTERMINATOR = '\n', DATAFILETYPE='char' ) (iii) bulk insert protest.dbo.usr_DERM_IMPORT FROM 'Y:\derm_import.txt' with (ROWTERMINATOR = '''char(10)''') (iv) bulk insert protest.dbo.usr_DERM_IMPORT FROM 'Y:\derm_import.txt'
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
51 15612217/02/2010 20:39:036510 SCENIC RIM REGIONAL 21 0IISSUING AACTIVE YYFHFREEHOLD N 0 80990 0 5DWELLING - LARGE HOUSESITE 98SECTION 17 APPLIES (SECONDARY USE ONLY) 50 15612217/02/2010 20:39:03TFRTRANSFER AAMENDED 17/02/2010 20:39:03 52 15612217/02/2010 20:39:03 479 0 BROOKLAND RD ROAD ALLENVIEW QLD4285 0 0 NA 53 15612217/02/2010 20:39:03 37UNDALLAH NN17930ALLENVIEW 0NONE 151RURAL HOME SITE 55 15612217/02/2010 20:39:0321SEC 37 - ANNUAL REVAL ISSUE 01/10/200717/03/200830/06/2008UV UNIMPROVED VALUE 285000 0
much appreciated Grant