Bulk Insert with ROWTERMINATOR failing

Try \r, \r\n, or \n\r as ROWTERMINATOR. I've also never specified the
DATAFILETYPE, maybe try each of those options while leaving that keyword
out.


On 4/1/09 1:26 PM, in article
63afd7ea-e3c5-473e-b268-3100366c8d62...oglegroups.com,
"knowthediffatgmaildotcom" wrote:

> Hello,
> I have a tab delimited file, generate from some legacy system that I
> want to insert into a SQL table. I cannot change the way the file is
> generated. The file containts 500k rows of data. The rows are
> terminated with CHAR(10), I know this because I wrote a little vb app
> that opens the file and loops through the data character by character
> and spits out the code for the character.
>
> I process the following statement and I receive "(1 row(s)
> affected)". It puts the first row of data into the table and then
> squashes as much of the rest of the file into the last table column.
> This means that it is not recognizing the ROWTERMINATOR, correct?
>
> SET ANSI_WARNINGS OFF
> BULK INSERT [GRC].[dbo].[UP_040109]
> FROM 'C:\TEMP\up\524.d.txt'
> WITH
> (
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n'
> )
>
> I have also tried the following ROWTERMINATOR values:
> '\n'
> '\r'
> '\r\n'
> ''' + nchar(10) + '''
> ''' + nchar(13) + nchar(10) + '''
>
> This odd thing is that if I open the file in wordpad, make a minor
> data change and resave it then try to run the bulk insert it works
> fine :/ I assume that Wordpad is resetting the line feed to something
> that can be processed like \r\n.
>
>
> Any one out there than can help explain why this isn't working? I've
> been stuck on it for a few days and google hasn't been able to solve
> it yet :)
>
> Thx
>

Found a work around but I still would like to do this without EXEC:

The following works:
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT [GRC].[dbo].[UP_040109]
FROM ''C:\TEMP\up\524.d.0''
WITH (ROWTERMINATOR = '''+CHAR(10)+''',FIELDTERMINATOR = ''\t'')'
EXEC(@bulk_cmd)

This does not work:
BULK INSERT [GRC].[dbo].[UP_040109]
FROM 'C:\TEMP\up\524.d.0'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''' + CHAR(10) + '''
)


Any Ideas, I dont see the difference?

On Apr 1, 2:12*pm, knowthed...atgmaildotcom wrote:

> Any Ideas, I dont see the difference?

The difference is that with EXEC, the CHAR(10) is actually part of the
string being executed. Without it, it is an attempt to use an
expression as a ROWTERMINATOR, which is not allowed.