Bulk Insert from file to table

• mupparaju78

  SSChasing Mays

  Points: 646

  More actions

  January 28, 2013 at 2:48 am

  #394035

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

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  January 28, 2013 at 3:50 am

  #1581515

  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.

• mupparaju78

  SSChasing Mays

  Points: 646

  More actions

  January 28, 2013 at 4:31 am

  #1581543

  i tried with .. but no luck

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  January 28, 2013 at 4:56 am

  #1581548

  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)

• mupparaju78

  SSChasing Mays

  Points: 646

  More actions

  January 28, 2013 at 11:03 pm

  #1581892

  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

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  January 29, 2013 at 1:55 am

  #1581938

  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);