0

I am trying to import a .csv file into SQL SERVER using BULK Import. I am facing weird problem when I am importing it initially it is not considering any line feed or any ROW Terminator which causes all the data to be imported into the last column. But When I am opening the .csv file in Excel and closing it using save as .csv all goes good and I am able to import data using BULK import.

My Code is as follow

BULK INSERT TS4_UCM_0 FROM 'C:\Test\stage1_Baseline1.csv'  
WITH (      
FIELDTERMINATOR =',',
FIRSTROW = 2,  
ROWTERMINATOR ='\n')
user2239073
  • 43
  • 9
  • try `ROWTERMINATOR = '\r\n'` and/or `ROWTERMINATOR = '\r'` – coge.soft May 20 '13 at 17:54
  • I have tried '\r\n' and also '\r'. If I am just opening and saving the file using excel, I am able to achieve it. – user2239073 May 20 '13 at 17:58
  • We have this problem regularly and solved it using the method you described: Before loading the csv file, open and save using Excel. – PowerUser May 20 '13 at 19:43
  • 1
    Maybe see http://stackoverflow.com/questions/479819/bulk-insert-sql-server-2000-unix-linebreaks for CHR(10) trick. – Andrew May 20 '13 at 21:06
  • This is easy to visualize if you take one or two lines from the CSV file with Python and convert the last few characters of the line into hex. Then you can actually see what Excel is doing to your file before/after. – tommy_o May 20 '13 at 23:29
  • Hey thanks Power user.. using Rowterminator = ' 0x0a' works great for me. Thanks all for your comments highly apprciate. – user2239073 May 21 '13 at 02:42

0 Answers0