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?
 
YZlatCommented:
the link below contains a solution

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=41295
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.