Importing a csv file

Hi ,
I have to import a csv file in to a Sql server 2005 64 bit server table and the data in excel is like this

id         name     state
1         "mike,sta"   Cal
2         "sbc , fts"  ny
3         alan        ca

Column name has a ',' in it so to distinguish we have double quotes where ever there is ',' in name . How can we import using BCP command(I want only using any Sql )

I want name to be imported in one column of the table . Can this be done with out using format file
vijay11Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
the link below contains a solution

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=41295
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vijay11Author Commented:
here in this case I am exporting a string in a table (All the data in 1 column ) and Imporing in to a table with 3 columns . While exporting only can I use other logic (Now I am using comma as delimiter)  

1, "mike,sta" ,Cal
2,"sbc , fts",ny
3,alan,ca
0
Aaron ShiloChief Database ArchitectCommented:
to make things simple use the import wizrd from ssms (under tasks wehn you right click).

import the csv with comma as your delimiter
0
Robert SchuttSoftware EngineerCommented:
Here's a good example: http://blog.netnerds.net/2007/01/bcp-remove-quotes-from-csv-import-using-bcp-format-file/ but you would need to change your export to include quotes on every (character) field, not just when it contains a comma.
0
Jerryuk007Commented:
You said: "I am exporting a string in a table (All the data in 1 column ) and Imporing in to a table with 3 columns".
If you have two MSSQL Tables, then you can use a script similar to the one below to reformat it...
SET NOCOUNT ON
select '1, "mike,sta" ,Cal' Column1 into testtable
insert into testtable select '2,"sbc , fts",ny'
insert into testtable select '3,alan,ca'

SET NOCOUNT ON
Declare @dblq1 int,@dblq2 int, @Comma1 int, @Comma2 int, @Column1 varchar(254)
select Column1 from testtable
DECLARE IterateThroughList CURSOR FOR 
	select Column1 from testtable
OPEN IterateThroughList
FETCH NEXT FROM IterateThroughList into @Column1
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @dblq1=charindex('"',@Column1)
	SET @dblq2=charindex('"',@Column1,@dblq1+1)
	if(@dblq1>0 and @dblq2>0) -- We have double quotes!
	Begin
		SET @Comma1=charindex(',',@Column1)
		SET @Comma2=charindex(',',@Column1,@dblq2)
		if not exists(select 1 from sysobjects where type='U' and [name]='FinalTable')
			select  substring(@Column1,0,@Comma1)Column1,
				substring(@Column1,@Comma1+1,@Comma2-@Comma1-1)Column2,
				substring(@Column1,@Comma2+1,len(@Column1)-@Comma2)Column3
			into FinalTable
		Else
		Insert into FinalTable
			select  substring(@Column1,0,@Comma1)Column1,
				substring(@Column1,@Comma1+1,@Comma2-@Comma1-1)Column2,
				substring(@Column1,@Comma2+1,len(@Column1)-@Comma2)Column3
	End
	Else
	Begin
		SET @Comma1=charindex(',',@Column1)
		SET @Comma2=charindex(',',@Column1,@Comma1+1)
		If(@Comma1>0 and @Comma2>0)
		Begin
			if not exists(select 1 from sysobjects where type='U' and [name]='FinalTable')
			select 	substring(@Column1,0,@Comma1)Column1,
				substring(@Column1,@Comma1+1,@Comma2-@Comma1-1)Column2,
				substring(@Column1,@Comma2+1,len(@Column1)-@Comma2)Column3
			into FinalTable
			else
			Insert into FinalTable
			select 	substring(@Column1,0,@Comma1)Column1,
				substring(@Column1,@Comma1+1,@Comma2-@Comma1-1)Column2,
				substring(@Column1,@Comma2+1,len(@Column1)-@Comma2)Column3
		End
	End
   FETCH NEXT FROM IterateThroughList into @Column1
END

CLOSE IterateThroughList
DEALLOCATE IterateThroughList
Select * from FinalTable
GO
DROP Table testtable
DROP Table FinalTable

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.