Bulk Insert from file to table

  • Hi, I have .dat file with following format data.

    1,2,3,

    4,5,6,

    7,8,9,

    i want load this format file into sql table using bulk insert statement.

    how can we fit ROWTERMINATOR in bulk statement.

    please let me know....

  • Use the ROWTERMINATOR option of the BULK INSERT command.

    http://msdn.microsoft.com/en-us/library/ms188365.aspx

    , \ n

    or something along them lines, just remove the spaces.

  • i tried with .. but no luck

  • bulk insert ... from '...' with (fieldterminator = ',', rowterminator = ', \ n') --Remove the spaces between , \ n

    Need to ensure that there is a carrage return at the end of the last line in the file

    1,2,3,(CR)

    4,5,6,(CR)

    7,8,9,(CR)

  • Hi anthony.green,

    Thank you for your reply.

    when i opend the file and delere " ," and type again... at that move ment your functionality is working.

    with out open file.. all rowsdata insrted into single row.....

    is there any functionality to edit the last charecter in file

  • Try ROWTERMINATOR of '','+CHAR(10)+''', will need to build it dynamic, might be getting confused as relates to line feed carrage return

    DECLARE @bulk_cmd varchar(1000);

    SET @bulk_cmd = 'BULK INSERT Table

    FROM ''C:\file.txt''

    WITH (ROWTERMINATOR = '','+CHAR(10)+''', FIELDTERMINATOR = '','')';

    EXEC (@bulk_cmd);

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply