x

rowterminator - bulk insert

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

more ▼

asked Mar 08, 2010 at 09:22 PM in Default

Grant Williams gravatar image

Grant Williams
26 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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

more ▼

answered Mar 11, 2010 at 10:02 PM

Grant Williams gravatar image

Grant Williams
26 1 1 1

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 09, 2010 at 05:41 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 08, 2010 at 10:46 PM

dvroman gravatar image

dvroman
1.1k 2 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, 2010 at 08:58 PM dvroman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x104
x10

asked: Mar 08, 2010 at 09:22 PM

Seen: 3674 times

Last Updated: Mar 09, 2010 at 07:06 AM