2012年3月6日星期二

bcp error (csv to sqlserver)

Hi everybody
I am using bcp to insert data from csv file to sql server . The problem that i am facing is that, The heading in the csv file is populating in the tables
also the datas are not inserting.
I dosent want the first row in the csv which holds the heading to insert in the table
Does anybody know to solve this problem
I am using this code

CREATE procedure usp_ImportMultipleFilesBCP @.servername varchar(128),
@.DatabaseName varchar(128), @.filepath varchar(500), @.pattern varchar(100),
@.TableName varchar(128)
as
declare @.query varchar(1000)
declare @.max1 int
declare @.count1 int
Declare @.filename varchar(100)
set @.count1 =0
create table #x (name varchar(200))
set @.query ='master.dbo.xp_cmdshell "dir '+@.filepath+@.pattern +' /b"'
insert #x exec (@.query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @.max1 = (select max(ID) from #y)
--print @.max1
--print @.count1
--select * from #y
While @.count1 <= @.max1
begin
set @.count1=@.count1+1
set @.filename = (select name from #y where [id] = @.count1)
set @.Query ='bcp "'+ @.databasename+'.dbo.'+@.Tablename + '"
in "'+ @.Filepath+@.Filename+'" -S' + @.servername + ' -T -c -r\n -t,'
set @.Query = 'MASTER.DBO.xp_cmdshell '+ "'"+ @.query +"'"
--print @.query
EXEC ( @.query)
--insert into logtable (query) select @.query
end

drop table #y
GO

Exec usp_ImportMultipleFilesBCP 'servername','databasename','c:\Myimport\','aa.csv ','Tablename'

when I execute this I get an error like

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL

If anybody knows pls reply
Thank you verymuchYour error msg suggests that there is some problem with the syntax you have use to execute BCP, Kindly check the command line syntax of BCP with the one you have used in the stored procedure...|||I am able to insert the rows from csv to sql server but I want to remove the first row (containing the heading for the columns) while inserting into sql server.
Any idea

没有评论:

发表评论