+ Reply to Thread
Results 1 to 4 of 4

Bulk Insert with ROWTERMINATOR failing

  1. Bulk Insert with ROWTERMINATOR failing

    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


  2. Re: 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
    >



  3. Re: Bulk Insert with ROWTERMINATOR failing

    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?

  4. Re: Bulk Insert with ROWTERMINATOR failing

    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.


+ Reply to Thread