How to delimit?

My data just like that belowed

name,sex,phone-number,address,notes,email

"andrew","male","203-222-222","aab","pretty good job,need contince","adfsa@.yahoo.com"

....

so,Row delimiter is {CR}{LF} no problem

Column delimiter is Comma{,} appear error at notes including a comma inside the content

how should i delimit ? thanks

take a look at the textqualifier

|||

declare @.text varchar(100)

set @.text = '"andrew","male","203-222-222","aab","pretty good job,need contince","adfsa@.yahoo.com"'

declare @.name varchar(100)

declare @.sex varchar(100)

declare @.phone varchar(100)

declare @.address varchar(100)

declare @.notes varchar(100)

declare @.email varchar(100)

declare @.currentCommaIndex int,

@.nextCommaIndex int

set @.currentCommaIndex = charindex(',', @.text)

select @.name = substring(@.text, 0, @.currentCommaIndex)

print 'name = ' + @.name

set @.text = substring(@.text, @.currentCommaIndex + 1, len(@.text) - @.currentCommaIndex)

set @.currentCommaIndex = charindex(',', @.text)

select @.sex = substring(@.text, 0, @.currentCommaIndex)

print 'sex = ' + @.sex

set @.text = substring(@.text, @.currentCommaIndex + 1, len(@.text) - @.currentCommaIndex)

set @.currentCommaIndex = charindex(',', @.text)

select @.phone = substring(@.text, 0, @.currentCommaIndex)

print 'phone = ' + @.phone

set @.text = substring(@.text, @.currentCommaIndex + 1, len(@.text) - @.currentCommaIndex)

set @.currentCommaIndex = charindex(',', @.text)

select @.address = substring(@.text, 0, @.currentCommaIndex)

print 'address = ' + @.address

set @.text = substring(@.text, @.currentCommaIndex + 1, len(@.text) - @.currentCommaIndex)

-- here comes a bit tricky part: find the next comma

-- which is followed by a double quote,

-- which means it's the end of the notes field:

declare @.tempIndex int

set @.tempIndex = charindex(',', @.text)

while substring(@.text, @.tempIndex + 1, 1) <> '"'

begin

set @.tempIndex = @.tempIndex + 1

end

set @.currentCommaIndex = @.tempIndex + 2

select @.notes = substring(@.text, 0, @.currentCommaIndex)

print 'notes = ' + @.notes

-- ...and, finally, this is easy again:

set @.email = substring(@.text, @.currentCommaIndex + 1, len(@.text) - @.currentCommaIndex)

print 'email = ' + @.email

--

Result:

name = "andrew"

sex = "male"

phone = "203-222-222"

address = "aab"

notes = "pretty good job,need contince"

email = "adfsa@.yahoo.com"

|||Can you use "pipe" "|" as delimiter? this could solve all your problem.