SQLTeam.com | Weblogs | Forums

Bulk insert with 2 delimiters


#1

Hi,
I have a csv file that each column is separated by a comma.
However, one column that i get from the client is in the format "firstname,lastname"
This is the layout of the csv file
Firstname , LastName, PhoneNumber, BirthDate, AgentName, Price
Steven,Black,97567785,18122000,"Homer,Simpson",2304

What happens is that in the price field when i import i get Simpson"2304
AgentName i get "Homer

This is the query in sql
BULK
INSERT #tmpDelete
FROM 'd:\book1.CSV'
WITH
(
CODEPAGE = 'ACP',
FIRSTROW =2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

I need the codepage as the text isn't in English and without it i get gibberish.