asked on

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

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vijay11

ASKER

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

Aaron Shilo

to make things simple use the import wizrd from ssms (under tasks wehn you right click).

import the csv with comma as your delimiter

SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jerryuk007

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