rowterminator - bulk insert

Question

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.

Vital statistics:

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....

;?P

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

Answer 1

0

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

Grant Williams
26 ● 1 ● 1 ● 1

(comments are locked)

Answer 2

0

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
LOGPARSER "SELECT myCol INTO MyTable from Y:\derm_import.txt" -o:sql -database:mydatabase -server:MyServer -createtable:on

answered Mar 09 '10 at 05:41 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 3

0

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.
If the file came from a mainframe, it gets more cumbersome since the transfer to the PC determines the line terminator.

answered Mar 08 '10 at 10:46 PM

dvroman
500 ● 1 ● 2

I used the CR & LF on purpose since it refers to the character in the data and not the interpretation of those characters in the OS dependent applications. 'n' normally refers to LF but some implimentations on some applications use alternate definitions of the 'n'. (I've seen 'n' refer to CRLF.)

Mar 15 '10 at 08:58 PM dvroman

(comments are locked)

rowterminator - bulk insert

Follow this question

Related Questions