BCP CSV FILE TO SQL SERVER TABLE

NOTEPAD FORMAT : SAVE AS .fmt

11.0
3
1   SQLCHAR     0     10    "," 1   ID       SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR     0     20    "," 2  NAME SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR     0     30    "\r\n" 3  ADDR SQL_Latin1_General_CP1_CI_AS


PROC :

CREATE TABLE TEMPSVK(ID INT,NAME VARCHAR(20),ADDR VARCHAR(20))

exec master..xp_cmdshell 'bcp "test.dbo.TEMPSVK" in "D:\excel\Check.csv" -T -F1 -f "D:\excel\test.fmt" '

select @@SERVERNAME

select * from TEMPSVK

truncate table TEMPSVK


exec master..xp_cmdshell 'bcp "test.dbo.TEMPSVK" in "D:\excel\Check_1.xls" -T -F1 -f "D:\excel\test.fmt" '


SAMPLE EXCEL IN CSV FILE FORMAT :

ID NAME ADDR
100 AAA CHENNAI
101 BBB CHENNAI
102 CCC CHENNAI